[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 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: declarative style many to many, possible fix
Thanks for looking. What happens when viewonly=False? I tried appending/popping from the list of related secondary objects but I didn't see any duplicate inserts/deletes. - Jae On Sep 2, 2009, at 8:16 AM, Michael Bayer wrote: secondary requires a Table object as its argument. it is not recommended to create a relation that uses a mapped table as its secondary unless the relation specifies viewonly=True. --~--~-~--~~~---~--~~ 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: declarative style many to many, possible fix
if you create new entities on the secondary table, and also insert records in the relation() with the secondary, it will persist them separately. I see that now. I have found myself using this pattern, however, since relation + secondary can create more efficient joins than an eagerload on an association object (the latter is solvable but it is a bit complex). I don't understand. Which pattern? I'm going to use the pattern of setting viewonly=True, since it makes the code so much cleaner (w/ declarative base) to have all many-to- many relations defined inside the class declaration. - Jae - Jae On Sep 2, 2009, at 8:16 AM, Michael Bayer wrote: secondary requires a Table object as its argument. it is not recommended to create a relation that uses a mapped table as its secondary unless the relation specifies viewonly=True. --~--~-~--~~~---~--~~ 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] declarative style many to many, possible fix
Is there a way to declaratively create many to many relationships
where the 'secondary' parameter for the relationship is deferred ?
I couldn't get this to work, e.g.
class User(DeclarativeBase):
id = Column(Integer, primary_key=True)
name = Column(String(20))
groups = relation(Group, primaryjoin=(User.id ==
GroupMember.user_id), secondaryjoin=(GroupMember.group_id ==
Group.id), secondary=GroupMember)
(the other classes are defined later).
I was able to get around this with the following patch.
--- a/sqlalchemy0.5/lib/sqlalchemy/orm/properties.pyMon Aug 31
22:37:21 2009 -0700
+++ b/sqlalchemy0.5/lib/sqlalchemy/orm/properties.pyTue Sep 01
22:11:07 2009 -0700
@@ -736,7 +745,11 @@
# accept callables for other attributes which may require
deferred initialization
for attr in ('order_by', 'primaryjoin', 'secondaryjoin',
'secondary', '_foreign_keys', 'remote_side'):
if util.callable(getattr(self, attr)):
-setattr(self, attr, getattr(self, attr)())
+called_value = getattr(self, attr)()
+# the 'secondary' param requires a table, not a
declarative class...
+if attr == 'secondary' and hasattr(called_value,
'__mapper__'):
+called_value = called_value.__mapper__.mapped_table
+setattr(self, attr, called_value)
# in the case that InstrumentedAttributes were used to
construct
# primaryjoin or secondaryjoin, remove the _orm_adapt
annotation so these
- Jae
--~--~-~--~~~---~--~~
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: eagerload polymorphic object's relations, single table inheritance
I looked at this further, i think I got it working half way.
Index: lib/sqlalchemy/orm/util.py
===
--- lib/sqlalchemy/orm/util.py (revision 6072)
+++ lib/sqlalchemy/orm/util.py (working copy)
@@ -387,6 +387,7 @@
if left_mapper or right_mapper:
self._orm_mappers = (left_mapper, right_mapper)
+parent_mapper = onclause.parententity._AliasedClass__mapper
if isinstance(onclause, basestring):
prop = left_mapper.get_property(onclause)
elif isinstance(onclause, attributes.QueryableAttribute):
@@ -411,6 +412,9 @@
onclause = sj
else:
onclause = pj
+
+if parent_mapper._single_table_criterion:
+onclause = sql.and_(onclause,
parent_mapper._single_table_criterion)
self._target_adapter = target_adapter
expression.Join.__init__(self, left, right, onclause, isouter)
It works for queries of the form :
items =
session
.query
(Item).with_polymorphic([FooItem]).options(eagerload(FooItem.child))
but not :
items = session.query(Item).with_polymorphic([FooItem,
BarItem]).options(eagerload(FooItem.foochild, BarItem.barchild))
I'll keep trying around for a solution that solves the above, and then
hopefully someone more knowledgeable can pick it up or guide me.
- Jae
On Aug 28, 2009, at 4:43 PM, Jae Kwon wrote:
I've seen similar discussions here, but it's been a while so perhaps
things have changed.
class Foo(Base):
__tablename__ = 'foo'
type = Column(Integer)
__mapper_args__ = {'polymorphic_on': type}
...
class BarFoo(Foo):
__mapper_args__ = {'polymorphic_identity': 1}
bar_id = Column(Integer)
# relations
bar = relation('Bar')
class BazFoo(Foo):
__mapper_args__ = {'polymorphic_identity': 2}
baz_id = Column(Integer)
# relations
baz = relation('Baz')
## assume existence of Bar and Baz objects
Is there a way to say, query all Foo objects while eager-loading the
bar/baz relations?
- Jae
--~--~-~--~~~---~--~~
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: eagerload polymorphic object's relations, single table inheritance
oops, it looks like the second form
(session.query(Item).with_polymorphic([FooItem,
BarItem]).options(eagerload(FooItem.foochild, BarItem.barchild)))
works fine.
should i go ahead and try to write test cases?
- Jae
On Aug 29, 2009, at 2:23 PM, Jae Kwon wrote:
I looked at this further, i think I got it working half way.
Index: lib/sqlalchemy/orm/util.py
===
--- lib/sqlalchemy/orm/util.py(revision 6072)
+++ lib/sqlalchemy/orm/util.py(working copy)
@@ -387,6 +387,7 @@
if left_mapper or right_mapper:
self._orm_mappers = (left_mapper, right_mapper)
+parent_mapper =
onclause.parententity._AliasedClass__mapper
if isinstance(onclause, basestring):
prop = left_mapper.get_property(onclause)
elif isinstance(onclause, attributes.QueryableAttribute):
@@ -411,6 +412,9 @@
onclause = sj
else:
onclause = pj
+
+if parent_mapper._single_table_criterion:
+onclause = sql.and_(onclause,
parent_mapper._single_table_criterion)
self._target_adapter = target_adapter
expression.Join.__init__(self, left, right, onclause, isouter)
It works for queries of the form :
items =
session
.query
(Item).with_polymorphic([FooItem]).options(eagerload(FooItem.child))
but not :
items = session.query(Item).with_polymorphic([FooItem,
BarItem]).options(eagerload(FooItem.foochild, BarItem.barchild))
I'll keep trying around for a solution that solves the above, and
then hopefully someone more knowledgeable can pick it up or guide me.
- Jae
On Aug 28, 2009, at 4:43 PM, Jae Kwon wrote:
I've seen similar discussions here, but it's been a while so perhaps
things have changed.
class Foo(Base):
__tablename__ = 'foo'
type = Column(Integer)
__mapper_args__ = {'polymorphic_on': type}
...
class BarFoo(Foo):
__mapper_args__ = {'polymorphic_identity': 1}
bar_id = Column(Integer)
# relations
bar = relation('Bar')
class BazFoo(Foo):
__mapper_args__ = {'polymorphic_identity': 2}
baz_id = Column(Integer)
# relations
baz = relation('Baz')
## assume existence of Bar and Baz objects
Is there a way to say, query all Foo objects while eager-loading the
bar/baz relations?
- Jae
--~--~-~--~~~---~--~~
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] eagerload polymorphic object's relations, single table inheritance
I've seen similar discussions here, but it's been a while so perhaps
things have changed.
class Foo(Base):
__tablename__ = 'foo'
type = Column(Integer)
__mapper_args__ = {'polymorphic_on': type}
...
class BarFoo(Foo):
__mapper_args__ = {'polymorphic_identity': 1}
bar_id = Column(Integer)
# relations
bar = relation('Bar')
class BazFoo(Foo):
__mapper_args__ = {'polymorphic_identity': 2}
baz_id = Column(Integer)
# relations
baz = relation('Baz')
## assume existence of Bar and Baz objects
Is there a way to say, query all Foo objects while eager-loading the
bar/baz relations?
- Jae
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
