Rails join with max condition

Question

Given these two models

class Article < AR::Base
  belongs_to :tag

  default_scope order('id DESC')
end

class Tag < AR::Base
  has_many :articles

  scope :featured, order(:ordering).limit(5)
end

I'm trying to join these two tables in a manner such I can retrieve a list of featured tags and for each of them the latest article in that tag with a single query, something like

Tag.featured.each do |tag|
  p tag.name
  p tag.articles.first.title # this will fetch the article in the tag with the highest id
end

Written in this way this code has the (n+1) query problem, I'm trying to optimize it because it will be run very frequently. I've excluded an includes call in the featured scope because it will load all the Article in the first 5 tags (a lot of articles...)

In plain old SQL I can join this two tables using a query like this

SELECT tag_id, MAX(id) FROM articles GROUP BY tag_id
# or in Rails
Article.select('MAX(id), tag_id').group(:tag_id)
+--------+---------+
| tag_id | MAX(id) |
+--------+---------+
| 14     | 26787   |
...
| 1      | 27854   |
| 5      | 27780   |
| 0      | 10953   |
+--------+---------+

as a join table I can retrieve all the data with a single query.

How to port that in Rails and ActiveRecord?

update

The full query i need executed in AR context is

SELECT a.*, t.*
FROM tags t
JOIN (SELECT MAX(id) AS latest, tag_id FROM articles GROUP BY tag_id) j ON j.tag_id = t.id
JOIN articles p ON j.latest = a.id
LIMIT 5

I tried AR#find_by_sql method with the latest query, I get the correct resultset but then I cannot navigate through objects

sql = '...' # This holds the previous query
Tag.find_by_sql(sql).first.class
=> "Tag"
Tag.find_by_sql(sql).first.articles.first
=> nil # why???

update 2

Tried also with

Tag.joins('JOIN ... all my query join part here ...').first.articles.first
=> nil

However I noticed that I can use my articles field directly as Tag fields, i.e. I can write

Tag.find_by_sql(...).first.title # where title is a field of Article class
Tag.joins(...).first.title # where title is a field of Article class

but obviously I can not call Article instance methods.

Answer 1

Found a partial solution to my issue. I could fetch the needed record with two queries and still having data loaded as AR objects:

# This will fetch the needed article ids
ids = Article.select('MAX(id) as max_id').group(:tag_id).map(&:max_id)
# This return the top tags each one with the article needed in articles.first
Tag.includes(:articles).where('article.id IN (?)', ids).limit(5).each do |t|
  t.name # gives the tag name
  t.articles # gives [<#Article...>] i.e. an array with a single article
end

Rails will do three queries:

1. one to select the ids
2. one to select the Tags ids in returned_ids of point 1
3. the latest to fetch with a (very strange) LEFT OUTER JOIN to fetch data from the two tables, using WHERE a.id IN (ids) AND t.id IN (returned_ids) as condition

I can't go with a WHERE condition like

where('article.id IN (SELECT MAX(id) from articles)')

because MySQL has a bug and it would think the subquery is derived. It seems that it will be fixed in 6.0.

Still looking for better answers, maybe a JOIN such as the one in my question which loads AR objects.