使用“average()”时如何在 AREL 中使用“to_sql”？

发布于 2024-11-10 04:20:54 字数 537 浏览 0 评论 0原文

我正在尝试从 AREL 获取 SQL，但如果我使用 average(:stars) ，它不起作用：

这有效：

Review.where("reviewed_user_id = ?", self.reviewed_user_id).to_sql
#=> "SELECT `reviews`.* FROM `reviews` WHERE (reviewed_user_id = 3)"

这会导致 NoMethodError：

Review.where("reviewed_user_id = ?", self.reviewed_user_id).average(:stars).to_sql
#=> undefined method `to_sql' for 3:Fixnum

所以这意味着to_sql 正在 AREL 的结果上调用，而不是在 AREL 对象上调用 - 但为什么呢？

如何获取生成的SQL？

原文

I am trying to the get SQL from AREL, but it does not work in case I use average(:stars) :

This works:

Review.where("reviewed_user_id = ?", self.reviewed_user_id).to_sql
#=> "SELECT `reviews`.* FROM `reviews` WHERE (reviewed_user_id = 3)"

This causes NoMethodError:

Review.where("reviewed_user_id = ?", self.reviewed_user_id).average(:stars).to_sql
#=> undefined method `to_sql' for 3:Fixnum

So that means that to_sql is getting called on the result of the AREL instead of on the AREL object - but why?

How to get the generated SQL ?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

酒与心事 2024-11-17 04:20:54

发生这种情况的原因是平均方法位于 ActiveRecord::Relation 上，而不是 Arel 上，这会强制计算。

m = Review.where('id = ?', 42).method(:average)
#=> #<Method: ActiveRecord::Relation(ActiveRecord::Calculations)#average>
m.source_location  # or m.__file__ if you're on a different version of Ruby
#=> ["/Users/jtran/.rvm/gems/ruby-1.9.2-p0/gems/activerecord-3.0.4/lib/active_record/relation/calculations.rb", 65]

通过检查 ActiveRecord::Calculations 的内部结构，您可以得出如何获取它使用的 SQL。

my_reviewed_user_id = 42
relation = Review.where('reviewed_user_id = ?', my_reviewed_user_id)
column = Arel::Attribute.new(Review.unscoped.table, :stars)
relation.select_values = [column.average]
relation.to_sql
#=> "SELECT AVG(\"reviews\".\"stars\") AS avg_id FROM \"reviews\" WHERE (reviewed_user_id = 42)"

如果您在控制台上工作，请小心。 ActiveRecord::Relation 会缓存内容，因此如果您在控制台中逐行输入上述内容，它实际上不起作用，因为漂亮打印会强制建立关系。然而，用分号分隔上面的内容并且不换行是可行的。

或者，您可以直接使用 Arel，如下所示：

my_reviewed_user_id = 42
reviews = Arel::Table.new(:reviews)
reviews.where(reviews[:reviewed_user_id].eq(my_reviewed_user_id)).project(reviews[:stars].average).to_sql
#=> "SELECT AVG(\"reviews\".\"stars\") AS avg_id FROM \"reviews\" WHERE \"users\".\"reviewed_user_id\" = 42"

The reason this is happening is because the average method is on ActiveRecord::Relation, not Arel, which forces the computation.

m = Review.where('id = ?', 42).method(:average)
#=> #<Method: ActiveRecord::Relation(ActiveRecord::Calculations)#average>
m.source_location  # or m.__file__ if you're on a different version of Ruby
#=> ["/Users/jtran/.rvm/gems/ruby-1.9.2-p0/gems/activerecord-3.0.4/lib/active_record/relation/calculations.rb", 65]

By checking out the internals of ActiveRecord::Calculations, you can derive how to get at the SQL that it uses.

my_reviewed_user_id = 42
relation = Review.where('reviewed_user_id = ?', my_reviewed_user_id)
column = Arel::Attribute.new(Review.unscoped.table, :stars)
relation.select_values = [column.average]
relation.to_sql
#=> "SELECT AVG(\"reviews\".\"stars\") AS avg_id FROM \"reviews\" WHERE (reviewed_user_id = 42)"

Careful if you're working at the console. ActiveRecord::Relation caches things so if you type the above into the console line by line, it will actually not work, because pretty-printing forces the relation. Separating the above by semicolons and no new lines, however, will work.

Alternatively, you can use Arel directly, like so:

my_reviewed_user_id = 42
reviews = Arel::Table.new(:reviews)
reviews.where(reviews[:reviewed_user_id].eq(my_reviewed_user_id)).project(reviews[:stars].average).to_sql
#=> "SELECT AVG(\"reviews\".\"stars\") AS avg_id FROM \"reviews\" WHERE \"users\".\"reviewed_user_id\" = 42"

回复收藏 0 原文

~没有更多了~