Ruby-on-rails Rails 3 不同的查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5901828/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Rails 3 DISTINCT QUERY
提问by John
Have a User, UserBadge, and Badge table. They are connected through a has_many through. A user can have multiple of the same badge but I want to query a unique list.
有一个 User、UserBadge 和 Badge 表。它们通过 has_many 连接。一个用户可以拥有多个相同的徽章，但我想查询一个唯一的列表。
@user.badges.select("DISTINCT id").order("created_at DESC")
It's throwing an error:
它抛出一个错误：
SQLite3::SQLException: near "DISTINCT": syntax error:
What would be the proper syntax?
什么是正确的语法？
EDIT: ADDED WHOLE ERROR
编辑：添加了整个错误
SQLite3::SQLException: near "DISTINCT": syntax error: SELECT "badges".*, DISTINCT badges.id FROM "badges" INNER JOIN "userbadges" ON "badges".id = "userbadges".badges_id WHERE (("userbadges".user_id = 1))
Could it be the comma between select and distinct?
它可能是 select 和 distinct 之间的逗号吗？
回答by Mike
DISTINCT needs to be straight after the SELECT i.e.
DISTINCT 需要在 SELECT 之后直接，即
SELECT DISTINCT(badges.id), "badges".* FROM "badges" INNER JOIN "userbadges" ON "badges".id = "userbadges".badges_id WHERE (("userbadges".user_id = 1))
Try:
尝试：
@user.select("DISTINCT(badges.id), badges.*").badges.order("badges.created_at DESC")
PostgreSQL requires you have an order statement for the distinct field:
PostgreSQL 要求你有一个用于不同字段的订单语句：
@user.select("DISTINCT(badges.id), badges.*").badges.order("badges.id").order("badges.created_at DESC")
回答by Kelend
Could try using group
可以尝试使用组
@user.badges.group(:id)
回答by ricsrock
Set
放
:uniq => true
on the association. Or create a separate association just for a user's unique badges.
在协会上。或者仅为用户的独特徽章创建单独的关联。
http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html#method-i-has_many
http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html#method-i-has_many
Something like
就像是
Class User < ActiveRecord::Base
has_many :user_badges
has_many :badges, :through => :user_badges
has_many :unique_badges, :through => :user_badges, :source => :badges, :uniq => true
#untested
end
回答by fl00r
Try this
尝试这个
@user.badges.select("DISTINCT(badges.id)").order("badges.created_at DESC")
回答by Jure Triglav
I had a similar issue, using PostgreSQL too.
我也有类似的问题，也使用 PostgreSQL。
My code looked like this:
我的代码如下所示：
User.find(2).devices.group('token')
Which caused an error:
这导致了错误：
Device Load (0.8ms) SELECT "devices".* FROM "devices" WHERE "devices"."user_id" = 2 GROUP BY token
ActiveRecord::StatementInvalid: PG::Error: ERROR: column "devices.id" must appear in the GROUP BY clause or be used in an aggregate function
Solution:
解决方案：
Changing that line to:
将该行更改为：
User.find(2).devices.select('distinct token')
Worked perfectly:
完美运行：
Device Load (1.3ms) SELECT distinct token FROM "devices" WHERE "devices"."user_id" = 2
=> [#<Device token: "example">, #<Device token: "example1">, #<Device token: "example2">, #<Device token: "example3">, #<Device token: "example4">, #<Device token: "example4">]
Update:
更新：
Sorry, I haven't read the original question thoroughly and have actually responded to another post in the thread, complaining of the #group method not working with PostgreSQL. It's still a valuable answer though, so I'll leave it.
抱歉，我没有彻底阅读原始问题，实际上已经回复了线程中的另一篇文章，抱怨 #group 方法不适用于 PostgreSQL。不过，这仍然是一个有价值的答案，所以我会留下它。
回答by konyak
There are at least three ways to get a unique list of badges from the user:
至少有三种方法可以从用户那里获取唯一的徽章列表：
@user.badges.group(:id)@user.badges.select("DISTINCT badges.*")- Or add
:uniq => truein thehas_many :badges, :throughline
@user.badges.group(:id)@user.badges.select("DISTINCT badges.*")- 或添加
:uniq => true的has_many :badges, :through行
Of course, you can chain in your order("badges.created_at DESC")to the query also.
当然，您也可以链接order("badges.created_at DESC")到查询中。
回答by Yoann Augen
@user.badges.select("id").order("created_at DESC").uniq
This one work for me.
这个对我有用。
