Kent wrote: > 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.
another solution, though it performs more poorly from a SQL perspective, is to use any() or has() instead of a join. its much easier to write as an expression, and it generates an EXISTS subquery. the count of primary rows is unaffected. > > - 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. > > > -- 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.
