I forgot -- what I posted above is the lower level of my question.
Here it is at a higher level, what I originally intended to ask.
SQLAlchemy already knows how to do what I'm looking for in the case of
finding an A that has a single, particular B in a collection:
session.query(User).filter(User.addresses.contains(address)).all()
or in my case,
session.query(A).filter(A.bs.contains(b_being_sought[0])).all()
.. or first() or one() instead of all().
In light of that, whoops, I wrote the second part of my previous post
wrong; where I had filter(b=b_being_sought[0]) I should have written
filter(bs.contains(b_being_sought[0]). In the mappers,
mapper(table_a, A, properties={bs:relation(B, backref="as",
secondary=secondary_table)}).
Anyway -- so what would really clean it all up would be:
session.query(A).filter(A.bs.contains(list_of_bs_being_sought)).all().
THAT would do exactly what I'm trying to accomplish. But it would
require contains() to accept a list and know what to do with it. My
proposal would be that the expected behavior is for contains() to
construct an intersect of selects where each select is like the one it
creates in its simpler case where the argument to contains() is a
scalar instead of a list. Does that make sense?
Thanks,
Eric
On Mar 5, 7:23 am, Eric Ongerth <[EMAIL PROTECTED]> 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
-~----------~----~----~----~------~----~------~--~---
