ruby-on-rails - 带有分页的Rails复杂过滤器
问题描述
我正在做一个我必须展示组的项目。
涉及以下型号
团体
class Group < ApplicationRecord
has_many :user_groups, dependent: :destroy
has_many :users, through: :user_groups
belongs_to :creator, :class_name => "User", :foreign_key => "creator_id"
belongs_to :group_status
has_many :matches, dependent: :destroy
has_many :liked, :through => :matches, :source => :macther
has_many :inverse_matches , :class_name => "Match" , :foreign_key => "matcher_id", dependent: :destroy
has_many :liked_by, :through => :inverse_matches, :source => :group
enum status: %w[active inactive]
reverse_geocoded_by :latitude, :longitude
end
用户
class User < ApplicationRecord
has_one :setting, dependent: :destroy
has_many :friendships, dependent: :destroy #, :class_name => "Friendship", :foreign_key => 'user_id'
has_many :invited, :through => :friendships, :source => :friend
has_many :inverse_friendships , :class_name => "Friendship" , :foreign_key => "friend_id", dependent: :destroy
has_many :invited_by, :through => :inverse_friendships, :source => :user
has_many :user_groups, dependent: :destroy
has_many :groups, through: :user_groups
has_many :group_creations, :foreign_key => :creator_id, :class_name => 'Group'
has_many :group_statuses
has_many :matches, dependent: :destroy
enum gender: %w[male female other]
end
用户组
class UserGroup < ApplicationRecord
belongs_to :user
belongs_to :group
enum status: %w[pending joined reported]
attr_accessor :invite_id
end
匹配
class Match < ApplicationRecord
belongs_to :group
belongs_to :matcher, :class_name => "Group"
belongs_to :user
enum status: %w[like match unlike block blocked report reported]
end
首先根据用户的喜好显示组,例如年龄,性别和组成员的数量,然后显示其余用户。
我正在做的是首先排除所有不符合用户偏好的组并显示剩余的组,而不是显示那些不符合用户偏好的组。
这就是算法的样子
1-查找与组成员计数不匹配的组
group_member_count_check = Group.joins(:users).joined_group.group('groups.id')
.having("count(groups.id) != #{setting.group_member_count}").pluck(:id)
2-查找不符合性别偏好的组
if setting.male_only
gender_check = Group.joins(:users).joined_group.where('users.gender !=?', 0).pluck(:id)
elsif setting.female_only
gender_check = Group.joins(:users).joined_group.where('users.gender !=?', 1).pluck(:id)
end
3-比删除当前用户包含被阻止用户的组
blocked_users = current_user.friendships.where(friendships: {status: 2}).pluck(:friend_id) +
current_user.inverse_friendships.where(friendships: {status: 2}).pluck(:user_id)
blocked_group_ids = Group.joins(:users).joined_group.where(users: {id: blocked_users}).pluck(:id)
4-删除已经喜欢/不喜欢的组
already_liked_or_matched_id = (group.matches.where(status: [0,1,3,4,5,6]).pluck(:matcher_id)
+ current_user.matches.where(status: [2]).pluck(:matcher_id)).uniq
现在首先返回不包括上述提取组的组
groups = Group.includes(:group_status, :users)
.where(status: 'active', spot_light_enabled: false)
.where.not(id: group_member_count_check + gender_check +blocked_group_ids + already_liked_or_matched_id )
.near([latitude, longitude], setting.max_distace, order: 'random()').page(params[:page]).per(params[:per_page])
如果没有剩余的首选组,则显示其他组
if groups.length.zero?
groups = Group.includes(:group_status, :users).where(status: 'active')
.where(id: gender_check + group_member_count_check)
.where.not(id: blocked_group_ids + already_liked_or_matched_id )
.near([latitude, longitude], 10000, order: 'random()').page(params[:page]).per(params[:per_page])
end
比响应是这样的
{
groups: groups,
pagination: pagination_dict(Group.where.not(id: blocked_group_ids ).page(params[:page]).per(params[:per_page]))
}
模式
create_table "users", id: :serial, force: :cascade do |t|
t.string "phone_number"
t.string "user_name"
t.string "authentication_token"
t.string "first_name"
t.string "last_name"
t.text "bio"
t.date "dob"
t.integer "age"
t.integer "gender"
t.integer "group_id"
t.decimal "latitude", precision: 15, scale: 6
t.decimal "longitude", precision: 15, scale: 6
t.string "city"
t.string "state"
t.boolean "creator", default: false
t.boolean "is_premium", default: false
t.datetime "created_at", null: false
t.datetime "updated_at", null: false
t.integer "instant_match_allow", default: 1
t.integer "spot_light_allow", default: 1
t.boolean "is_blocked", default: false
t.boolean "is_new", default: true
t.index ["authentication_token"], name: "index_users_on_authentication_token"
t.index ["phone_number"], name: "index_users_on_phone_number", unique: true
t.index ["user_name"], name: "index_users_on_user_name", unique: true
end
create_table "groups", force: :cascade do |t|
t.integer "group_status_id"
t.string "name"
t.integer "status", default: 0
t.decimal "latitude", precision: 15, scale: 6
t.decimal "longitude", precision: 15, scale: 6
t.string "city"
t.string "state"
t.datetime "created_at", null: false
t.datetime "updated_at", null: false
t.integer "creator_id"
t.integer "instant_match_allow", default: 1
t.integer "spot_light_allow", default: 1
t.boolean "spot_light_enabled", default: false
t.datetime "spot_light_time"
t.integer "place_id"
t.index ["group_status_id"], name: "index_groups_on_group_status_id"
end
create_table "user_groups", force: :cascade do |t|
t.integer "user_id"
t.integer "group_id"
t.boolean "creator", default: false
t.integer "status", default: 0
t.datetime "created_at", null: false
t.datetime "updated_at", null: false
end
create_table "matches", force: :cascade do |t|
t.integer "group_id"
t.integer "matcher_id"
t.integer "status"
t.integer "user_id"
t.datetime "created_at", null: false
t.datetime "updated_at", null: false
t.integer "chat_id"
end
现在问题
1-主要是查询太多(我必须进行更多检查但排除已显示的组等)
2-分页不按预期工作(跳过一些页面,因为从首选组切换到其他组)
管理这个的首选方法是什么?
解决方案
为了快速重构,让我们考虑命名范围和以下方法。
- 一、按用户偏好查找组
- 通过排除首选组来查找其他组
- 连接两组并应用分页
例如,Group 类可能具有以下范围。请注意,我无法对其进行测试,您可能仍需要重构以使其工作。
class Group < ApplicationRecord
delegate :setting, to: :user, prefix: true # for accessing setting with `user_setting` method
scope :status_active, -> { where(status: 'active') }
scope :spot_light_disalbed, -> { where(spot_light_enabled: false) }
scope :filter_blocked_groups, ->(blocked_users_id) { where.not(users: { id: blocked_users_id }) }
scope :filter_by_gender_setting, -> { where('users.gender == ?', user_setting.male_only ? 0 : 1) }
scope :filter_by_group_member_count, -> { group('groups.id').having("count(groups.id) == #{user_setting.group_member_count}") }
scope :paginate, ->(page, per_page) { page(page).per(per_page) }
scope :near_groups, ->(distance) { near([latitude, longitude], distance, order: 'random()') }
end
# Find groups by user preference
preferred_groups = Group.includes(:group_status, :users).joins(:users).joined_group
.status_active
.spot_light_disalbed
.filter_blocked_groups(blocked_users_id)
.filter_by_gender_setting
.filter_by_group_member_count
.near_groups(setting.max_distace)
# Find other groups by excluding the preferred groups
other_groups = Group.includes(:group_status, :users).joins(:users).joined_group
.where.not(id: preferred_groups.ids)
.near_groups(10_000)
# Concat the two groups and apply pagination
groups = (preferred_groups + other_groups).paginate(params[:page], params[:per_page])
我没有包括already_liked_or_matched_id
查询,因为我不清楚查询。你也可以添加它。上述方法的好处是..
- 我们只对
groups
表进行两次查询 - 可读性
希望这有所帮助。我相信还有更多的优化空间。您还可以考虑通过将所有这些查询移动到单独的类来实现查询模式。
推荐阅读
- xcode - Xcode 性能
- neo4j - Neo4j:如何满足第一次看到的发件人+第一次看到多个收件人的附件
- java - 访问包含 postfix 的服务器并使用它从 Spring Boot 应用程序发送邮件
- python - 加速 Dijkstra 算法
- javascript - 如何从 dc.js selectMenu 中删除附加提示选项“全选”?
- python - 使用列表选择具有多个条件的数据框记录
- algorithm - RDD连续子集的Scala Spark执行
- android - DataBindingUtil 将布局膨胀为空
- mysql - 如何更改 mySQL 中的存储引擎?
- reactjs - .net core 3.1 with react - 根据身份角色渲染导航菜单项