CODEWITHSUNDEEP
mysqlruby-on-rails-4
Rockwell Rice
Rockwell Rice

Reputation: 3002

Ruby on Rails - Order by count of related table records

I am trying to do a query and cannot seem to crack the syntax of this. So I need to grab surveys for the current month, most recent, limit it to 5, and then order by the count of the related table which is the responses for the survey. So if a survey has had 8 responses and another 4, the one with 8 will show up at the top.

I have a scope set to find surveys for the month, but either I need to ditch that for a more involved query, or find a way to order it by the max survey.response_count .

This is the closest I have gotten but is still not correct.

@sorted_active_surveys = Survey.joins(:survey_responses).group(:survey_id).order('COUNT(survey_responses.survey_id) DESC')

If I try in combination with the scope the created_at date becomes ambiguous and errors out.

Survey.rb

scope :for_month, ->(date) { where("MONTH(created_at) = ?", date.month).limit(5) }

This also creates the same error

    scope :for_month, ->(date) { where("MONTH(survey.created_at) = ?", date.month).limit(5) }

Any help would be GREATLY appreciated.

Upvotes: 1

Views: 46

Answers (1)

Rob Nice
Rob Nice

Reputation: 298

This doesn't keep the scope but seems to order by response count correctly and keep the dates within the current month.

Survey.where('surveys.created_at > ? AND surveys.created_at < ?', Time.now.beginning_of_month, Time.now.end_of_month).joins(:survey_responses).group(:survey_id).order('COUNT(survey_responses.survey_id) DESC')

Upvotes: 1

Related Questions