[sqlalchemy] Re: Inferring joins from table A to table C via table B
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" 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.googl
[sqlalchemy] Re: Inferring joins from table A to table C via table B
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. :( you have to realize in all of those cases, the mappers have been given by you the exact paths which it is to join on - it's never just "looking" for something and picking the first match. The "outer join" formulation (i.e. via eager loading) is present since you've placed "lazy=False" on those relations. The key practice here is requiring explicit statement of all behaviors, and we try to stick to that pretty often unless a behavior has absolutely zero chance of being surprising or appearing inconsistent with the steps required in more complex scenarios. Specifically with query(A).join(B) working "automatically", if your code were greatly dependent on this, and you then someday added a second relation() between A and B, all your existing code would break. > On Sep 3, 12:29 pm, "Michael Bayer" 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 -~--~~~--
[sqlalchemy] Re: Inferring joins from table A to table C via table B
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" 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Inferring joins from table A to table C via table B
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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Inferring joins from table A to table C via table B
> > 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. Again, my example was not the joining between two adjacent tables, which we can get SA to handle on its own without our help. It was between two tables that are adjacent only through an intermediary table that touches both of them. --Damon --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Inferring joins from table A to table C via table B
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') if you're saying this, query(A).join(B) that isn't really how query.join() is supposed to work - it's not using ORM channels to figure out the join in that case. all documentation and examples use the first format. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
