Converting an SQL query to a Rails AREL query?

Question

I have a working SQL query thanks to the help of Erwin Brandstetter in my earlier question 'Order with a has_many relationship'.

How would I turn this SQL into an ActiveRecords or AREL query for use in a scope?

SELECT a.*
FROM   articles a
LEFT   JOIN (
   SELECT DISTINCT ON (article_id)
          article_id, value
   FROM   metrics m
   WHERE  name = 'score'
   ORDER  BY article_id, date_created DESC
   ) m ON m.article_id = a.id
ORDER  BY m.value DESC;

The closest I have come is with a friends help...

scope :highest_score, select("articles.*").joins("LEFT JOIN (SELECT 
      DISTINCT ON (article_id) article_id, value FROM metrics WHERE name = 
      'score' ORDER  BY article_id, date_created DESC) ON metrics.article_id = 
      articles.id").order("metrics.value DESC")

...which gives me an error:

ActiveRecord::StatementInvalid: PGError:
      ERROR:  subquery in FROM must have an alias

UPDATE:

My earlier question has a complete description of the relevant schema and query. But basically Articles have_many Metrics, and a Metric has a name and a value. My query orders Articles by the highest value of the most recent Metric with name = 'score'.

Thanks!

SOLUTION:

Thanks to Mattherick for pointing me in the correct direction! His query works on SQLite, but due to postgresql's (appropriate) intolerance for ambiguity, the final working scope is:

scope :highest_score, joins(:metrics).where("metrics.name" => "score")
      .order("metrics.value desc").group("metrics.article_id", 
      "articles.id", "metrics.value", "metrics.date_created")
      .order("metrics.date_created desc")

The SO question which Matherick referred to in his comments explains the problem here — and why sqlite is at fault for accepting ambiguous queries rather than postgresql for refusing. Good example for me of the benefit of using the same database for local development as for production.

Answer 1

I think this should work

class Article < ActiveRecord::Base

  scope :highest_score, lambda { Article.joins(:metrics).where("metrics.name" => "score").order("metrics.value").group(:article_id).order("metrics.article_id desc").order("metrics.date_created desc") }

end