[sqlalchemy] eager load polymorphic relation
using python 2.7, sqla 0.7.9, postgresql 9.1 i'm trying to eager load a relationship to a polymorphic target (the target uses joined table inheritance). here is example: http://pastebin.com/xSiHS4QW and this is output: SELECT ... FROM container LEFT OUTER JOIN root AS root_1 ON root_1.id = container.root_id no type1, not type2? looks like with_polymorphic mappig arg controls that: http://pastebin.com/FfmJSZWc and this is output: SELECT ... FROM container LEFT OUTER JOIN ( SELECT ... FROM root LEFT OUTER JOIN type1 ON root.id = type1.id LEFT OUTER JOIN type2 ON root.id = type2.id ) AS anon_1 ON anon_1.root_id = container.root_id but i thought i'd get: SELECT ... FROM container LEFT OUTER JOIN root AS root_1 ON root_1.id = container.root_id LEFT OUTER JOIN type1 ON root.id = type1.id LEFT OUTER JOIN type2 ON root.id = type2.id this is not problem for small sets but for bulk ops either lazy loading or subselect are much slower. it looks like many have run into this. i tried using contains_eager but though that joins how i want it still does not load the entire target object (just base): c = Container .query .join(Container.root) .outerjoin(type1, root.c.id == type1.c.id) .outerjoin(type2, root.c.id == type2.c.id) .options(contains_eager(Container.root)) .first() c.root.field2 # field2 was not mapped so will do SELECT is there a way to build a query that eagerly loads the whole entity? q = Session.query ... c = q.first() c.root.field2 # already loaded so not SELECT thanks -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] joined table inhertiance and eager loading self-referencing relationships
using python 2.7, sqla 0.7.9, postgresql 9.1 i've setup joined table inheritance and wanted an eager loaded relationship from one of the derived objects to another derived object. when i do that i get what seems to be a really inefficient query. here is the minimal setup: http://pastebin.com/yS7gDfju and this is the output: SELECT ... FROM root JOIN type2 ON root.id = type2.id LEFT OUTER JOIN (SELECT root.id AS root_id, root.type AS root_type, type1.id AS type1_id, type1.field1 AS type1_field1 FROM root JOIN type1 ON root.id = type1.id) AS anon_1 ON type2.type1_id = anon_1.type1_id what i expected was: SELECT ... FROM root JOIN type2 ON root.id = type2.id LEFT OUTER JOIN root as root_1 ON root_1.id = type2.type1_id LEFT OUTER JOIN type1 ON type1.id = root_1.id which runs *much* faster for the data sets i've tried. is there a way to force the relationship to generate the 2nd form? or am i doing something i shouldn't? thanks -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/0laXtaxdh8UJ. 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: sqla 0.6.4, orm, problem with self-referring joins
=AliasedA1A1)) #AliasedB1 = orm.aliased(B) #q = q.join((AliasedB1, A.some_b)).options(orm.contains_eager(A.some_b, alias=AliasedB1)) print q = On Sep 20, 7:56 am, Michael Bayer mike...@zzzcomputing.com wrote: On Sep 19, 2010, at 11:59 PM, me wrote: when i apply multiple joins to orm query, one of them self-referring, using (target, property) form sqla generates the wrong join criteria by selecting aliased table for the left side of the join. sess = Session() AliasedA = orm.aliased(A) q = sess.query(A) q = q.join((AliasedA, A.some_a)).options(orm.contains_eager(A.some_a, alias=AliasedA)) q = q.join((B, A.some_b)).options(orm.contains_eager(A.some_b)) print q -- SELECT b.id AS b_id, b.a_id AS b_a_id, b.b_data AS b_b_data, a_1.id AS a_1_id, a_1.a_data AS a_1_a_data, a.id AS a_id, a.a_data AS a_a_data FROM a JOIN assoc AS assoc_1 ON a.id = assoc_1.left_id JOIN a AS a_1 ON assoc_1.right_id = a_1.id JOIN b ON b.a_id = a_1.id here i expected JOIN b ON b.a_id = a.id not JOIN b ON b.a_id = a_1.id. This one is fun. join() always joins from the most recent FROM that it can. So here you want to first call reset_joinpoint() before calling join() again. The other traditional way is to use the separate orm.join() construct in conjunction with select_from(), though we are continuing to refine join() so that wont be needed (I eventually would like it to allow (left, right, onclause)...but we need to get everyone on a modernized calling form first). perhaps weight relations so that those that introduce alias are applied in right order? interestingbut this is really a 50/50 guess. Its just as likely someone really meant to join from AliasedA to B. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] sqla 0.6.4, orm, problem with self-referring joins
when i apply multiple joins to orm query, one of them self-referring,
using (target, property) form sqla generates the wrong join criteria
by selecting aliased table for the left side of the join.
tried sqla 0.5.8 and 0.6.4, same result.
e.g. for sqla 0.6.4
from sqlalchemy import create_engine, Table, MetaData, orm, Column,
Integer, ForeignKey, String
from sqlalchemy.interfaces import PoolListener
class Listener(PoolListener):
def connect(self, dbapi_con, con_record):
dbapi_con.execute(PRAGMA foreign_keys=ON)
engine = create_engine(
sqlite:///:memory:,
listeners=[Listener()],
echo=True)
Session = orm.sessionmaker(bind=engine)
metadata = MetaData()
a_table = Table(
a, metadata,
Column(id, Integer, primary_key=True),
Column(a_data, String)
)
assoc_table = Table(
assoc, metadata,
Column(left_id, ForeignKey(a_table.c.id)),
Column(right_id, ForeignKey(a_table.c.id))
)
b_table = Table(
b, metadata,
Column(id, Integer, primary_key=True),
Column(a_id, ForeignKey(a_table.c.id)),
Column(b_data, String)
)
class B(object):
pass
orm.mapper(B, b_table)
class A(object):
pass
orm.mapper(
A, a_table,
properties={
some_a: orm.relation(
A,
primaryjoin=a_table.c.id ==
assoc_table.c.left_id,
secondary=assoc_table,
secondaryjoin=assoc_table.c.right_id ==
a_table.c.id),
some_b: orm.relation(
B,
primaryjoin=b_table.c.a_id == a_table.c.id)
}
)
sess = Session()
AliasedA = orm.aliased(A)
q = sess.query(A)
q = q.join((AliasedA, A.some_a)).options(orm.contains_eager(A.some_a,
alias=AliasedA))
q = q.join((B, A.some_b)).options(orm.contains_eager(A.some_b))
print q
--
SELECT
b.id AS b_id, b.a_id AS b_a_id, b.b_data AS b_b_data,
a_1.id AS a_1_id, a_1.a_data AS a_1_a_data,
a.id AS a_id, a.a_data AS a_a_data
FROM a
JOIN assoc AS assoc_1 ON a.id = assoc_1.left_id
JOIN a AS a_1 ON assoc_1.right_id = a_1.id
JOIN b ON b.a_id = a_1.id
here i expected JOIN b ON b.a_id = a.id not JOIN b ON b.a_id =
a_1.id.
looks like the whole query is anaylzed for a_1 alias based on
join_to_left=True set in 0.6.4/orm/query.py, ln 1341.
i can change order that joins are applied to avoid this OR replace
property A.bs with explicit join condition (i.e. A.id = B.a_id) but
for generated queries this is not always easy/convenient to do.
perhaps weight relations so that those that introduce alias are
applied in right order?
is this a bug or expected? is there a way to avoid this behavior when
using multiple (target, property) joins?
thanks.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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: 0.55, orm, varying relation join on criteria
thanks for the (insanely fast) help! wanted to avoid doing something unnecessarily odd. i ended up wrapping the relation in an object proxy when passing it to the join. the proxy ANDs additional criterion into the primary or secondary join attributes of the relation based on what is supplied when creating the proxy. though clearly odd it seem to generally work. i'm not sure if this may break orm magic somewhere down the line... code looks like this: query(User).outerjoin((Email, RelationProxy(User.emails, Email.name != bogus))) the object proxy code is based on the following if anyone's interested: http://code.activestate.com/recipes/496741/ http://code.activestate.com/recipes/519639/ On Sep 14, 2:13 pm, Michael Bayer mike...@zzzcomputing.com wrote: me wrote: For certain orm queries with a 1-to-many relation i want to left outer join and then update the on-clause for that relation. Since the criteria changes between queries I cannot fix the join criteria when specifying my object/table mappings. For example: tables: user, email relation: user.emails (1-many) select * from user left outer join email on email.user_id = user.id and email.name like '%hello%' While this is easy to write as a one off query in my case I need to be able to add variable filtering to the join on-clause and in a way that hopefully works for more complex relations. So e.g. if I have a query built like this: query(user).outerjoin((email, emails)) Is there a general way to add to the primary/secondary join criteria that is pulled from this emails relation? Or is there a better way to express this in SA that I've missed? the contract of query.outerjoin(SomeClass.property) is that you're doing a plain join from A to B along pre-established routes. If you'd like the criterion of the ON clause to be customized, the standard route is to spell out the entire thing you want completely. The only potential time saver here would be if you held onto the primaryjoin aspect of the relation and used it in an AND clause, which at first looks like: query(User).outerjoin((Email, and_(email_primary_join, other criterion))) the next level would be that you'd pull email_primary_join from the mapping. You can get at this via User.emails.property.primaryjoin. at the moment that's as automated as it gets as far as what's built in. --~--~-~--~~~---~--~~ 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] 0.55, orm, varying relation join on criteria
For certain orm queries with a 1-to-many relation i want to left outer join and then update the on-clause for that relation. Since the criteria changes between queries I cannot fix the join criteria when specifying my object/table mappings. For example: tables: user, email relation: user.emails (1-many) select * from user left outer join email on email.user_id = user.id and email.name like '%hello%' While this is easy to write as a one off query in my case I need to be able to add variable filtering to the join on-clause and in a way that hopefully works for more complex relations. So e.g. if I have a query built like this: query(user).outerjoin((email, emails)) Is there a general way to add to the primary/secondary join criteria that is pulled from this emails relation? Or is there a better way to express this in SA that I've missed? Thanks, M --~--~-~--~~~---~--~~ 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: 0.5.2, orm, deferring joined columns during query construction
Thanks for the help! Specifying path as a string relative to the root of the query properly defers the columns. Specifying path information with qualified object attributes (your second suggestion) also works. So everything works, I didn't Is this approach correct for cases where you end up deferring a large number of columns (e.g. 20)? Is there perhaps a more efficient way to bulk defer/undefer columns (whether these are columns of related children or the root) short of specifying that information when initially creating the query (e.g. session.query(A.b, A.c, B.x, ...)). I avoided doing that as I wanted an instance of a root object and the query.one() problem. Thanks, M On Mar 12, 8:56 am, Michael Bayer mike...@zzzcomputing.com wrote: me wrote: 2] Using path to the joined column: query = query.option(defer(a.s1_relation.col1), defer (a.s1_alias_relation.col2),...) 2 Simply doesn't work for me (i.e. the columns i'm deferring are clearly in the echoed sql). try not putting a in there. defer() is based on an older usage of Query that assumes one entity at the root, i.e. s.query(SomeClass).options(defer(relation.somecol)). I also tried passing instrumented object attributes to defers like this: query = query.option(defer(S1.y), defer(S1Alias.x)) but that failed with an exception about e.g. S1 not being found in the mapper. I tried adding them to the query like this: query.add_entity(S1) but that didn't help. needs a path from the root: defer(SomeClass.foo, FooClass.bar) --~--~-~--~~~---~--~~ 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] 0.5.2, orm, deferring joined columns during query construction
To optimize a query I'm attempting to defer columns from joined tables I know I'm not going to need. The columns from joined tables to defer varies from query to query so I can't specify this when setting up my object/table mappings. I'm able to do this just fine for columns of the primary table like this: query = query.option(defer(x)). It appears there are two ways to do the same for joined columns: 1] When specifying entities query = a_session.query(P, S1.x, S2.y, S1Alias.x)... 2] Using path to the joined column: query = query.option(defer(a.s1_relation.col1), defer (a.s1_alias_relation.col2),...) 1 Seemed easy enough but i ran into problems when using this in conjunction with query.one(): it would raise exceptions that the result set had more than one row. I really just want to pull the entity object (preloaded with relevant column values, some from related tables) and then extract that to a dictionary. So pulling S1.x, S2.y, S1Alias.x into the result, making it a tuple, seems a little strange. 2 Simply doesn't work for me (i.e. the columns i'm deferring are clearly in the echoed sql). I also tried passing instrumented object attributes to defers like this: query = query.option(defer(S1.y), defer(S1Alias.x)) but that failed with an exception about e.g. S1 not being found in the mapper. I tried adding them to the query like this: query.add_entity(S1) but that didn't help. Apart from the unwanted columns the emitted SQL is what i expect and looks like: SELECT bunch of columns FROM parent LEFT OUTER JOIN parent_child AS series_child_1 ON series.id = series_child_1.series_id LEFT OUTER JOIN child ON series_child_1.child_id = child.id -- primary child INNER JOIN child AS child_1 ON child_1.id = series.parent_child_id WHERE series.id = 1000 AND child.name LIKE 'prefix%' Am i missing something? Is there a way to specify joined column defers during query construction? Do i need to write a custom MapperProperty to do this? Thanks, M --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
