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.
Get Slaptijack updates delivered to your Inbox or RSS Reader for free!
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.