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 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 -~----------~----~----~----~------~----~------~--~---