CODEWITHSUNDEEP
ruby-on-railsactiverecordruby-on-rails-3.1
timpone
timpone

Reputation: 19969

Join counts across tables

I have three objects (List, ListType and GlobalList) and am trying to get a count of the items in GlobalList with a specific global_id and a specific list_type_id.

This seems to work:

select count(*) from globals_lists gl, lists l where gl.global_id=946 and gl.list_id=l.id and l.list_type_id=10;

The structure is the following: 

class ListType < ActiveRecord::Base
 has_many: lists
end

class List < ActiveRecord::Base
   has_many :global_lists
   belongs_to :list_type
end

class GlobalList < ActiveRecord::Base
  belongs_to :list
end

Not sure how to do AR call - at this but can seem to put a where on the join. The list_type_id will always be 10.

a=GlobalList.where("global_id=?",119).joins(:list)

How would I do this ActiveRecord call? Most of the stuff I found via Google involved named scopes, but it seems like this should be able to be done without scopes.

Upvotes: 0

Views: 439

Answers (1)

cristian
cristian

Reputation: 8744

First approach:

result = GlobalList
  .select("COUNT(*) AS total")
  .where("globals_lists.global_id=? AND lists.list_type_id=?",119, 10)
  .joins(:list)

Then you will account your result using result.total

Second approach is tho use count method instead of select("COUNT(*) AS total") 

GlobalList      
  .where("globals_lists.global_id=? AND lists.list_type_id=?",119, 10)
  .joins(:list)
  .count

You can find more about ActiveRecord Query Interface in Rails Guides, or directly at what you are interested in count and joins

Upvotes: 1

Related Questions