执行原始SQL /调用范围时，Rails 3可能会出现ERB错误？ [英] Rails 3 Possible ERB Bug When Executing Raw SQL/Calling Scopes?

问题描述

在我的用户模型中，我有一个范围：

in my User model I have a scope:

scope :with_tournament_entrees, :include => :registers, :conditions => "registers.id IS NOT NULL"

我想查看此作用域及其生成的SQL产生：

I wanted to see the SQL being generated by this scope and it produces:

"SELECT \"users\".* FROM \"users\"  WHERE (registers.id IS NOT NULL)"

我看不到我添加的include参数的任何提及，并返回如果在控制台中使用 ActiveRecord :: Base.connection.execute 作为原始SQL执行此错误。另一方面，如果我要在控制台中查询User.with_tournament_entrees，则会生成正确的 8条记录，我需要没有任何错误，只要查看日志即可看到正在执行的SQL语句...

I don't see any mention of the include parameter I added and returns an error if executed in the console as raw SQL with the help of ActiveRecord::Base.connection.execute. If I was to query User.with_tournament_entrees in the console on the other hand it produces correct 8 records I need with no errors whatsoever, looking at the log I see the SQL statement being executed...

SELECT "users"."id" AS t0_r0, "users"."email" AS t0_r1, 
       "users"."encrypted_password" AS t0_r2, ....
       "registers"."id" AS t1_r0, "registers"."competition_id" AS t1_r1,
       "registers"."user_id" AS t1_r2 ... 
       FROM "users" LEFT OUTER JOIN "registers" ON "registers"."user_id" = "users"."id"
       WHERE (registers.id IS NOT NULL)

看起来不错，所以只是仔细检查一下，我复制了SQL并将其包装在 ActiveRecord中： Base.connection.execute 并完美执行-除非像我调用作用域时那样获得 8条记录，否则现在获得 8条记录 。

Which looks about right, so just to double check I copy the SQL and wrap it inside ActiveRecord::Base.connection.execute and it executes perfectly - except now instead of getting 8 records like I did when I called the scope I get 12 records.

有什么用？它执行完全相同的SQL，对吗？那为什么我得到不同的结果呢？我正在使用PostgreSQL数据库。

What gives? It's executing the exact same SQL right? So why am I getting different results? I'm using a PostgreSQL database.

哦，我知道您不应该在Rails 3中使用作用域，这就是为什么我要尝试使用SQL的原因我可以弄清楚如何进行ActiveRecordify（？）。

Oh and I know you shouldn't use scopes in Rails 3, which is why I'm trying to get at the SQL so I can figure out how to ActiveRecordify(?) it.

推荐答案

这由2个事实解释：
1。有效地添加联接意味着您将在用户行和寄存器行的每个有效组合中获得一个结果集行。因此，如果某个用户在寄存器表中有2条记录-您将获得该用户记录的两行。
2. Active Record知道上述内容，并且能够合并行，因此您将获得一个拥有两个寄存器的用户，而不是两个拥有一个寄存器的相同用户。

This is explained by 2 facts: 1. Adding joins effectively means you get a result set row per every valid combination of user row and a register row. So, if some user has 2 records in registers table - you get two rows for that user record. 2. Active Record knows the above and is capable of "merging" rows, so that you get one user with two registers, not two same users with one register each.

通过这种方式，运行裸查询会从MySQL的角度得出结果，即12行。由于某些行包含同一用户的字段，而只有多个寄存器的行，因此ActiveRecord可以将它们最多处理8条记录。

This way, running bare query yields the results from MySQL point of view, which is 12 rows. And ActiveRecord processes them down to 8 records, as some of the rows contain same user's fields, just multiple registers' ones.

希望这会有所帮助：）

这篇关于执行原始SQL /调用范围时，Rails 3可能会出现ERB错误？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！