Ruby on Rails

On Friday 03 September 2010, Marnen Laibow-Koser wrote:
> I had a couple more ideas just after I posted.
>
> Marnen Laibow-Koser wrote:
> > Michael Schuerig wrote:
> [...]
>
> >> :joins with a symbol does an inner join.
> >
> > So use an SQL fragment if you need an outer join (which, on
> > reflection, I suppose you do).
>
> 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".

> >> When there are several draft
> >> versions, which one would I get? Answer: any. I want a specific
> >> one: the latest.
> >
> > You can specify conditions and sort order on fields from joined
> > tables. That should do the trick.
>
> Another (possibly zany) idea: group the versions by article_id.
>
> Are you ultimately trying to retrieve the latest version only for
> 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: 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). 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)

With an index on versions(article_id, updated_at) this isn't even too
bad on the database. There are other ways with uncorrelated sub-selects
and outer joins.

Let me emphasize that my problem is *not* getting the right data out of
the database. I can get it in a single query. And I can make
ActiveRecord do this. In order to get there, I had to write more literal
SQL than I would have liked, for instance the entire condition
containing the sub-select. AFAICT, ARel doesn't make things any easier,
it only mirrors the structure of literal, but plain SQL in convoluted
Ruby.

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/

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