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
