List sorted Posts on Group and children Groups efficiently

Say I have a system that has Groups and Posts on those Groups. A Group can have multiple "child Groups" > Group 1 > Group 2 > Group 3 > Group 1 > Group 2 > Group 4 > Group 1 > Group 5 > Group 6 > ... So I have these two tables: Groups (*GroupId*, Name, ParentGroupId, ...) Posts (*PostId*, GroupId, UserId, Text, ...) Displaying a list of latest 10 posts on Group 3, 4 and 6 isn't a problem. An index on GroupId does it (WHERE GroupId=12345 ORDER BY PostId DESC). However, the challenge I'm having is how can I make Group 2 list posts created directly in Group 2, but also on children (Group 3 and 4), and the same for Group 1 (list posts on Group 1 and on all children, sorted). The only thing I can think of is to create an extra "indexing table" which will have a list of PostId for each Parent Group, and use that to retrieve the Posts. But, I'm afraid that will be a pain to maintain and ensure it's accurate (like using a TRIGGER). What if a bug happens and then we have to fix the index, rebuild the whole index again, etc... Is there a better way of doing this? I was hoping to use something more native and auto-maintained. I use MariaDB 10.4 and Sphinx Search.