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.
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 🙂