sql - 想要在 Rails 中使用 Count、Group by 和 Order DESC 进行排序
问题描述
这是我的疑问,我有下表:
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
这是我的用户表:
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
我需要创建前 10 个最受关注的用户,所以在这种情况下,是最重复的 follow_id,但我无法根据 ActiveRecord 中刚刚创建的列的计数进行排序。我正在尝试这样的事情:
@userst = Following.where(:group => "followed_id", :select => "device_id, COUNT(folloing_id)", sort: :dsc)
我做错了什么?
这是我的用户控制器(如您所见,顶部尚未完成):
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
只是为了让您知道这是用户模型:
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
解决方案
假设您有一个用户模型,并且它包含与以下模型的 has_many 关系,您可以尝试:
User.joins(:followings)
.order('COUNT(followings.followed_id) DESC')
.group('users.id')
.limit(10)
推荐阅读
- python - WindowsContext: OleInitialize() failed: "COM error 0x80010106 RPC_E_CHANGED_MODE (Unknown error 0x0ffffffff80010106)"
- arrays - Swift:追加到字典中返回随机
- svn - 如何仅签出给定父目录的特定子目录未签出
- javascript - 发布空正文(长度为 0 的字符串)
- ionic-framework - ERROR 错误:尝试比较“[object Object]”时出错。ionic 中只允许使用数组和可迭代对象
- visual-studio-code - VS代码`\`下划线不够长
- r - 重复的预测给出相同的值
- javascript - 如何在单击angularjs中的按钮时将文本框值添加到数组并添加所需的功能
- javascript - 以表格格式排列 JSON 字符串
- javascript - f.state 不是功能 wordpress