1

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.

Community
  • 1
  • 1
djoll
  • 1,139
  • 1
  • 12
  • 31
  • 1
    how does your schema and associations look like? with this information it is easier to try your query ;). without more information maybe this works: scope :highest_score, lambda { Article.all.includes(:metrics).where(:name => "score").order("article_id desc and date_created desc") } – Matthias May 07 '13 at 10:53
  • Thanks Mattherick! I didn't want to double post the info, as my schema and associations are described in this first question: http://stackoverflow.com/questions/16382606/order-with-a-has-many-relationship — but basically Articles has_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 for any help you can give! dj – djoll May 07 '13 at 10:57

1 Answers1

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
Matthias
  • 4,355
  • 2
  • 25
  • 34
  • Thanks Mettherick. Unfortunately I get this error: ActiveRecord::StatementInvalid: PGError: ERROR: column metrics.score does not exist. LINE 1: ...P BY article_id ORDER BY metrics.article_id desc, metrics.sc... though I think your query misses a few pieces, namely the ordering of the Articles by the Metric.value for Metric.name = "score". Thanks for having a look! – djoll May 07 '13 at 11:44
  • Updated my answer, but I am not shure anymore :). I don´t get a ActiveRecord::StatementInvalid: PGError: ERROR. I simple tried it out with sqlite3 on my local machine. – Matthias May 07 '13 at 11:52
  • 1
    As I see now you are getting a PostgressError, maybe this question could help you http://stackoverflow.com/questions/16418504/pgerror-in-group-by-clause – Matthias May 07 '13 at 12:27
  • Thanks Mathherick. That link helps explain the problem and lead me to the solution using your query. Thanks again! ANS: scope :highest_score, lambda { Article.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") } – djoll May 07 '13 at 23:53
  • Also, the lambda isn't needed (I think only needed for dynamic scopes where you are passing in a variable) so I removed it. – djoll May 08 '13 at 00:30
  • 1
    Yes I think the lambda is not necessary in rails 3.2, but rails 4 you will get a deprecation warning, or directly an error, if I´m not mistaken. – Matthias May 08 '13 at 09:06