Want to order with Count, Group by and Order DESC in Rails

Question

This is my doubt, I have the following table:

  create_table "followings", force: :cascade do |t|
    t.integer "follower_id"
    t.integer "followed_id"
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
    t.index ["followed_id"], name: "index_followings_on_followed_id"
    t.index ["follower_id", "followed_id"], name: "index_followings_on_follower_id_and_followed_id", unique: true
    t.index ["follower_id"], name: "index_followings_on_follower_id"
  end

And this is my user table:

  create_table "users", force: :cascade do |t|
    t.string "email", default: "", null: false
    t.string "encrypted_password", default: "", null: false
    t.string "reset_password_token"
    t.datetime "reset_password_sent_at"
    t.datetime "remember_created_at"
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
    t.string "photo"
    t.string "coverimage"
    t.string "fullname"
    t.string "username"
    t.index ["email"], name: "index_users_on_email", unique: true
    t.index ["reset_password_token"], name: "index_users_on_reset_password_token", unique: true
  end

and I need to create a top 10 of the most followed users, so in this case, the most repetitive followed_id's, but I can not make an order by on a count just created column inside ActiveRecord. I am trying something like this:

@userst = Following.where(:group  => "followed_id", :select => "device_id, COUNT(folloing_id)", sort: :dsc)

what I can be doing wrong?

this is my user controller (top is not finished yet as you can see):

class UsersController < ApplicationController
  before_action :authenticate_user!

  def index
    @users = User.all
  end

  def show
    @user = User.find(params[:id])
    @posts = @user.posts.ordered_by_most_recent
  end

  def edit
    @user = User.find(params[:id])
  end

  def following
    @title = "Following"
    @user  = User.find(params[:id])
    @users = @user.following.paginate(page: params[:page])
    render 'show_follow'
  end

  def followers
    @title = "Followers"
    @user  = User.find(params[:id])
    @users = @user.followers.paginate(page: params[:page])
    render 'show_follow'
  end

  def top
    @userst = User.where()
  end
end

Just to let you know this is the user model:

class User < ApplicationRecord
  # Include default devise modules. Others available are:
  # :confirmable, :lockable, :timeoutable, :trackable and :omniauthable
  devise :database_authenticatable, :registerable,
         :recoverable, :rememberable, :validatable, :authentication_keys => [:username]

  validates :fullname, presence: true, length: { maximum: 20 }
  validates :username, presence: true, length: { maximum: 20 }

  validates_uniqueness_of :username

  has_many :posts
  has_many :active_followings, class_name:  "Following",
  foreign_key: "follower_id",
  dependent:   :destroy
  has_many :passive_followings, class_name:  "Following",
  foreign_key: "followed_id",
  dependent:   :destroy
  has_many :following, through: :active_followings, source: :followed
  has_many :followers, through: :passive_followings, source: :follower

  mount_uploader :photo, FileUploader
  mount_uploader :coverimage, FileUploader

    # Follows a user.
  def follow(other_user)
    following << other_user
  end

  # Unfollows a user.
  def unfollow(other_user)
    following.delete(other_user)
  end

  # Returns true if the current user is following the other user.
  def following?(other_user)
    following.include?(other_user)
  end
end

Answer 1

Assuming you have a user model, and it contains a has_many relationship with the followings model, you can try with:

User.joins(:followings)
    .order('COUNT(followings.followed_id) DESC')
    .group('users.id')
    .limit(10)