How to use rails to find this result across 3 tables ?

Question

A project has 3 tables : client, invoices, deliveries

The relations are:

             client has_many invoices
             client has_many deliveries


              invoices belongs_to client
              deliveries belongs_to client

Both invoices and deliveries model have a date field.

Now I want to find all clients who has at least 1 record either in invoices or deliveries within that month, so that need the distinct clients who have records in invoices or have records in deliveries or even both.

How to find the clients using Rails or even SQL ?

Thanks for help

Answer 1

I would define a method on the client that checks if date is in month x for deliveries and invoices

def has_invoice_in_month(year, month)
   start_date = Date.new(year, month, 1)
   end_date = Date.new(year, month, -1)
   invoices.each do |invoice|
     return true if start_date < invoice.date and end_date > invoice.date
   end
end

Now you iterate over the clients and get them all together

Client.find(:all).collect { |c| c if c.has_invoice_in_month(y,x) or
 c.has_delivery_in_month(y,x))}.uniq

The uniq call removes all the nil objects that get returned as well

If only a few clients out of many ahve invoices or deliveries you could try if it is faster to get Client.find(:all, :join => :invoices) + Client.find(:all, :join => :deliveries)

Answer 2

This would be my first crack at it:

Client.all(:include => [:deliveries, :invoices], :conditions => ['(deliveries.date > :start_date) OR (invoices.date > :start_date)', :start_date => 1.month.ago])

Ideally I would want to use named_scope or custom finder method in the model to handle this:

class Client < ActiveRecord::Base
   named_scope :active_since, lambda {|date| {:include => [:deliveries, :invoices], :conditions => ['(deliveries.date > :start_date) OR (invoices.date > :start_date)', :start_date => date]}}

  # or
  def self.find_by_delivery_or_invoice_activity
    all(:include => [:deliveries, :invoices], :conditions => ['(deliveries.date > :start_date) OR (invoices.date > :start_date)', :start_date => 1.month.ago])
  end
end

I am pretty sure this should be pretty close to what you need, but I wouldn't expect it to work without some modifications for your situation.