List sorted Posts on Group and children Groups efficiently

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply