On 29 Apr 2008, at 22:53, Trevor Squires wrote:
> > 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 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". > Yup, agree with you on all that. I was just wondering if we could be smart about that, in addition to resolving the issue on the ticket > 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. Yeah, ugh. If we grouped by post_id are we guaranteed anything sensible (I seem to remember that what the db gives for non agregate, not grouped on columns can't be relied upon to be anything specific other than one of the rows but I can't remember). > > > 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. > It would be nice to have in addition to what's in your ticket, it seems non trivial though. Fred --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
