CODEWITHSUNDEEP
sqlruby-on-railsactiverecord
ZelluX
ZelluX

Reputation: 72605

Optimising an ActiveRecord count query

I have a Record table with columns named item_id and key. Item_id is a foreign key pointing to a table named Item. Active model definition looks like as follows:

class Record < ActiveRecord::Base
  belongs_to :item
end

class Item < ActiveRecord::Base
  has_many :records
end

Now I want to count how many records belongs to each item with distinct keys, the result should look like

{
    #<Item id: 1, ...> => 19,
    #<Item id: 2, ...> => 6,
    #<Item id: 3, ...> => 21,
    #<Item id: 4, ...> => 33,
}

I can do this in two lines with a Hash object counting:

records = Record.select('DISTINCT item_id, key').includes(:item)
records.each_with_object(Hash.new 0) { |e, a| a[e.item] += 1 }

I am wondering if it is possible to make it using pure active record queries or SQL statements.

Upvotes: 0

Views: 507

Answers (2)

emrahbasman
emrahbasman

Reputation: 2013

Record.count(group: "item_id")

OR

Record.select("item_id, COUNT(*) as count_all").group("item_id")

UPDATE: In your situation you can count records with distinct keys like below

Record.select("item_id, COUNT(DISTINCT key) as count_all").group("item_id")

Upvotes: 6

Jiř&#237; Posp&#237;šil
Jiř&#237; Posp&#237;šil

Reputation: 14402

You can do it either with ActiveRecord like this (you get the count by calling .record_count on each item record):

Item.
 select("items.*, count(records.id) as record_count").
 joins("left join records on records.item_id = items.id").
 group("items.id").
 all

Or you can use 4.1.2.4 :counter_cache if you don't need to specify any conditions.

Upvotes: 0

Related Questions