Rails 3 DISTINCT QUERY

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 "b开发者_如何学JAVAadges".*, 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?

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

Could try using group

@user.badges.group(:id)

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

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

Try this

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

I had a similar issue, using PostgreSQL too.

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.

There are at least three ways to get a unique list of badges from the user:

1. @user.badges.group(:id)
2. @user.badges.select("DISTINCT badges.*")
3. Or add :uniq => true in the has_many :badges, :through line

Of course, you can chain in your order("badges.created_at DESC") to the query also.

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

This one work for me.