Ruby on Rails

Michael Schuerig wrote:
> On Friday 03 September 2010, Marnen Laibow-Koser wrote:
>>
>> On further reflection, I don't know why you would need an outer join
>> unless not every Article has a Version associated. Is that the case?
>
> I need an *inner* join with the latest of several versions, possibly
> additionally meeting the condition of being in state "published".
>
Then I'm not sure why :joins won't do the trick.

>> each article? That's what you imply, but a couple of things you
>> wrote made me thing that you might want to retrieve multiple
>> versions. Which is it?
>> [...]
>
> If you are unsure of this, that is the question you ought to have asked
> before even starting to make suggestions.

The answer doesn't make a heck of a lot of difference to my suggestions,
except in the last layer of details in the join, which did not
originally concern me. :)

> The answer: Yes, I'm trying to
> display lists of either only the latest version or the only latest
> published version of each article, if any such version exists.
>
> To find the latest of several things, you need to compare them (or have
> the comparison pre-packaged in an index).

Of course.

> In the context of a select
> statement for articles, finding the latest version can take the form of
> a correlated sub-select:
>
> SELECT articles.*, versions.* FROM articles
> JOIN versions ON articles.id = versions.article_id
> WHERE versions.updated_at =
> (SELECT MAX(v.updated_at) FROM versions AS v
> WHERE v.article_id = articles.id)

Yes, that solution had occurred to me. But the subquery is unnecessary,
I think:

[outer SELECT and JOIN as above]
ORDER BY article.id, version.created_at desc
GROUP BY article.id
[further JOINs may be necessary in some DBs, but the principle holds]

This will give equivalent results and may be more ActiveRecord-friendly.

OTOH, I *have* once or twice used subqueries with AR. It isn't my
favorite thing to do, but it is possible if absolutely necessary (which
I don't think it is here).

[...]
>
> As I wrote already in closing of my original question: I appreciate any
> suggestions how to do this elegantly in ARec.
>
> Michael
>
> --
> Michael Schuerig
> mailto:michael@schuerig.de
> http://www.schuerig.de/michael/

Best,
-- 
Marnen Laibow-Koser
http://www.marnen.org
marnen@marnen.org

Sent from my iPhone
--
Posted via http://www.ruby-forum.com/.

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To post to this group, send email to rubyonrails-talk@googlegroups.com.
To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.