How to sort ActiveRecord results by the number of matched models?

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. the message is similar to the answers.

I want to create a new cafe array and sort it by cafe.posts.count. Finally, I will show you two of the best cafes for which there are many answers.

I tried some sorting codes, for example

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

but it didn’t work. The error says that the cafe has no connection with the mail. how can i sort a ruby ​​array using a matched number of models?

I am not good at English, I will be grateful for your answers!

Answer 1

Hey in mysqlyou can directly use both

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

Top 2 limit

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

Answer 2

posts post, :

has_many :posts, dependent: :destroy

SQL. MySQL dsc, desc. , :

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

Answer 3

you can use counter_cache

class Post < ActiveRecord::Base
   belongs_to :cafe, counter_cache: true
   belongs_to :user
end

Then add the carry:

add_column :cafe, :posts_count, :integer, default: 0

then you can ask how

@cafe_by_posts = Cafe.order('posts_count DESC')