Rails 3 DISTINCT QUERY

Question

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

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?

Answer 1

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

Try:

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

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

Answer 2

Could try using group

@user.badges.group(:id)