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 sqlalchemy@googlegroups.com 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 -~----------~----~----~----~------~----~------~--~---