[sqlalchemy] Re: offset limit on query with relations
Huy wrote: Hi, When using the generative limit() offset() or order_by calls on mapper query, the sql generated looks weird. I get something like select table1.* table2.* from (select table1a.id from table1a limit 20 offset 0 order by table1.col) as table_row, table1 join table2 (on...) Notice how the limit and offset is in that subselect ? Is this by design. The query results are not what I would expect either because the subselect doesn't join to the main table (table1). Hope what Im describing makes sense. If the outer query involved eager loading of many-to-many properties, the number of rows returned would not necessarily be the same as the number of entities being loaded. By doing the limit and offset in the inner query, it guarantees that you will get exactly the expected number of entities. At least, that's my understanding. Hope that helps, Simon --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: offset limit on query with relations
King Simon-NFHD78 wrote: Huy wrote: Hi, When using the generative limit() offset() or order_by calls on mapper query, the sql generated looks weird. I get something like select table1.* table2.* from (select table1a.id from table1a limit 20 offset 0 order by table1.col) as table_row, table1 join table2 (on...) Notice how the limit and offset is in that subselect ? Is this by design. The query results are not what I would expect either because the subselect doesn't join to the main table (table1). Hope what Im describing makes sense. If the outer query involved eager loading of many-to-many properties, the number of rows returned would not necessarily be the same as the number of entities being loaded. By doing the limit and offset in the inner query, it guarantees that you will get exactly the expected number of entities. At least, that's my understanding. Hope that helps Hi Simon, That makes perfect sense, but why is the inner query joined not joined with the main table from the outer query ? because this is not done, I get a cartesian product between the inner result with the outer 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: offset limit on query with relations
On Apr 5, 2007, at 6:27 PM, HD Mail wrote: King Simon-NFHD78 wrote: Huy wrote: Hi, When using the generative limit() offset() or order_by calls on mapper query, the sql generated looks weird. I get something like select table1.* table2.* from (select table1a.id from table1a limit 20 offset 0 order by table1.col) as table_row, table1 join table2 (on...) Notice how the limit and offset is in that subselect ? Is this by design. The query results are not what I would expect either because the subselect doesn't join to the main table (table1). Hope what Im describing makes sense. If the outer query involved eager loading of many-to-many properties, the number of rows returned would not necessarily be the same as the number of entities being loaded. By doing the limit and offset in the inner query, it guarantees that you will get exactly the expected number of entities. At least, that's my understanding. Hope that helps Hi Simon, That makes perfect sense, but why is the inner query joined not joined with the main table from the outer query ? because this is not done, I get a cartesian product between the inner result with the outer result. the query wrapping i see in your example should only occur for eager loaded properties, so that the actual result you want, represented by the full rowset of the inner query, gets joined to the eager loaded properties you want. everything you specify with your Query should be in the inner query. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
