pardon my sql-ignorancy, but cant u express this in just one expression? it should be possible, it is a graph/set arithmetics after all... mmh, (could be very wrong!) something like - get all rows that has some b_id from the looked list - group(?) somehow by a_id, and then finger the a_id which collection of b's matches the looked list. on 2nd thought, maybe no, this is a procedural way, not a set-arithmetics way...
On Wednesday 05 March 2008 17:23:52 Eric Ongerth wrote: > 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 -~----------~----~----~----~------~----~------~--~---
