CODEWITHSUNDEEP
sqlruby-on-railsruby-on-rails-3postgresqlruby-on-rails-3.1
JavierQQ23
JavierQQ23

Reputation: 714

Rails - How to avoid multiple querys on this

I have this set of tables (I'm using postgresql)

User (with id, first_name, ....)
Assignment (with id, name, start_date, finish_date,....)
AssignmentUser(with assignment_id, user_id, flag_status)

The flag_status is a boolean that says if a user is still or not in an assignment.

Let's say user1 applies for assignment1, assignment2, assignment3 as follows:

                        start_date     finish_date   flag_status
user1  assignment1      11-11-11       11-12-11      false
user1  assignment2      01-10-11       01-02-12      true
user1  assignment3      01-01-12       01-03-12      true

Let's say I want to search TODAY the closest start_date of an user's assignment.

I've done this in my User model:

  def last_date
    self.assignments.where("date < ?", Date.today).max.try(:date)
  end

and this

def last_status
  AssignmentUser.find_by_assignment_id_and_user_id(Assignment.find_by_date(self.last_date), self.id).flag_status if self.last_date.present?
end

And in my view for each user:

User.all.each do |u|
  <td>   u.first_name   </td>
  <td>   u.last_date   </td>
  <td>   u.last_status  </td>
end

It works well but, in my log I see 3 queries for each user (as expected). So my question is: how can I avoid these multiple queries? (I guess it's more like a SQL question than a Rails one)

Upvotes: 1

Views: 231

Answers (1)

TomDunning
TomDunning

Reputation: 4877

@au = AssignmentUsers.find_by_sql(
  "SELECT assignment_users.user_id, MAX(assignment_users.date) as last_date,
   assignment_id, 
   assignments.flag_status,
   users.first_name
  FROM assignment_users
  LEFT JOIN assignments
   ON assignments.id = assignment_id
  LEFT JOIN users
   ON users.id = user_id
  WHERE date < CURDATE()
  GROUP BY user_id;"
 )

Then in your view:

@au.each do |u|
 <tr>
   <td>   u.first_name   </td>
   <td>   u.last_date    </td>
   <td>   u.last_status  </td>
 </tr>
end

PLEASE NOTE: this is iterating over AssignmentUsers, so if there are any users who are without an assignment they will be missed. IF that isn't good enough (i.e. you don't want to pull back a 2nd list - easily done by User.all(:conditions => "id NOT IN (#{@au.collect(&:user_id}) - then this solution isn't suitable and if that's the case, then i think you're already probably doing it the best way, although i would have changed your methods to scopes - if you want me to show you how that would look let me know.

Upvotes: 1

Related Questions