ok, so im thinking the join_to/join_via could be more DSLish and work
like this:
query.join_to('x').outerjoin_to('y').join_to('z') etc...
right now there is a from_obj parameter to Query. but it
unconditionally adds the mapper's table to it, so its hard to create
specific joins to that table. ill remove that extra table add if
theres one in there already. i added ticket 315 for this with a
description of what needs to be done.
On Sep 26, 2006, at 3:09 PM, Dennis Muhlestein wrote:
> 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
-------------------------------------------------------------------------
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
