Rails - need help with complex DB query

I'm building an app for assigning Shifts in a schedule, with or without an associated Employee. That is, a Shift with no Employee assigned, serves as a placeholder in the schedule.

On any given date, I want the schedule to show all Shifts on that date (consecutively ordered), and then show any remaining Employees, that are not scheduled to work on that date.

Here's the models:

class Employee < ActiveRecord::Base
  belongs_to :account
  has_many :shifts
end

class Shift < ActiveRecord::Base
  belongs_to :account
  belongs_to :employee

  default_scope order(:starts_at)
end

and my problematic, hideous controller code (slightly edited for brevity):

class SchedulesController < ApplicationController
  include CurrentDateHelper

  def day
    @shifts = @account.shifts.includes(:employee).within(current_date_range).all
    @employees = @account.employees.alphabetically.all

    @employees.reject!{|employee| @shifts.map(&:employee).include? employee}
  end
end

and the (very simplified) view:

<tbody>
  <%- @shifts.each do |shift| -%>
    <%- if shift.employee.present? -%>
      <%- employee = shift.employee -%>
      <%- employee_shifts = shifts.select{|s| s.employee == employee} -%>

      <tr>
        <th><%= employee.name %></th>

        <%- employee_shifts.each do |shift| -%>
          <td><%= shift.timestamp %></td>
        <%- end -%>
      </tr>

    <%- else -%>
      <tr>
        <th>Unassigned</th>
        <td><%= shift.timestamp %></td>
      </tr>
    <%- end -%>
  <%- end -%>

  &开发者_如何学运维lt;%- @employees.each do |shift| -%>
    <tr>
      <th><%= employee.name %></th>
      <td>Click to create a shift for <%= employee.name %></td>
    </tr>
  <%- end -%>
</tbody>

My problem is that an Employee are currently displayed multiple times in the schedule, if/when that Employee have more than one Shift on the same date. That, and the controller code is just ugly :S

I would probably need to group @shifts by Employee, and work from that...? My SQL chops are not that good, so any pointers are greatly appreciated - I'm not necessarily looking for a copy/paste solution, more like some clues for me to dig into and learn from.

Thanks for reading through this - I hope it makes sense! :)

You're probably right in that you want to group by shifts, but you probably don't want to do that with SQL. You do want to have access to that data in the view, and if you grouped by employee in the SQL, it would return only one shift per employee.

Rather you probably want to use enumerable's group_by, which in the view, would look something like:

<% @shifts.group_by(&:employee).each do |employee, shifts| %>
   <%# do stuff for each 'employee' the grouped array of his/her 'shifts' %>
<% end %>

As for the other part. You could exclude the ids of the employees returned with the shifts. Look into Arel's 'not_in' predicate to do that, e.g.

ids = @shifts.map {|s| s.employee.try(:id) }.compact
condition = Employee.arel_table[:id].not_in(ids)
@employees = @account.employees.alphabetically.where(condition)

# of if you extracted this into a scope 'exluding' on Employee, it could look like:
@employees = @account.employees.alphabetically.excluding(@shifts.map &:employee)

Or if you just want to replace the reject! business you have going on, you could simply subtract the shift-holding users from the array. e.g.:

@employees = @account.employees.alphabetically.all - @shifts.map(&:employee)

You could also do a uniq based on employee ID if you wanted to skip the sql.