WITH RECURSIVE SELECT via secondary table

Question

I'm having a bit of a hard time trying to piece this together. I'm not adept with databases or complex queries.

The Database

I'm using the latest MariaDB release.

I have a database table configuration like so, representing a hierarchical data structure:

|----------------------|
| fieldsets            |
|----+-----------------|
| id | parent_field_id |
|----+-----------------|
| 1  | NULL            |
| 2  | 1               |
|----------------------|

|-------------------------|
| fields                  |
|----+--------------------|
| id | parent_fieldset_id |
|----+--------------------|
| 1  | 1                  |
| 2  | 1                  |
|-------------------------|

The Problem

I'm trying to piece together a recursive query. I need to select every fieldset in a given hierarchy. For example, in the above, stripped-down example, I want to select fieldset of id = 1, and every descendant fieldset.

The IDs of the next rung down in any given level in the hierarchy are obtained only via columns of a secondary table.

The table fieldsets contains no column by which I can directly get all child fieldsets. I need to get all fields that are a child of a given fieldset, and then get any fieldsets that are a child of that field.

A Better Illustration of the Problem

This query does not work because of the reported error: "Restrictions imposed on recursive definitions are violated for table all_fieldsets"

However, it really illustrates what I need to do in order to get all descendant fieldsets in the hierarchy (remember, a fieldset does not contain the column for its parent fieldset, since a fieldset cannot have a fieldset as a direct parent. Instead, a fieldset has a parent_field_id which points to a row in the fields table, and that row in the fields table correspondingly has a column named parent_fieldset_id which points to a row back in the fieldsets table, which is considered the parent fieldset to a fieldset, just an indirect parent.

WITH RECURSIVE all_fieldsets AS (
    SELECT fieldsets.* FROM fieldsets WHERE id = 125
    UNION ALL
    SELECT fieldsets.* FROM fieldsets
    WHERE fieldsets.parent_field_id IN (
        SELECT id FROM fields f
        INNER JOIN all_fieldsets afs
        WHERE f.parent_fieldset_id = afs.id
    )
)
SELECT * FROM all_fieldsets

My Attempt

The query I have thus far (which does not work):

WITH RECURSIVE all_fieldsets AS (
    SELECT fieldsets.* FROM fieldsets WHERE id = 125
    UNION
    SELECT fieldsets.* FROM fieldsets WHERE fieldsets.id IN (SELECT fs.id FROM fieldsets fs LEFT JOIN fields f ON f.id = fs.parent_field_id WHERE f.parent_fieldset_id = fieldsets.id)
)
SELECT * FROM all_fieldsets

My Research

I'm also having a hard time finding an example which fits my use-case. There's so many results for hierarchical structures that involve one table having only relations to itself, not via a secondary table, as in my case. It's difficult when you do not know the correct terms for certain concepts, and any layman explanation seems to yield too many tangential search results.

My Plea

I would be enormously grateful to all who can point out where I'm going wrong, and perhaps suggest the outline of a query that will work.

score 0 · Answer 1 · answered Feb 27 '20 at 16:08

0

The main problem I see with your current code is that the recursive portion of the CTE (the query which appears after the union) is not selecting from the recursive CTE, when it should be. Consider this updated version:

WITH RECURSIVE all_fieldsets AS (
    SELECT * FROM fieldsets WHERE id = 125
    UNION ALL
    SELECT f1.*
    FROM fieldsets f1
    INNER JOIN all_fieldsets f2
        ON f1.parent_field_id = f2.id
)

SELECT *
FROM all_fieldsets;

Note that the join in the recursive portion of the CTE relates a given descendant record in fieldsets to its parent in the CTE.

answered Feb 27 '20 at 16:08

Tim Biegeleisen

502,043
27
286
360

Thank you for taking the time to answer. However, I think perhaps I have not explained my case clearly enough. The table `fieldsets` contains no column by which I can directly get all child `fieldsets`. I need to get all `fields` that are a child of a given `fieldset`, and then get any `fieldsets` that are a child of that `field`, and so on down the hierarchy until there are no further `fieldsets` to collate. – thephpdev Feb 27 '20 at 16:20
@thephpdev And this is more or less exactly what my answer is doing. Did you actually try running my code? – Tim Biegeleisen Feb 27 '20 at 16:38
"Did you actually try running my code?" I did, and it did not work. I'm not demeaning your expertise, so I would please ask you refrain from the passive aggression. I'm trying to be as polite as I can. – thephpdev Feb 27 '20 at 17:22
As to why it is not working: I believe it is because you are not referencing the `fields` table anywhere in your query. Therefore, it is impossible to get the next level in the hierarchy. This is because the information to get the next level of `fieldsets` exists only on the `fields` that are a child of a `fieldset`. `fieldsets` cannot have direct child `fieldsets`. A `fieldset` has child `fields`, which then have child `fieldsets`. – thephpdev Feb 27 '20 at 17:22
Unless I'm misunderstanding the fundamentals of INNER JOINS and recursive queries, which is entirely possible, and perhaps even probable, however I cannot help but understand it the way I currently do, which is exactly why I'm asking for help on StackOverflow,. – thephpdev Feb 27 '20 at 17:24
Also to expand on "did not work" (as that is not helpful), only one result was returned, when in fact `fieldset` of `id` `125` does indeed have two child `fields`, which in turn have about `10` child `fieldsets`. – thephpdev Feb 27 '20 at 17:31
I managed to figure it out in the end, I had to do some joins inside the recursive query to fetch the necessary information from the `fields` table. Thank you for your time and assistance thus far. – thephpdev Feb 27 '20 at 20:51
You never showed us the output you want. Can you really expect anyone to come up with the exact query? – Tim Biegeleisen Feb 27 '20 at 23:37
I concede I could have illustrated it better. However, I admitted of that in my initial response to your answer. I did also specify from the get go the specifics of my query, explaining that I want to get all fieldsets in a given hierarchy, and that it must be via the fields table. Further, the column names in the diagram I posted show that you must get the next level via the child fields of a fieldset. It's not worth arguing about. I'll most certainly be sure to create a more detailed schematic should I ever run into an issue I need help with again. Thank you for your time. – thephpdev Feb 28 '20 at 08:03

score 0 · Accepted Answer · answered Feb 27 '20 at 20:49

I got home from work, and I just could not set this down!

But, out of that came a solution.

I highly recommend reading this answer about recursive queries to get a better idea of how they work, and what the syntax means. Quite brilliantly explained: How to select using WITH RECURSIVE clause

The Solution

WITH RECURSIVE all_fieldsets AS (
    SELECT * FROM fieldsets fs
        WHERE id = 59
    UNION ALL
    SELECT fs.* FROM fieldsets fs
        INNER JOIN all_fieldsets afs
        INNER JOIN fields f
            ON f.parent_fieldset_id = afs.id
            AND fs.parent_field_id = f.id
)
SELECT * FROM all_fieldsets

I had to use joins to get the information from the fields table, in order to get the next level in the hierarchy, and then do this recursively until there is an empty result in the recursive query.