without my completely understanding this (which would require
reconstructing it and running it), would a query.outerjoin_to('foo')
solve the problem ?
On Sep 26, 2006, at 12:54 PM, Dennis Muhlestein wrote:
> Sorry, this is long but bear with me :)
>
> I have a rather complex object (a) that can be joined to another
> table/object b in more than one way. The structure is something like
> this
>
> Table A
> id int primary key
> c_id int references c #(c can have lots of a)
>
> Table B
> a_id int references a
> c_id int references c
> primary key (a_id,c_id) #(c can have one (or none) b for each "A"
> whether they own it or not)
>
> C isn't important for this example really.
>
> In my mapper for A, I've added a property (let's say "c_b" for the
> join with the custom primaryjoin and that works just great.
> session.query(A).select_by( ... ) produces the join that I've
> specified:
> A.mapper.add_property('c_b', relation
> (B,uselist=False,primaryjoin=and_(
> a.c.id==b.c.a_id,a.c.c_id==b.c.c_id))
>
> example:
> select .. from A left outer join B on .... (my join from above)...
> WHERE... etc.
>
> That's all fine.
>
> I need to do the following though:
>
> session.query(A).select ( and_(B.c.otherproperty == None ,
> B.c.otherproperty=false) )
>
> The above statement does not include the outerjoin for A to B.
> Instead, it includes
> FROM A,B WHERE..etc.. which obviously includes too many rows.
>
> Next try:
>
> session.query(A).options(eagerload('c_b')).select ( B.c.otherproperty
> == None , B.c.otherproperty=false )
>
> Result is FROM B,A left outer join B on .... etc WHERE etc. (Same
> result as first try)
>
> Alternate try:
>
> session.query(A).select ( or_(B.c.otherproperty == None ,
> B.c.otherproperty=false) & q.join_to('c_a') )
>
> Result returns too few rows because the join is handled manually in
> the where clause with and statements and functions like an inner join
> instead of an outer join.
>
> So.. reverting to the select statement, I can do this:
>
> s=select( [tablea], and_(...correct from
> clause),from_obj=[tablea.outerjoin(tableb, and_(...joinclause...))] )
> alist=A.mapper.instances( s.execute(), session )
>
> And... wallah.. I have the correct list of results.
>
> Well, is there any way to accomplish the last result by using the
> query object's select statement or am I consigned to using the mapper?
> If I can use the query interface, I don't have to worry about all the
> other join objects in the select statement, and can instead pass
> eagerload options for the properties I need in the query. It really
> is noticably fewer lines of code and less headache.
>
> Thoughts?
>
> Thanks
> Dennis
>
> ----------------------------------------------------------------------
> ---
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the chance to
> share your
> opinions on IT & business topics through brief surveys -- and earn
> cash
> http://www.techsay.com/default.php?
> page=join.php&p=sourceforge&CID=DEVDEV
> _______________________________________________
> Sqlalchemy-users mailing list
> Sqlalchemy-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys -- and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
