SQL查询和ActiveRecord.find_by_sql如何返回不同的结果？ [英] How is it possible that SQL query and ActiveRecord.find_by_sql return different results?

问题描述

我有一个难题。我在我的 Account 类上编写了一个作用域，该作用域找到了一堆满足较长条件的帐户对象。这是我的范围：

 范围：unverified_with_no_associations，-> {
 find_by_sql（ SELECT COUNT（DISTINCT（accounts.id，account.email））从级别为0且ID不在ID中的帐户
（从验证中选择DISTINCT（account_id）从ID）中且ID不在
（从职位选择DISTINCT（account_id））和ID不在
（从编辑中选择DISTINCT（account_id））和ID不在
（从帖子中选择DISTINCT（account_id））和ID不在
（从评论中选择DISTINCT（account_id））和ID不在
（从荣誉中选择DISTINCT（sender_id））和ID不在
（SELECT DISTINCT（account_id）从堆栈中，而没有account_id NULL））
} 
  

当我使用 Account.unverified_with_no_associations 我收到此对象 [＃< Account：0x007f7fc94d79c0 id：nil>] 。

但是，当我连接到数据库并按原样执行sql时：

 从级别为0且ID不在
中的帐户中选择COUNT（DISTINCT（accounts.id，account.email））从
中选择ID（不在验证中选择DISTINCT（account_id））并且ID不在$ b $中b（从职位选择DISTINCT（account_id））和ID不在
（从编辑选择DISTINCT（account_id）从ID）和ID不在
（从帖子中选择DISTINCT（account_id））和ID不在
（从评论中选择DISTINCT（account_id））和ID不在
（从荣誉中选择DISTINCT（sender_id））和ID不在
（从堆栈中选择DISTINCT（account_id）的account_id不为空） ; 
  

我收到电话号码 221214 。为什么会有不同的结果？我排除了连接到不同数据库的可能性。我检查了我的 .env 文件，并确认我和应用程序位于同一数据库中。有人知道为什么我会在查询中获得如此大的差异吗？

------------ UPDATE --------

我发现 find_by_sql 不喜欢包含 COUNT 在其论点中。当我从sql中删除 COUNT（）并稍后执行 .count 方法时，我检索到匹配的数字。 / p>

但是，当我同时使用这两种方法时，仍然得到不同的结果。

我真正需要的是与众不同的 accounts.id 和 accounts.email ，但方法不会返回相同的输出。

例如，当我执行sql版本时，收到的输出看起来像这样：

  row 
 --------- 
（1234，me @ gmail.com）
  

但是当我使用activerecord版本时，我得到以下信息：

  [＃&Account：0x007fdc9ec104d0 id：nil> ] 
  

，但没有随附的电子邮件。

---- UPDATE＃3 ------

在我的sql输出中，我也得到这个 OID 2249未知：无法识别行的类型。 这是什么意思？

解决方案

您在两种方法中都得到正确的结果

如果仅在选择中使用 count 则您总是会得到一个作为查询结果的数字。因此，您从数据库中获得的结果是可以预期的。

在Rails情况下，您尝试通过 scope 获取一些记录集在选择语句中 count 。如果您的查询中有 count ，则可能会得到空集。

尝试 count_by_sql 方法 http://apidock.com/rails/ActiveRecord/Base / count_by_sql / class
来获取记录数而不是空集。

并在没有作用域的情况下使用它，但要使用类方法：

  def self.unverified_with_no_associations（）
 self.count_by_sql（ SELECT COUNT（DISTINCT（accounts.id，account.email）） ）从级别为0的帐户中，并且ID不在
中（从验证中选择DISTINCT（account_id）），而ID不在
中（从位置中选择DISTINCT（account_id）从头寸开始），且ID不在
中（从编辑中选择DISTINCT（account_id）和ID不在
（从帖子中选择DISTINCT（account_id）从帖子）和ID不在
（从评论中选择DISTINCT（account_id）从评论）和ID不在
 （选择DI STINCT（sender_id）来自荣誉）和ID不在
中（SELECT DISTINCT（account_id）来自堆栈，而account_id不为NULL））
结束
  

I have a conundrum. I am writing a scope on my Account class that finds a bunch of account objects that meet a lengthy condition. Here is my scope:

 scope :unverified_with_no_associations, -> {
  find_by_sql("SELECT COUNT(DISTINCT(accounts.id, accounts.email)) FROM accounts WHERE level = 0 AND id NOT IN
                (SELECT DISTINCT(account_id) FROM verifications) AND id NOT IN 
                (SELECT DISTINCT(account_id) FROM positions) AND id NOT IN
                (SELECT DISTINCT(account_id) FROM edits) AND id NOT IN
                (SELECT DISTINCT(account_id) FROM posts) AND id NOT IN
                (SELECT DISTINCT(account_id) FROM reviews) AND id NOT IN
                (SELECT DISTINCT(sender_id) FROM kudos) AND id NOT IN
                (SELECT DISTINCT(account_id) FROM stacks WHERE account_id IS NOT NULL)")
}

When I execute this scope by with Account.unverified_with_no_associations I receive this object back [#<Account:0x007f7fc94d79c0 id: nil>].

However when I connect to the database and execute the sql as is:

SELECT COUNT(DISTINCT(accounts.id, accounts.email)) FROM accounts WHERE level = 0 AND id NOT IN
                (SELECT DISTINCT(account_id) FROM verifications) AND id NOT IN 
                (SELECT DISTINCT(account_id) FROM positions) AND id NOT IN
                (SELECT DISTINCT(account_id) FROM edits) AND id NOT IN
                (SELECT DISTINCT(account_id) FROM posts) AND id NOT IN
                (SELECT DISTINCT(account_id) FROM reviews) AND id NOT IN
                (SELECT DISTINCT(sender_id) FROM kudos) AND id NOT IN
                (SELECT DISTINCT(account_id) FROM stacks WHERE account_id IS NOT NULL);

I receive the number 221214. Why would it be that I get to differenct results? I've ruled out the possibility of connecting onto different databases. I've checked my .env files and have confirmed that I am in the same database as my application. Does anyone know why I would get such a difference in queries?

------------UPDATE--------

I discovered that find_by_sql does not like the inclusion of COUNT in its argument. When I remove the COUNT() from the sql and later execute a .count method I retrieve the matching number.

However, I still get different results when I use both methods.

What I really need are distinct accounts.id and accounts.email but the methods do not return the same output.

For example, when I execute the sql version I receive an output that looks like this:

row
---------
(1234,[e-mail])

but when I use the activerecord version I get this:

[#<Account:0x007fdc9ec104d0 id: nil>]

but with no accompanying email.

----UPDATE #3------

Also on my sql output I get this unknown OID 2249: failed to recognize type of 'row'. It will be treated as String. What does this mean?

解决方案

You have right result in both examples.

If you use only count in select you always got a number as result of query. So your result from database is expectable.

In rails case you trying to get some set of records by scope with count in select statement. It's expectable to got empty set if you have count in your query.

Try count_by_sql method http://apidock.com/rails/ActiveRecord/Base/count_by_sql/class to get number of records instead of empty set.

And use it without scope, but with class method:

def self.unverified_with_no_associations()
  self.count_by_sql("SELECT COUNT(DISTINCT(accounts.id, accounts.email)) FROM accounts WHERE level = 0 AND id NOT IN
            (SELECT DISTINCT(account_id) FROM verifications) AND id NOT IN 
            (SELECT DISTINCT(account_id) FROM positions) AND id NOT IN
            (SELECT DISTINCT(account_id) FROM edits) AND id NOT IN
            (SELECT DISTINCT(account_id) FROM posts) AND id NOT IN
            (SELECT DISTINCT(account_id) FROM reviews) AND id NOT IN
            (SELECT DISTINCT(sender_id) FROM kudos) AND id NOT IN
            (SELECT DISTINCT(account_id) FROM stacks WHERE account_id IS NOT NULL)")
end

这篇关于SQL查询和ActiveRecord.find_by_sql如何返回不同的结果？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！