SQL: how to find a complement to a set with a derived function/value

Question

This one has me stumped, so I'm hoping someone who's smarter than me can help me out.

I'm working on a rails project in which I've got a User model which has an association of clock_periods joined to it, having the following partial definition:

User
  has_many :clock_periods
    #clock_periods has the following properties:
    #clock_in_time:datetime
    #clock_out_time:datetime
  named_scope :clocked_in, :select => "users.*",
      :joins => :clock_periods, :conditions => 'clock_periods.clock_out_time IS NULL'
  def clocked_in?
    #default scope on clock periods sorts by date
    clock_periods.last.clock_out_time.nil?
  end

The SQL query to retrieve all clocked in users is trivial:

SELECT users.* FROM users INNER JOIN clock_periods ON clock_periods.user_id = users.id 
  WHERE clock_periods.clock_out_time IS NULL

The converse however--finding all users who are currently clocked out--is deceptively difficult. I ended up using the following named scope definition, though its hackish:

named_scope :clocked_out, lambda{{
  :conditions => ["users.id not in (?)", clocked_in.map(&:id)+ [-1]]
}}

What bothers me about it is that it seems like there ought to be a way to do this in SQL without resorting to generating statements like

SELECT users.* FROM users WHERE users.id NOT IN (1,3,5)

Anybody got a better way, or is this really the only way to handle it?

Answer 1

assuming getdate() = the function in your SQL implementation that returns a datetime representing right now.

SELECT users.* FROM users INNER JOIN clock_periods ON clock_periods.user_id = users.id 
  WHERE getdate() > clock_periods.clock_out_time and getdate() < clock_periods.clock_in_time

Answer 2

In rails, Eric H's answer should look something like:

users = ClockPeriod.find(:all, :select => 'users.*', :include => :user,
  :conditions => ['? > clock_periods.clock_out_time AND ? < clock_periods.clock_in_time',
  Time.now, Time.now])

At least, I think that would work...

Answer 3

Besides @Eric's suggestion there's the issue (unless you've guaranteed against it in some other way you're not showing us) that a user might not have any clock period -- then the inner join would fail to include that user and he wouldn't show either as clocked in or as clocked out. Assuming you also want to show those users as clocked out, the SQL should be something like:

SELECT users.*
  FROM users 
  LEFT JOIN clock_periods ON clock_periods.user_id = users.id 
  WHERE (clock_periods.clock_user_id IS NULL) OR
        (getdate() BETWEEN clock_periods.clock_out_time AND
                           clock_periods.clock_in_time)

(this kind of thing is the main use of outer joins such as LEFT JOIN).