I know how to issue an Intersect of multiple Selects, but I am
wondering if there is a simple way to seek the intersection of
multiple ORM queries. I already know how to get the results I want in
pure SQL or in SA-assisted non-ORM statements. I just wonder if I
could use sqlalchemy even more powerfully in the case of a particular
M:M relationship in my model.
Here's the situation, very simple. Table a is mapped to class A, and
table b mapped to class B. I have a many:many relationship between
class A and class B, via a secondary table whose only columns are a_id
and b_id. What I'm looking for is, I have a list of Bs and I want to
find the (single) A that has exactly those same Bs in its Bs
collection. (I know the results will either be a single A or None,
because of certain uniqueness constraints that don't matter to this
question).
So to do this with selects, this should work fine: (pardon the pseudo-
SQL)
intersect(
select a_id from secondary_table where b_id ==
b_being_sought[0].id,
select a_id from secondary_table where b_id ==
b_being_sought[1].id,
...
select a_id from secondary_able where b_id ==
b_being_sought[n].id
)
-- Easily generated with a Python loop over the list of B's that I'm
searching for.
So each of those selects returns a number of rows from the secondary
table, all linked to ONE of the B's in the list; and the intersect
returns the single (or none) row in the secondary table which refers
to the A which has *all* of those B's in its B collection.
Fine. But it would be so syntactically smooth if I could just do
something like:
intersect(
query(A).filter(b=b_being_sought[0]),
query(A).filter(b=b_being_sought[1]),
...
query(A).filter(b=b_being_sought[n])
)
Is this possible in some way? I haven't found a way to make this work
ORM-style, because intersect() only wants select statements. Am I
correct in thinking that I could build each ORM query, steal its
where_clause and use those where_clauses as my set of selects for the
intersect()? But that is enough extra steps, and enough exposition of
internals, to clearly make it a confusing and backwards way of getting
the results I want. Definitely not a path to more readable code -- if
that were the only way, then I would just do it the non-ORM way above.
All comments appreciated.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
