WITH RECURSIVE SELECT via secondary table

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.

Answer

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.