Rails 3 不同的查询 [英] Rails 3 DISTINCT QUERY

问题描述

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

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.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))

会不会是 select 和 distinct 之间的逗号?

Could it be the comma between select and distinct?

推荐答案

DISTINCT 需要在 SELECT 之后直接，即

DISTINCT needs to be straight after the SELECT i.e.

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 要求你有一个不同字段的顺序语句:

PostgreSQL requires you have an order statement for the distinct field:

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

这篇关于Rails 3 不同的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！