Complex rails find ordering

Question

Hi,

I am trying to do a find which orders results by their house name and then by the customer's last name.

Customer.find(:all, 
    :conditions =>['customers.id IN (?)', intersection], 
    :joins => 'JOIN histories ON histories.customer_id = customers.id
     JOIN houses ON histories.house_id = houses.id',
    :order => "houses.name ASC, customers.last_name ASC",
    :select => "customers.*, histories.job_title, houses.name"
)

My problem is this will return every history related to each customer.

if I add AND histories.finish_date IS NULL This will prevent every history for the selected customer being returned but it will also stop customers in the intersection who have no history or a finish_date set from being returned.

Basically I need every customer in the intersection returned once with there current house name(if they have one) and then ordered by their house name and then their last name.

So is there a way of doing this?

Here is an example

customer

id  last_name
1     franks
2     doors
3     greens

histories

id    finish_date      house_id     customer_id
1        NULL             1             1
2        NULL             2             2
3        11/03/10         2             1
4        22/04/09         1             2

NULL = current house

houses

id name
1    a
2    b

Results

intersection = 1,2,3

last_name       house     
franks            a
doors             b
greens            NULL

Thanks

Answer 1

IMO it's simpler to do the sorting logic in Rails instead of the database:

customers = Customer.find(:all, :conditions => { :id  => intersection }, :include => [ { :histories => :houses } ])

customers.sort_by { |c| c.last_name }
customers.sort_by do |c|
  current_house = c.histories.find_by_finish_date(nil) # Returns nil if no matching record found
  if current_house
    current_house.name
  else
    ''
  end
end

Explanations

• :conditions can take an hash { :column_name => array } which translates into your IN where-condition
• :include pre-loads (eager loading) the tables if the corresponding associations exist. To put it another way: :joins creates INNER JOINs, while :include creates LEFT JOINs. Here we will left join histories and again left join houses. You could omit this :include tag, in which case rails does a new query each time you access a histories or houses property.
• sort_by allows to define a custom sort criteria.
• find_by_finish_date is one of rails' magic methods; it is equivalent to h.find(:conditions => {:finish_date => nil })
• How to output: Just output all of them in your view. If he does not have histories, customer.histories is an empty array.

Answer 2

I think you need to use outer joins.

For example, this should work:

Customer.find(:all, 
  :conditions =>['customers.id IN (?) and histories.finish_date is null', intersection], 
  :joins => 'LEFT OUTER JOIN histories ON histories.customer_id = customers.id
    LEFT OUTER JOIN houses ON histories.house_id = houses.id',
  :order => "houses.name ASC, customers.last_name ASC",
  :select => "customers.*, histories.job_title, houses.name"
)

If you've got an association between Customer and History and between History and House you should be able to do :include => [:histories => :house] instead of the :joins option.

The only other thing is that the customers with no house will appear first in the list due to NULL being earlier in the order than a non-NULL value. You might want to try an order option like this :

:order => 'isnull(houses.name), houses.name, customers.last_name'

to achieve what you specified.