How to sort ActiveRecord results by associated model count?

Question

class Cafe < ActiveRecord::Base

  belongs_to :user

  has_many :posts, dependent: :destroy
  has_many :tags, dependent: :destroy
end

This is cafe.rb model

class Post < ActiveRecord::Base
  belongs_to :cafe
  belongs_to :user
end

and this is postq.rb. post is like replies.

I want to create new cafe array and sort it by cafe.posts.count. Finally, I will show top two cafes that have many replies.

I've tried some sorting codes like,

@cafe_by_posts = Cafe.joins(:post).order('posts.size dsc')

but it didn't work. The error said that cafe has no association with post. how can I sort ruby array by associated model count?

I'm not good at english, I'll appreciate your answers!

Answer 1

Hey in mysql you can directly used like

@cafe_by_posts = Cafe.joins(:posts).group("posts.cafe_id").order('count(posts.cafe_id) desc')

You can directly select Top 2 using limit

 @cafe_by_posts = Cafe.joins(:posts).group("posts.cafe_id").order('count(posts.cafe_id) desc').limit(2)

Answer 2

Relation name is posts not post, look:

has_many :posts, dependent: :destroy

Ordering should be done by SQL constraint. There is no dsc sort order in MySQL, it is desc. So, here you go:

 @cafe_by_posts = Cafe.joins(:posts)
                      .group('posts.cafe_id')
                      .order('COUNT(posts.cafe_id) DESC')
                      .limit(2) # to retrieve only 2 topmost records