CODEWITHSUNDEEP
ruby-on-railsrubyactiverecord
Ignacio Martínez
Ignacio Martínez

Reputation: 702

Rails ActiveRecord: group and order by count on association

I have a model Survey and a model Music. Survey has_and_belongs_to_many musics and Music has_and_belongs_to_many surveys.

Music has an id and a name field. The user that takes the Survey has to choose between different musics.

I'd like to get the most chosen music for a group of surveys.

I ended with something like this:

surveys.joins(:musics).group("musics.id").order("count(musics.id) desc")

(surveys being a group of surveys for a given date) But that gives me an error of ambiguous "created_at", so for the sake of testing I used all the Survey table instead of a group of them:

Survey.joins(:musics).group("musics.id").order("count(musics.id) desc")

But now the error is that "surveys.id" must appear in the GROUP BY clause or be used in an aggregate function.

What am I doing wrong and how can I get the result I'm looking for?

Upvotes: 1

Views: 2114

Answers (3)

Ignacio Martínez
Ignacio Martínez

Reputation: 702

I ended up doing a scope on music as fallows (surveys is a group of Survey for a given range of time):

scope :avrg, ->(surveys) { joins(:surveys).where("surveys_musics.survey_id in (?)", surveys).group("musics.id")
    .order("count(surveys.id) desc").limit(1).first.name }

Upvotes: 1

Vishal Jain
Vishal Jain

Reputation: 1940

You should try this for achieving result as music and their corresponding count so after that you can get your most used music by using rails hash method just verify your column name and relational table name:

result=Music.joins(:surveys).group("DATE(survey_musics.created_at),survey_musics.music_id").select("musics.name as name, count(*) as number_of_time_used")

Find max used music name by ruby hash methods

result.inject({}) { |hsh, r| hsh[r.name] = r.number_of_time_used; hsh }.max_by{|k,v| v}

otherwise you have used it as by combining:

Music.joins(:surveys).group("DATE(survey_musics.created_at),survey_musics.music_id").select("musics.name as name, count(*) as number_of_time_used").inject({}) { |hsh, r| hsh[r.name] = r.number_of_time_used; hsh }.max_by{|k,v| v}

i think this might help you.

Upvotes: 0

CoupDeMistral
CoupDeMistral

Reputation: 198

looks like you are getting the ambiguous created_at error because the query you used to create surveys didn't specify the table. Try changing it to something like:

surveys = Survey.where("surveys.created_at > '#{your_datetime_variable}' ")

then do the join

Upvotes: 0

Related Questions