0

I have been looking up the web to find a solution but to no avail. I am working on a problem where we have two tables in a has_many association, here are the model names

 class Employee < ActiveRecord::Base     # main table stores all the employee details
       has_many :work_orders
       attributes :id, :name

 class WorkOrder < ActiveRecord::Base   # Work Orders are created by a form
       belongs_to :employee
       attributes :id, :work_details, :employee_id

The software specification was to have a form for WorkOrders to be created mentioning all the work details by a separate entity ( lets say a customer so employee_id is nil upon saving the form details ). At this point upon saving the details of the WorkOrder in the database we need to trigger an auto dispatch system which will automatically assign an employee with minimum number of work_orders giving preference in the following order below;

Employee with no associated WorkOrders (first condition, if not found then next)

Employee with minimum number of associated WorkOrders he is working (or second condition)

I am trying to couple the above conditions in one single FIND SQL using active record which will return a set with only one Employee record so that we can associate the employee.id with work_orders.employee_id of this new work_order.

I wrote an admin panel to find all the work_orders with no associated employee_id and can update the work_orders records with the next available employee by giving the employee.id manually (using the above preference) but i want to automate this to some extent.

Any suggestions or pseudo code will be helpful ?

Salman Ahmed
  • 161
  • 1
  • 1
  • 4

1 Answers1

0

select a.id, count(b.employee_id) from employees a left join work_orders b on a.id = b.employee_id group by a.id, b.employee_id order by count(a.id), a.id

This query will give you the least used employee.

simply translation is: (Add your own orders according to your requirement) Employee.joins("left join work_orders on work_orders.employee_id = employees.id").group("employees.id, work_orders.employee_id")

garfiny
  • 158
  • 9
  • Hey thanks a lot the solution was right in front of my eyes , you pointed it out very clearly it seems i was missing the group parameter when using the find method , now the query i working great – Salman Ahmed Nov 30 '12 at 22:02