Sheldon/Jens, Any chance that this has been figured out and patched?
Great analysis Sheldon! Jeff On Thu, May 1, 2008 at 7:24 PM, Sheldon Maloff <[EMAIL PROTECTED]> wrote: > Hello Jens, > > I think I know what's going on here, because our descending sort > searches are broken too and I have started to investigate what's causing > the problem and trying to fix it. I have a January '08 version of the > trunk. I believe it's changed quite a lot since that time. > > Jens, I don't think it's anything you "broke" but rather an artifiact of > how MySQL works. At least, I'm using MySQL and this is the behaviour I > see. > > I created 6 records, whose ids are 1 to 6 in my database. I am > paginating on every 5 records. In my reverse sort I would expect to see > records 6, 5, 4, 3, 2 on page 1 of the results. And id 1 on page 2 of > the results. > > What I see is a method called ar_find_by_contents. It calls > find_id_by_content that returns an array that in turn calls ferret. The > array that comes back from ferret is actually correctly sorted: > > 6 0.928179502487183 > 5 0.928179502487183 > 4 0.928179502487183 > 3 0.928179502487183 > 2 0.928179502487183 > 1 0.928179502487183 > > The first number is the id, the second is the rank. > > Now what happens is ar_find_by_contents calls retrieve_records. And > retrieve_records produces a SELECT statement like so: > > SELECT * FROM model WHERE id IN (6, 1, 2, 3, 4, 5) LIMIT 0, 5 > > It took me a while to figure out that things are being passed around as > a hash, and hence the wacky order of the ids in the IN clause. Now the > problem with this statement is that MySQL doesn't return records in the > order that the ids appear in the IN clause. MySQL returns records in the > order of the Primary Key on the table, which happens to be the id > column. So MySQL is returning records 1, 2, 3, 4, 5, 6, in that order. > Then the LIMIT clause kicks in and truncates the results to 1 through 5. > > Now the rest of ar_find_by_contents valiantly tries to order the AR > results with the rank returned by ferret (my first table above). The > problem is, record 6, the youngest, is no longer in the results because > LIMIT took it out. So AAF sorts records 1 through 5 descending. > > Following along we can see how page two returns only record 6. On page > two, the limit changes to > > SELECT * FROM model WHERE id IN (6, 1, 2, 3, 4, 5) LIMIT 5, 5 > > Once again, My SQL returns records 1, 2, 3, 4, 5, 6, but this time the > limit returns only the last record, id 6. And then AAF sorts that > descending. > > I working on a patch for the version I have by making MySQL return only > the correct set of records in the first place. In other words, ensuring > that the only ids present in the IN clause are the ones that should > appear on page 1 of the results, or page 2, or pane N. > > So my AR query for page 1 looks like > > SELECT * FROM model WHERE id IN (6, 5, 4, 3, 2) LIMIT 0, 5 > > and the AR query for page 2 looks like > > SELECT * FROM model WHERE id IN (1) LIMIT 0, 5 > > I got it working, but in the process have made every other search, not > work. Funny. I'm sure I'll figure it out. > > Anyway, Jens, that's the gist of the problem at least how it relates to > MySQL. Other databases may vary. > > Regards > Sheldon Maloff > veer.com > > > Jens Kraemer wrote: > > Hi Max, > > > > thanks for your detailed report. Might well be that I broke one or more > > of the various combinations of pagination / sorting / active record > > conditions (where you might specify :order, too, btw) in trunk. > > > > I'll look into it asap. > > > > Cheers, > > Jens > -- > Posted via http://www.ruby-forum.com/. > _______________________________________________ > Ferret-talk mailing list > [email protected] > http://rubyforge.org/mailman/listinfo/ferret-talk > -- Jeff Webb [EMAIL PROTECTED] http://boowebb.com/
_______________________________________________ Ferret-talk mailing list [email protected] http://rubyforge.org/mailman/listinfo/ferret-talk
