On 10/16/2010 03:31 PM, rake wrote:
> Table structure:
>
> A(id, name)
> B(id, name, A_id)
> C(id, name, B_id)
>
> one-to-many A->B and B->C
>
> I'm trying to use session.query() to select all rows of A such that
> none of the joined B rows have any joined C rows.
>
> A:
> (1,'A1')
> (2,'A2')
>
> B:
> (1,'B1',1)
> (2,'B2',1)
> (3,'B3',2)
>
> C:
> (1,'C1',1)
>
> So, it would return (2,'A2'). Any help would be appreciated.
>
>
Assuming you have relations A.bs and B.cs:
q = session.query(A)
q = q.filter(~A.bs.any(B.cs.any())
which would translate roughly into this SQL:
SELECT <A columns>
FROM A WHERE NOT EXISTS (
SELECT 1
FROM B
WHERE B.a_id = A.id
AND EXISTS (
SELECT 1
FROM C
WHERE C.b_id = B.id))
-Conor
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
