由于未知的OID被视为字符串，因此ActiveRecord和SQL无法返回相同结果的情况 [英] A situation where ActiveRecord and SQL do not return the same results due to unknown OID being treated as String

问题描述

我有问题。早些时候，我发布了一个与我要问的问题类似的问题，该问题已得到解答，可以在上下文中找到 ActiveRecord计数和SQL计数不匹配

I have a problem. Earlier I posted a similar question to the one I am about to ask which was answered and can be found here for context ActiveRecord Count and SQL count do not match

该问题与计算查询结果有关。在这个问题中，不是得到相同的计数结果，而是得到不同的对象结果。

That question had to do with counting a query result. In this question, instead of not getting the same count result, I am getting different object results.

这是ActiveRecord代码：

Here is the ActiveRecord code:

scope :unverified_with_no_associations, -> {
  find_by_sql("SELECT 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)")

这将返回 [＃<帐户：0x007f96bf143c70 id：>] ，并且在sql输出 unknown OID 2249之后收到此消息：无法识别行的类型。

This returns [#<Account:0x007f96bf143c70 id: >] and I get this message after the sql output unknown OID 2249: failed to recognize type of 'row'. It will be treated as String.

如果我在数据库中运行上述sql代码，则会检索到我需要的内容：

If I run the above sql code in the database, I retrieve what I need:

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

是什么原因导致这种不匹配以及如何避免这种情况。我发现了有关自定义postgres类型的帖子自定义postgres类型，但这看起来像是我需要弄清事情的真相，而我想避免这种情况。有人知道为什么这是问题吗？

What causes this mismatch and how can I avoid it. I found this post about custom postgres types Custom postgres types but this looks like I would need to get to the nitty gritty of things and I would like to avoid this. Does someone know why this is a problem?

推荐答案

您遇到了这个问题，因为ActiveRecord无法将这一行识别为您的行帐户表。 AR无法解析您的sql，并且不确定如何使用匿名命令。

You got this problem because ActiveRecord can't recognize this row as row from your account table. AR not parsing your sql and it's not sure what to do with anonymous cortage.

如果您使用 find_by_sql 属性无法正确映射到模型，但仍可访问，因此请尝试：

If you use find_by_sql your selected attributes not mapped to your model properly but still accessible, so try:

result.id
result.email

但是您也有两种解决方法。

But you also have two ways to fix that.

首先（这是非常hacky但很简单的解决方案），将您的sql转换为 Arel ，适用于以下范围：

First (it's very hackish but easy solution), transform your sql to Arel, that vaild for the scopes:

scope :unverified_with_no_associations, -> {
  send(:default_scoped).from(Arel.sql("(SELECT * 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))  
                AS accounts"))

...并调用AR不同方法：

... and call AR distinct method:

Account.unverified_with_no_associations.select(:id, :email).distinct

第二（这是更好的解决方案）：

Second (it's much better solution):

不要使用sql直接。用 Arel （ https://github.com/ rails / arel ）或使用 squeel （ https://github.com/activerecord-hackery/squeel ）

Don't use sql directly. Rewrite your scope with Arel (https://github.com/rails/arel) or with squeel (https://github.com/activerecord-hackery/squeel)

这篇关于由于未知的OID被视为字符串，因此ActiveRecord和SQL无法返回相同结果的情况的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！