I confirm your query determining the fully expanded list of A_ids works as
I explicitly tried it.
Now. I have actually simplified the problem before presenting it here in
the list. But I just tried to extrapolate the solution back to the
original problem and failed miserably :-(
Yes, that is the problem with simplifying problems - one is liable to
simplify-out some essential properties of the problem.
The original problem is not much more complicated, and for clarity, I will
get rid of the Table B step as I can understand that 2nd step perfectly
well.
So there is a 1 to 1 relationship between X objects and A objects.
A objects are complex and have elements. This elements then refer to either
another X (never the containing X) or refer to a B object.
Table X
id A_id
=========
21 1
23 3
....
Table A
A_id
======
1
3
...
Table Aelements
object_id index X_id B_id
=========================
1 0 NULL 5
1 1 23 NULL
1 2 NULL 7
3 0 NULL 2
3 1 NULL 3
...
Now extrapolating your recursive query, with the main input object is (X
id=21) I've tried:
WITH RECURSIVE AE(object_id, X_id, B_id) AS (
SELECT object_id , X_id , B_id FROM Aelements WHERE object_id IN
(SELECT A_id FROM X WHERE id=21) AND X_id IS NULL
UNION ALL
SELECT Aelements.object_id , Aelements.X_id , Aelements.B_id FROM
Aelements, AE WHERE AE.object_id IN (SELECT A_id FROM X WHERE id IN (SELECT
X_id FROM Aelements WHERE object_id IN (SELECT A_id FROM X WHERE id=21) AND
X_id IS NOT NULL)))
SELECT * FROM AE;
But I think I'm failing to express the recursion in the above.
It doesn't work.
Basically in above, the resulting flattened Aelements list should be 1 3,
and therefore the corresponding B_id should be 5 7 2 3 , and finally the
last step from table B.
Right, this schema was made by a masochist. :)
I see David R. already had a go at a solution which seems perfect and it
already probably works for you, I'm only going to elaborate a bit more
for education purposes since you seem interested in learning about CTE
and recursive queries.
Firstly, the table A is analogous to an Index and completely superfluous
to our problem, so it can be ignored from the start. Aelements already
contains the object id's we need, so we do not need table A.
I'm going to refer to Aelements simply as "E" from now on.
So if we start with Table X, the problem states (if my understanding is
correct) that we need to check for the X.A_id of the given X.id, if
there are any E.object_id's which has a reiterating reference back to
table X in the E.X_id column and expand those corresponding X.A_id
relational entries until we have exhaustively added every implicated
A_id that is a 1st, 2nd, ... or n-th tier relation to the original X.id.
This list we will then match to the B values later (which is easy and
not part of this immediate problem, so I remove the B references
completely from the CTE).
In the first (top) part of any CTE table is the root or origin query -
which for our case is simply the original referenced X.id, so if we make
a CTE with only the root part, it would be:
WITH AE(X_id, A_id) AS (
SELECT X.id, X.A_id FROM X WHERE X.id = ?1
)
SELECT * FROM AE;
Note that we start from X_id and we look to expand for it the A_id
values, and I know later we will need to refer back to the X_id values,
so that is why I chose those field names in that order in the CTE. The
result of this query should be the single line from the X table that
gives the correct root id. (Nice thing about CTEs is that you can at any
point select from them, like we do here, to see what the results are so
far).
Now we are ready to add the recursive part - We need for every X_id
added to that CTE table, to also add any related A_id values which it
points to AND which has a corresponding X_id in the E table that refers
back to another X.id in the X table (i.e where the E.X_id column is not
null).
The recursive query part is simply the query that follows the root via a
UNION *and* that refers back to the containing CTE. So to make AE a
recursive CTE, we will add our id-expanding query after the UNION and
refer (JOIN) back to the same AE cte *as if it always contained the
full list of X_id references we need* (this is the bit that feels a
little like magic).
WITH AE(X_id, A_id) AS (
SELECT X.id, X.A_id, NULL FROM X WHERE X.id = ?1
UNION ALL
SELECT E.X_id, X.A_id FROM Aelements AS E JOIN X ON X.id = E.X_id
WHERE E.object_id = AE.A_id AND E.X_id IS NOT NULL
)
SELECT * FROM AE;
As we scan the Aelements table for the object_id(s) that matches the
A_id which our root query produced
[ ...FROM Aelements AS E ... WHERE E.object_id = AE.A_id... ],
any entries found with a valid X_id
[ ... WHERE ... E.X_id IS NOT NULL ]
is then matched back to the X table
[ ... JOIN X ON X.id = E.X_id ... ]
and that E.X_id and X.A_id is then appended to our AE cte table to await
further processing.
[ ... UNION ALL SELECT E.X_id, X.A_id ... ]
It reminds me a little bit of how Lemmings used to build bridges. We
traverse the query rows always adding more rows in front of us while
processing the rows below our feet, so long as there exist more rows
that match our criteria from those we process, we keep going, and if we
reach the final one, self-terminate. :)
The final step is simple, use the output from this CTE (AE) to match the
rows in Aelements finding all the B_ids which in turn matches the B
table to find the B values.
WITH AE(X_id, A_id) AS (
SELECT X.id, X.A_id, NULL FROM X WHERE X.id = ?1
UNION ALL
SELECT E.X_id, X.A_id FROM Aelements AS E JOIN X ON X.id = E.X_id
WHERE E.object_id = AE.A_id AND E.X_id IS NOT NULL
)
SELECT DISTINCT B.value
FROM AE
JOIN Aelements AS E ON E.object_id = AE.A_id
JOIN B ON B.object_id = E.B_id
;
I've tried to explain some complex stuff in very simple terms here
without writing an entire book, but probably failed miserably on all
counts... All I can suggest beyond these explanations is to play with
the CTE queries and inspect them at every step - I promise it becomes
self-evident quickly how the machinations function on the inside, and
when you grasp it once, it can never be un-grasped again.
Good luck and feel free to ask more if you still have questions,
Ryan
PS: again, I didn't test this on a real system, all sql is typed from my
head, so mistakes have likely slipped in, but should work in theory.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
