由于未知的OID被视为字符串,ActiveRecord和SQL未返回相同结果的情况字符串
我遇到问题。早些时候,我发布了一个类似的问题,我将要问哪个已经回答,可以在这里找到相关背景ActiveRecord Count and SQL count do not match由于未知的OID被视为字符串,ActiveRecord和SQL未返回相同结果的情况字符串
这个问题与计算查询结果有关。在这个问题中,我没有得到相同的计数结果,而是得到了不同的对象结果。
这里是ActiveRecord的代码:
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)")
这将返回[#<Account:0x007f96bf143c70 id: >]
和我的SQL输出后,得到这个消息unknown OID 2249: failed to recognize type of 'row'. It will be treated as String.
如果我在数据库中运行上面的SQL代码,我找回我需要什么:
-------row-------
(1234,[email protected])
什么原因导致这种不匹配,我该如何避免它。我发现这篇文章关于自定义postgres类型Custom postgres types,但这看起来像我需要到事情的本质,我想避免这种情况。有人知道为什么这是一个问题吗?
你得到了这个问题,因为ActiveRecord无法将这一行识别为您帐户表中的行。 AR不解析你的sql,它不知道如何处理匿名cortage。
如果使用find_by_sql
您选定的属性不会映射到您的模型正确,但仍然可以访问,那么尝试:
result.id
result.email
但你也有两种方法来解决这个问题。
首先(这是非常的hackish,但简单的解决方案),将您的SQL来Arel
,即vaild的范围:
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不同方法:
Account.unverified_with_no_associations.select(:id, :email).distinct
秒(这是更好的解决方案):
不要使用sql di rectly。用Arel
()或squeel
(https://github.com/activerecord-hackery/squeel)
重写您的范围感谢rootatdarkstar。阿雷尔做到了。 –