On 4/29/08, Frederick Cheung <[EMAIL PROTECTED]> wrote: > > > On 29 Apr 2008, at 20:37, Trevor Squires wrote: > > > > > Fred, > > > > I thought you might be interested in this: > > > > http://rails.lighthouseapp.com/projects/8994-ruby-on-rails/tickets/64 > > > > Funny, I was thinking about has_one just this afternoon. It's a > toughie though - while fiddling with some of the internals can get you > the right comment, you're still loading all comments. Not sure off the > top of my head how you get around that (what does the old :include do ?) > > Fred >
Hey Fred, "what does the old :include do?" - it ignores your :order so it's arbitrary as far as I'm concerned. Ironically and by happy coincidence, using mysql and a has_one :order where you want the most recent record tends to actually give you the most recent record even though your :order clause is discarded. Yes, this means that fallback-eager is (probably) favoring the last record in the set. "you're still loading all comments" - yeah, new-eager and fallback-eager both ask the database for rows that will be discarded. What I'm bringing up in the ticket is that there are currently 3 different results, none of which are consistent with any others. 1 - Non eager is definitively correct. 2 - fallback-eager ignores :order so can't be trusted [*] 3 - new-eager does the opposite of #1 so is *never* correct [*] sorta, if you are aware of the issues/limitations it might still work out for you Both eager styles discard records so the impact is the same - it's just that new-eager is guaranteed to always be wrong. So now that I've gotten that bit out of the way, you mentioned "still loading all comments". I've spent a bit of time on the resultset trimming issue for has_one with :order and found: If all databases supported a postgres-style "distinct on" operation the solution would be trivial. Of course, mysql doesn't have anything like this. The only query-based approach to trimming the results that I've found to work reliably in mysql is to craft a rather heinous self-join: select comments.* from comments left join comments_prune on comments.post_id = comments_prune.post_id and comments.created_at < comments_prune.created_at where comments_prune.id is null and comments.post_id in (1,2,3,4,5) order by comments.created_at desc It would be possible to build such a query by parsing the :order option and adding appropriate 'and' join conditions but it gets quickly out of hand - imagine :order => 'foo asc, bar desc, wibble asc'. BLECH. The most bulletproof solution when :order is present on a has_one is to just issue 2 queries: first fetch the id and foreign_key ordered appropriately, build a list of ids that discards non-first-for-foreign_key ids and then fetch again using your pruned id list. But as I said, trimming the resultset isn't relevant to my ticket and I have to admit I'm not sure how much value would be generated by coding up trimming logic anyhow. Regards, Trevor -- -- Trevor Squires http://somethinglearned.com --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en -~----------~----~----~----~------~----~------~--~---
