Ruby on Rails

On Friday 03 September 2010, Marnen Laibow-Koser wrote:
> 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.

Because :joins, doing a simple inner join on articles and versions,
creates a relation with a row for each version. I want one row, made up
from an article with its latest (published) version. What condition
would you put on the join, apart from corresponding keys, in order to
achieve that?

>
> > 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]

How does this ensure that I get the latest version? Before you go and
suggest using MAX(updated_at) on the groups, consider how groups work or
get Bill Karwin's "SQL Antipatterns" and read ch. 15, "Ambiguous
Groups".

Also, for any suggestions containing an ORDER BY versions.updated_at in
the outer SELECT: The sorting is applied at the very end, not somewhere
in between

Michael

--
Michael Schuerig
mailto:michael@schuerig.de
http://www.schuerig.de/michael/

--
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.