ruby-on-rails - Rails 3 DISTINCT QUERY

Question

有一个用户，UserBadge和徽章表。它们通过has_many连接。一个用户可以有多个相同的徽章，但是我想查询一个唯一的列表。

@user.badges.select("DISTINCT id").order("created_at DESC")

引发错误:

SQLite3::SQLException: near "DISTINCT": syntax 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))

难道是选择和区分之间的逗号？

Answer 1

SELECT之后，DISTINCT需要保持平直，即

SELECT DISTINCT(badges.id), "badges".* FROM "badges" INNER JOIN "userbadges" ON "badges".id = "userbadges".badges_id WHERE (("userbadges".user_id = 1))

尝试:

@user.select("DISTINCT(badges.id), badges.*").badges.order("badges.created_at DESC")

PostgreSQL要求您具有用于不重复字段的订单声明:

@user.select("DISTINCT(badges.id), badges.*").badges.order("badges.id").order("badges.created_at DESC")