Michael Bayer wrote:
> On Apr 7, 2007, at 8:11 AM, HD Mail wrote:
>
>
>> Hi,
>>
>> I was after some opinions on the following use of SA.
>>
>> 1. Is there any problems using SA in this way ?
>> 2. Is there better ways of achieving this ?
>>
>>
> ...
>
>> query = db.query(model.Asset).options(contains_eager('location'),
>> contains_eager('type'))
>> r = query.instances(s.execute())
>> return r, count
>>
>>
>
> that youve constructed exactly the query you want and used it via
> instances() is exactly how i want people to use SQLAlchemy when they
> know what non-trivial SQL they want to issue. query() only creates a
> very specific kind of SQL and it could never support figuring out how
> to construct the SQL that you already know how to do.
>
> Particularly for your query you are doing an eager load between
> "asset" and "location" yet a lot of your query criterion depends upon
> "location", so in that sense yes you have to use custom SQL, since
> query() will never involve eager loaded joins in the query criterion.
>
> however, theres a reason query follows this behavior, which is that
> if you are loading Asset objects with an eager loaded list of
> Location objects, but you have placed limiting criterion on the list
> of Locations specifically, you will now have Asset objects whose
> loaded list of Locations may not be complete compared to whats in the
> database (and they will remain that way until those instances are
> expire()d or similar). So you should be aware that that is the
> effect youre getting in your code.
>
Hi Michael,
Everything you say makes perfect sense for 1:N relationships, but in my
case, and with alot of other cases where I need the order by or the
criteria/filter on the joined table, it's a 1:1. In these cases I'm not
sure why SA can't generate the same type of SQL statement that I am
above. It would make perfect sense for it to.
I understand the eagerload problem with a list of child objects but with
1:1 relations I think the query interface should be querying in the same
way that my manual SQL is.
> also the separate "count()" step may be problematic since consider it
> wont return just the number of Asset objects loaded, but the number
> of rows total, which is Asset * Location * AssetType object rows. if
> you want just the number of Asset's returned youd just return len(r).
>
You're right, but because the the joins are 1:1, len(r) and count() will
give me the same result.
Thanks
Huy
> >
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
