Unfortunately, in the case I noticed this happen, the join needs to be applied first since the filter's where clause and the order by both depend on the joined tables. Don't think your solution is helpful in that case, but informative nonetheless.
I can imagine a DISTINCT helping (I am using oracle, so limit is implemented with an outer select and rownum), but the minute I put a DISTINCT in the outer SELECT, the order by in the inner select is destroyed. (Also, I couldn't figure out how to get sqlalchemy to put the distinct in the outer select -- at the same select that uses ROWNUM <= :N in Oracle -- perhaps for good reason, since it destroys the order by!) This case was somewhat contrived and may never present as a live problem anyway. Thank you for your informative input and speedy response. - Kent On Jan 21, 12:00 pm, "Michael Bayer" <mike...@zzzcomputing.com> wrote: > Kent wrote: > > The limit() function behaves in a way that is possibly unexpected: > > > If you ask sqlalchemy to query "limit(3)" where a join is involved, > > for example, and 2 of the "top-3" are actually the same primary key, > > sqlalchemy gets the 3 results, throws out the duplicate and your query > > size ends up as 2. This gives the impression there are no more > > matches because you asked for 3 and got only 2, when in fact you may > > have hundreds of possible rows. > > > Right off, I can't think of an elegant solution to this behavior. > > Certainly sqlalchemy could *detect* the problem after the fact and re- > > request a greater limit, but that is ugly hackish and results in > > multiple trips to the database, sqla wouldn't know how many to re- > > request, etc... > > > sqlalchemy could request "DISTINCT" results, but I'm not sure how > > easily that could be implemented or what it may break. > > > Interested if anyone has an elegant solution? > > this is a natural effect of placing LIMIT on a query with a join. To > limit the "inner" results only, use query.limit(N).from_self().join(...) > which will apply the LIMIT to a subquery. > > SQLAlchemy's "eager loading" feature also does this subquery effect as > needed. from_self() was added so that users could have access to this > functionality in a more generic way. > > the Query remains explicit about join() and the fact that you're working > with SQL - so from_self() is a necessary explicit step too. > > > -- > > You received this message because you are subscribed to the Google Groups > > "sqlalchemy" group. > > To post to this group, send email to sqlalch...@googlegroups.com. > > To unsubscribe from this group, send email to > > sqlalchemy+unsubscr...@googlegroups.com. > > For more options, visit this group at > >http://groups.google.com/group/sqlalchemy?hl=en.
-- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
