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.