I think so. See, the problem is when you use select_by ( <the
property on table b> = <whatever >), the generated sql appends the
same sql that you would get by using the join_to function. That in
effect does an inner join and eliminates the extra rows desired in the
result. The join needs to take place with the "A left join B using
(join clause)" syntax instead.
Thanks
-Dennis
On 9/26/06, Michael Bayer <[EMAIL PROTECTED]> wrote:
> 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
>
>
--
You can see what's happening at http://muhlesteins.com
-------------------------------------------------------------------------
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
