On Feb 27, 2013, at 12:21 PM, Ladislav Lenart <lenart...@volny.cz> wrote:
> Hello. > > Suppose I have the following mapped classes, A and B, that have two distinct > M:N > relationships, AB1 and AB2. If A.x is null, only relations in AB1 apply. If it > is not null, only relations in AB2 apply. A also has 1:N relationship to C > (one > A can have more Cs). Finally, A is infact a joined table inheritance > superclass > with two subclasses, A1 and A2. I want to select all As for a given B via AB1 > or > AB2. I also want to prefetch A.cs of the results using joinedload. I use the > code like this: > > q1 = session.query(A).with_polymorphic([A1, A2]) > q1 = q1.filter(exists().where(and_( > A.x == None, > AB1.a_id == A.id, > AB1.b_id == b_id, # input argument > )) > > q2 = session.query(A).with_polymorphic([A1, A2]) > q2 = q2.filter(exists().where(and_( > A.x != None, > AB2.a_id == A.id, > AB2.b_id == b_id, # input argument, > )) > > q = q1.union_all(q2) > q = q.options( > joinedload(A.cs), > ) > > return q > > This creates the following SQL: > > SELECT .... > FROM ( > SELECT... -- via AB1 > UNION ALL > SELECT ... -- via AB2 > ) anon_1 > LEFT OUTER JOIN c ON c.a_id = a.id -- error line > > This fails with a missing from clause error for table a. The attribute a.id is > actually anon_1.a_id. I'm kind of amazed it even managed to render that without bombing out a lot sooner. I'm not sure joinedload() is sophisticated enough right now to figure out the two queries inside of a UNION and all of that, one thing to try would be if your code has different/better/worse behavior on 0.8 (I'd be curious), but short of that the workaround is to join explicitly and then use contains_eager(), like: q = q.outerjoin(A.cs).options(contains_eager(A.cs)) that's assuming plain old outerjoin(A.cs) works here (which it also might have similar issues). If that's not working then you really might have to get more explicit, in a case this complicated that almost might not be worth it. Another option is to use subqueryload(A.cs) instead. subqueryload() tends to produce a wider range of queries and also tends to put less strain on the database. Ultimately its a SQLAlchemy bug but it's not clear how hard it would be to fix. If all the above options fail I might try to see if there's a repair path at least within 0.8. A link to working example mappings would be of great help here. -- 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.
