you can explicitly create these many-to-many join relations and eager
load them.
users = session.query(User).options(eagerload(User.groups)).all()
if you want to query for relations with a filter, AFAIK you need to
define them as a separate relation.
class User(...)
groups = relation("Group", primaryjoin=("User.id ==
GroupMember.user_id"), secondaryjoin=("GroupMember.group_id ==
Group.id"), secondary="GroupMember")
large_groups = relation("Group", primaryjoin=("User.id ==
GroupMember.user_id"), secondaryjoin=("GroupMember.group_id ==
Group.id & Group.size > 10"), secondary="GroupMember")
users = session.query(User).options(eagerload(User.large_groups)).all()
users[0].large_groups # ...
On Sep 3, 2009, at 1:24 PM, Damon wrote:
>
> Thank you very much for the explanation. It is what I feared was the
> case.
>
> One of the great features we love about SA is the mappers, allowing us
> to define table relationships in such a way that we can decide what
> table(s) around which to "pivot", giving us different ways of
> returning data even when processed from the same query. It seemed to
> us that if the mappers are able to traverse all the joins necessary to
> render the mapped objects -- we greatly admire SA's ability to
> construct all the outer joins required to do this in one fell swoop --
> that it should also be possible to have SA follow similar logic to
> construct query objects as well -- in a completely analogous fasion --
> when supplied with filters.
>
> Alas that this is not the case. :(
>
> --Damon
>
> On Sep 3, 12:29 pm, "Michael Bayer" <mike...@zzzcomputing.com> wrote:
>> Damon wrote:
>>
>>>>> MUST we explicitly supply the join to such query objects? Or is
>>>>> there
>>>>> some way that SA can figure out that tbl_people_documents is in
>>>>> between tbl_people and tbl_documents on its own? Perhaps there is
>>>>> something we can add to the tbl_people/tbl_documents object
>>>>> definitions that clues SA in?
>>
>>>> join on the relation.
>>
>>>> query(A).join(A.relation_to_b).filter(B.foo == 'bar')
>>
>>> The problem with that, from what we're trying to build, is that we
>>> have to explicitly know that relation object and supply it.
>>
>>> We want SA to *infer* the relationship between any two tables
>>> based on
>>> the ORM relationships that we have already defined in our mapper
>>> objects.
>>
>> but you're asking for it to infer the join between *three* tables -
>> i.e.
>> your association table. The current SQLA functionality is that
>> ORM-level
>> joins, that is joins which occur due to the presence of a
>> relation(), must
>> be expressed explicitly in terms of the relation between the two
>> entity
>> classes. Right now only a SQL level join, that is joins which
>> occur
>> due to the presence of a known foreign key between the two tables,
>> is what
>> happens if you don't specify the relation() you'd like to join on.
>>
>> The proposed enhancement would require that we change the method
>> used when
>> someone joins from A to B using query.join(), in that it would
>> specifically search for ORM-level relations, instead of relying upon
>> SQL-level joining which searches only for foreign keys between the
>> two
>> tables. It would also throw an error if there were any ambiguity
>> involved. I'm not 100% sure but I think it's quite possible that
>> we had
>> such a "assume the only relation() in use" feature a long time ago
>> when
>> constructing joins, and it was removed in favor of explicitness,
>> but I'd
>> have to dig through 0.3 functionality to see if that was the case.
>>
>> My initial take on this feature is -1 on this since I don't think
>> being
>> explicit about an ORM relation is burdensome or a bad idea (plus we
>> might
>> have already made this decision a long time ago). We might just
>> need some
>> better error messages when a join can't be found between "A" and
>> "B" to
>> suggest that its only looking for immediate foreign keys in that
>> case, not
>> ORM relations.
>>
>> Alternatively, SQL-expression level join() would "search" for any
>> number
>> of paths from table A to table B between any other tables that may
>> create
>> a path between them. that would also find the association table
>> between A
>> and B and create a longer series of joins without ORM
>> involvement. I'm
>> strongly -1 on such a feature as the expression language shouldn't be
>> tasked with performing expensive graph traversals just to formulate
>> a SQL
>> query, and table.join()'s contract is that it produces a JOIN
>> between only
>> two tables, not a string of joins.
> >
--~--~---------~--~----~------------~-------~--~----~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
