Tutorial :ActiveRecord select attributes without AR objects



Question:

I have a table with MANY rows, I need just the IDs of certain rows. The slow way is to call

SomeARClass.find(:all, :conditions => {:foo => true}, :select => :id)  

This returns AR Objects...

Is there a way to call a select on a class and have it return a plain old ruby data structure. Something like this:

SomeARClass.select(:id, :conditions => {:foo => true})  -> [1,2,3]  


Solution:1

ActiveRecord::Base.connection.select_all(sql)  

or

SomeARClass.connection.select_all(sql)  

This is what you want to use. It returns an array of hashes. It should be used sparingly though. Hand coded sql is what ActiveRecord was built to replace. I only use in in really performance critical areas where constructing and returning AR objects is too slow.


Solution:2

I don't think there is anything like SomeARClass.select(:id, :conditions => {:foo => true}) but you have two options

  1. SomeARClass.find(:all, :conditions => {:foo => true}, :select => :id).map(&:id)  #=> [1,2,3,4]  
  2. id_hash = ActiveRecord::Base.connection.select_all('select id from tablename')  #=>  [{"id"=>"1"}, {"id"=>"2"}, {"id"=>"3"}, {"id"=>"4"}]    id_hash.map(&:values).flatten  #=>  ["1", "2", "3", "4"]  

The second option returns only a hash and not Active record objects but it does looks a bit hackish.


Solution:3

The pluck method is exactly what you want, without compromising the SQL bad practice:

SomeARClass.where(:foo => true).pluck(:id)  

I believe this should be the selected answer!


Solution:4

Short answer:

Notes:

There is small difference between pluck and select_all:

If you pluck data - ActiveRecord map data to Ruby objects, like Dates, Enums, Models, etc, and it could be different than you expect.

You can notice that the result below is a string:

2.5.1 :001 > ActiveRecord::Base.connection.select_all("select created_at from some_ar_class where id = 1 limit 1").rows.first     (0.6ms)  select created_at from some_ar_classes where id = 1 limit 1   => ["2018-10-01 01:12:31.758161"]  

While pluck return Dates

2.5.1 :002 > SomeARClass.where(id: 1).pluck(:created_at).first.class     (0.4ms)  SELECT "some_ar_classes"."created_at" FROM "some_ar_classes" WHERE "some_ar_classes"."id" = $1  [["id", 1]]   => ActiveSupport::TimeWithZone  

The same happens with Enums(int in database, but :symbols in Ruby)

And all this parsing/mapping operations also takes time. It's not a lot, but still. So if you asking for the most fast way to fetch data - its definitely raw sql query with connection.select_all, but there are very small situations when you can get some significant performance increase on that.

So my recommendation is using pluck.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »