转换一个SQL查询到Rails的AREL查询？ [英] Converting an SQL query to a Rails AREL query?

问题描述

我有一个工作的SQL查询得益于欧文Brandstetter修改在我先前的问题的订购了的has_many关系。

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

我怎么会变成这个SQL到ActiveRecords或AREL查询使用的范围是什么？

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

更新：

我前面问题有相关的架构和查询的完整描述。但基本上文章have_many指标和公制有一个名称和值。我查询订单文章的最高的值的最新的 公制与名称='得分'。

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

谢谢！

解决方法：

感谢Mattherick指着我正确的方向！他的查询工作在SQLite的，但由于PostgreSQL的（适用）不容忍含糊不清，最后的工作范围是：

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")

所谓问题，这Matherick提到在他的评论说明了这里的问题 - 为什么SQLite是有过错的接受不明确的查询，而不是PostgreSQL的拒绝。很好的例子使用的好处我的一样的数据库，为地方发展生产。

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.

推荐答案

我觉得这应该工作

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

这篇关于转换一个SQL查询到Rails的AREL查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！