Stephen Horvath Stephen Horvath - 7 months ago 15
SQL Question

How do I write an ActiveRecord query that filters out results from a joined table?

Users and Sessions are joined by a

has_and_belongs_to_many
association.

How do I get the unique list of Users that meet the following conditions?


  • user.coach == true

  • user.available == true



And then NOT include a user if that user is a coach in any active Session:


  • session.coach_id == user.id

  • session.call_ends_at == nil



Is there a way I can write this with ActiveRecord Query language? Do I need to write a pure SQL statement? Some kind of hybrid? What would YOU do?

I also have scopes defined that could be helpful here. But I'm not sure how to add them in:


  • User.available_coaches
    (scope)

  • Session.in_progress
    (scope)



User model

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) }


Session model

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) }


Schema

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

Answer

I would do it with 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)")

Note that since there is no join to sessions there is no need for .uniq.

Comments