0

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.

1

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
         "Some cool stuff about this child" AS ChildDescription,
         ChildId + 1 AS ChildId -- This is the new child level
    FROM ExampleRecursiveCTE
)

SELECT ParentDescription, ParentId, ChildDescription, ChildId
FROM ExampleRecursiveCTE
2
  • Thank you for the suggestion. However, how is it recursive, if you're not referencing ExampleRecursiveCTE inside itself? This just looks like a normal UNION that could be done without CTE.
    – Nuno
    Jun 29 '21 at 22:29
  • @Nuno Apologies, that was a typo. The CTE should reference itself, and I've updated my example to fix that. Thanks!
    – J.D.
    Jun 29 '21 at 23:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.