使用Postgres和Activerecord在多个列中查找具有重复值的记录的最佳方法是什么?
What is the best way to find records with duplicate values across multiple columns using Postgres, and Activerecord?
我找到了此解决方案此处:
User.find(:all,:group => ; [:first,:email],:having => count(*)> 1)
但是它没有似乎可以和postgres一起使用。我收到此错误:
But it doesn't seem to work with postgres. I'm getting this error:
PG :: GroupingError:错误: parts.id列必须出现在GROUP BY子句中或在聚合函数中使用
PG::GroupingError: ERROR: column "parts.id" must appear in the GROUP BY clause or be used in an aggregate function
推荐答案经过测试的&工作版本
User.select(:first,:email).group(:first,:email).having("count(*) > 1")此外,这有点无关,但方便。如果要查看每个组合的查找时间,请在末尾加上.size:
Also, this is a little unrelated but handy. If you want to see how times each combination was found, put .size at the end:
User.select(:first,:email).group(:first,:email).having("count(*) > 1").size,您将获得如下所示的结果集:
and you'll get a result set back that looks like this:
{[nil, nil]=>512, ["Joe", "test@test"]=>23, ["Jim", "email2@gmail"]=>36, ["John", "email3@gmail"]=>21}认为这很酷,以前从未见过。
Thought that was pretty cool and hadn't seen it before.
感谢Taryn,这只是她答案的调整版本。
Credit to Taryn, this is just a tweaked version of her answer.
更多推荐
使用Active Record,Rails&查找具有多个重复字段的行Postgres
发布评论