Stephen Horvath Stephen Horvath - 2 years ago 72
SQL Question

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

Users and Sessions are joined by a


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

  • == true

  • user.available == true

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

  • session.coach_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

  • Session.in_progress

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


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

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

Answer Source

I would do it with SQL exists:

User.where(coach: true, available: true).
  where("not exists (select 1 from sessions " +
    "where sessions.coach_id = and sessions.call_ends_at is null)")

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download