CODEWITHSUNDEEP
sqlruby-on-rails
sovanlandy
sovanlandy

Reputation: 1700

Group By multiple column for sql count statement , in rails environment

I have two Relations (class)

class RecommendedForType < ActiveRecord::Base
   attr_accessible :description, :name
   has_many :recommended_for_type_restaurants
end

And

class RecommendedForTypeRestaurant < ActiveRecord::Base
   attr_accessible :recommended_for_type_id, :restaurant_id, :user_id
   belongs_to :restaurant
   belongs_to :user
   belongs_to :recommended_for_type

   def self.get_count(rest_id)
     r = RecommendedForTypeRestaurant.where(restaurant_id: rest_id)
     @result =  r.includes(:recommended_for_type)
            .select("recommended_for_type_id, recommended_for_types.name")
            .group ("recommended_tor_type_id, recommended_for_types.name")
            .count
   end

end

if I call 

 RecommendedForTypeRestaurant.get_count(1) # get stat of restaurant_id: 1

I get

 {"dinner"=>1, "fast food"=>1, "lunch"=>3, "romantic dinner"=>1}

My goal is to get both id and name of RecommendTypeFor in the return result as well. currently i can only make it return either id or name. Something like this

 {{"id" => 1, "name" => "dinner", "count" => 1}, {"id" =>2, "name" => "lunch", "count" => 3} }

For sure i can do another round of sql once i get id or name to create that hash but i think that not the efficient way. Appreciate any help or suggestion :)

Upvotes: 2

Views: 3942

Answers (2)

mdwpilot
mdwpilot

Reputation: 99

You need to group separately, try the following group.

.group ("recommended_tor_type_id", "recommended_for_types.name").count

Upvotes: 9

AlexBrand
AlexBrand

Reputation: 12409

I believe that if you remove the .count at the end and change the select to:

.select("count(*), recommended_for_type_id, recommended_for_types.name")

You will get an array of models that will have the attributes you need and the count.

You should be able to test it out in the console by do something like this:

 r = RecommendedForTypeRestaurant.where(restaurant_id: rest_id)
 @result =  r.includes(:recommended_for_type)
        .select("recommended_for_type_id, recommended_for_types.name, count(*)")
        .group ("recommended_tor_type_id, recommended_for_types.name")

 @result.each do |r|
     puts r.recommended_for_type_id
     puts r.name
     puts r.count
 end

Hope it helps!

Upvotes: -1

Related Questions