CODEWITHSUNDEEP
rubyruby-on-rails-3
Gareth Burrows
Gareth Burrows

Reputation: 1182

most efficient way to query across tables / models in rails

I have 2 models, being User and Absence, in a Rail 3.2 ruby 2.1.5 app

class User
  has_many :absences
end

class Absence
  belongs_to :user
end

on the Absence there is a :start_date and an :end_date

I need to figure out the following in the most efficient way

"how many users are absent today?"

So this will be a count of the distinct user ids from a selection of all the absences where the start date is today or before today and the end date is today or after today.

I thought about doing something like 

user_ids =  Absence.where("absences.start_date <= ? and absences.end_date >= ?", Date.today, Date.today).map(&:user_id)
user_ids.uniq!
user_ids.count

That "think" that gets me the count of the IDs I need, but is the most efficient way to do this query?

Upvotes: 0

Views: 40

Answers (2)

Thorin
Thorin

Reputation: 2034

I think you should use:

Absence.where("absences.start_date <= ? and absences.end_date >= ?", Date.today, Date.today).group("user_id").length

Upvotes: 0

Surya
Surya

Reputation: 15992

Try this: 

absent_users = User.includes(:absences).where("absences.start_date <= ? and absences.end_date >= ?", Date.today, Date.today)

or to be more concise:

current_date = Date.today
absent_users = User.includes(:absences).where("absences.start_date <= ? and absences.end_date >= ?", current_date, current_date)

It should get you distinct results of user, if you still don't get distinct results then add distinct in the active record chain.

Are you after just the unique user_id records? then you can just add select in your query for user_id column:

absent_user_ids = Absence.select(:user_id).where("absences.start_date <= ? and absences.end_date >= ?", Date.today, Date.today).distinct

Upvotes: 1

Related Questions