query.rb

# Originally written to detect which users have expired cache for specific API method.
# CCP required API users to respect the cache, which means we'd have to record the expiration date locally, and only request new data once the timer expired. This takes care of it.


# --->cut<---
# COALESCE to force NULLs to a recognisable string.
# when 'method' - if it's this method impose conditions
# if it's '(none)' (aka NULL) - this user has no EveApiCache entries, so select them
# else this user has a non-#{method} EveApiCache so select them
def users_to_work_on(method)
 User.find(:all,
           :select => "users.id, COALESCE(eac.method_name, '(none)'), MAX(eac.expires_at), users.api_key, users.api_userid",
           :joins => "LEFT OUTER JOIN eve_api_caches eac ON users.id = eac.user_id",
           :conditions => "(users.api_key IS NOT NULL AND users.api_userid IS NOT NULL)",
           :order => "RANDOM()",
           :group => "users.id, eac.method_name, users.api_key, users.api_userid HAVING ( CASE COALESCE(eac.method_name, '(none)') WHEN '#{method}' THEN MAX(eac.expires_at) IS NULL OR MAX(eac.expires_at) < '    #{Time.now.utc.to_s}' WHEN '(none)' THEN true ELSE true END )"
          ).uniq
end