I'm writing this post for two reasons:
- For some reason, I couldn't find a lot of information on ActiveRecord and
SELECT DISTINCT()
. - I'm hoping some Ruby / ActiveRecord guru will stumble across this and provide a better solution.
Recently, I was working on a project written in Ruby with ActiveRecord that required me to draw a list of host names from a database and perform some actions on them. The actions aren't really important here. What I had a hard time finding was the ActiveRecord way of performing a SELECT DISTINCT()
SQL query. My search came up empty, so I was forced to use the find_by_sql()
method below. Obviously, I've changed the code to make it generic and not violate any non-disclosure agreements I might have.
records = Class.find_by_sql(["SELECT DISTINCT(hostname) FROM table"])
This code works just fine, but it lacks a certain elegance. One thing that really bothers me is that I have to hardcode the table name into this line of code. If we make a change to the table name some time down the road, this line will have to be found and modified. Blech.
Dude,
You can get away with something like this:
records = Class.find(:all, :select => "DISTINCT(hostname)")
It's a bit cleaner.
Thanks for the heads up, Mando. I'll give that a go.
Check out this page! It has a lot of good examples on how to do the subtle SQL stuff (like DISTINCT).
http://railsmanual.com/module/ActiveRecord::Calculations::ClassMethods
@Greg:
Thanks for the link. That's a great resource.
I ended up just hard coding the SQL as well, and then filtering down to the values I needed:
records = SoapCall.find_by_sql(["SELECT DISTINCT(service) FROM soap_calls"]).collect{|x| x.service}
I'm using Rails 2.2.2 and we now have a :group option you can use in your find method that accomplishes the same thing as select distinct.
records = Class.find(:all, :group => "hostname")
That should give you your distinct list.
Conrad -
I'm trying to select the most recent chat_room_messages from all chat_rooms.
I tried the ChatRoomMessage.all :group => 'chat_room_id' approach, but it gave me the OLDEST message from each room, no matter how I ordered the messages.