CODEWITHSUNDEEP
ruby-on-railspostgresql
Avdept
Avdept

Reputation: 2289

Ordering by count of items associated table

I have model Gif, which has has_many gif_statistics. GifStatistic model has field status with possible options: like or dislike. I have query like Gif.joins(:gif_statistics).where(...). And i want to order this query depending on count of either likedgif_statisticsordisliked`, for ex.

pseudocode

Gif.joins(:gif_statistics).where(...).order("gifs.gifs_statistics.where(state: "liked").count)")

I tried to apply sort method, but that didn't work. What else could i try to achieve this? Also i only have order by count after where clause

Upvotes: 1

Views: 55

Answers (2)

Patrice Gagnon
Patrice Gagnon

Reputation: 1464

Please let me try an answer. I would certainly do this in straight SQL. You can always do your agregation in Ruby, with arrays and maps but that'll yield poor performance. Here is your SQL query (please correct any error as I don't have your schema, hence I can't test it).

select g.id, s.status, count(s.status) cnt
from gifs g
inner join gif_statistics s
  on g.id = s.gif_id
group by g.id, s.status
order by cnt

Now see this document for running the SQL in ruby:

http://guides.rubyonrails.org/active_record_querying.html#finding-by-sql

Upvotes: 2

Pavel Tkackenko
Pavel Tkackenko

Reputation: 953

Try this:

Gif.joins(:gif_statistics).where(...).order('gif_statistics.status DESC')

Such query will return Gifs ordered by gif_statistics.status. Use DESC or ASC for alphabetical order of like/dislike.

Upvotes: 0

Related Questions