用户和会话通过has_and_belongs_to_many
关联加入。
如何获得满足以下条件的唯一用户列表?
user.coach == true
user.available == true
如果用户是任何活动会话中的教练,则不包括该用户:
session.coach_id == user.id
session.call_ends_at == nil
有没有一种方法可以用ActiveRecord查询语言写这篇文章?我需要写一个纯SQL语句吗?某种混合动力?你会怎么做?
我还定义了可能对这里有所帮助的范围。但我不知道如何添加它们:
User.available_coaches
(范围)Session.in_progress
(范围)
用户模型
class User < ActiveRecord::Base
has_many :client_sessions, class_name: 'Session', foreign_key: :client_id
has_many :coach_sessions, class_name: 'Session', foreign_key: :coach_id
scope :coaches, -> { where(coach: true) }
scope :available_coaches, -> { coaches.where(available: true) }
会话模型
class Session < ActiveRecord::Base
belongs_to :client, class_name: 'User'
belongs_to :coach, class_name: 'User'
scope :in_progress, -> { where.not(coach: nil).where(call_ends_at: nil) }
架构
create_table "sessions", force: :cascade do |t|
t.integer "client_id"
t.integer "coach_id"
t.boolean "canceled", default: false
t.datetime "coach_accepted_at"
t.datetime "call_begins_at"
t.datetime "call_ends_at"
t.datetime "created_at", null: false
t.datetime "updated_at", null: false
end
add_index "sessions", ["client_id"], name: "index_sessions_on_client_id", using: :btree
add_index "sessions", ["coach_id"], name: "index_sessions_on_coach_id", using: :btree
create_table "users", force: :cascade do |t|
t.string "first_name"
t.string "last_name"
t.boolean "coach", default: false
t.boolean "available", default: false
t.datetime "created_at", null: false
t.datetime "updated_at", null: false
end
我会使用SQL exists
:
User.where(coach: true, available: true).
where("not exists (select 1 from sessions " +
"where sessions.coach_id = users.id and sessions.call_ends_at is null)")
注意,由于不存在到sessions
的联接,因此不需要.uniq
。