List sorted Posts on Group and children Groups efficiently

Question

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.

J.D. · Accepted Answer · 2021-06-29T23:39:30.687

What you're trying to solve is a type of tree or hierarchy problem. Usually these are solvable with recursion. Specifically in a RDBMS like MariaDB you can use something called a Recursive CTE to generate the relational dataset that represents the parent (ancestor) / child tree-like structure of your data.

Just like with recursion in a procedural language, in a recursive CTE, there is a base case aka the anchor and the recursive case that the anchor is unioned with.

Generic syntax example:

WITH RECURSIVE ExampleRecursiveCTE AS 
(
    -- Setting up the anchor / base case
    SELECT
        "" As ParentDescription,
        0 AS ParentId,
        "This is the base case" AS ChildDescription, 
        0 AS ChildId
UNION ALL

-- The recursive levels
SELECT 
     ChildDescription AS ParentDescription, 
     ChildId AS ParentId, -- The ancestor level becomes the parent of this level
     &quot;Some cool stuff about this child&quot; AS ChildDescription,
     ChildId + 1 AS ChildId -- This is the new child level
FROM ExampleRecursiveCTE

)
SELECT ParentDescription, ParentId, ChildDescription, ChildId
FROM ExampleRecursiveCTE

List sorted Posts on Group and children Groups efficiently

1 Answers1