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.
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 received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
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
-~----------~----~----~----~------~----~------~--~---
