Concatenating ActiveRecord::Relations queried through polymorphic association

Question

My application has a Job model. Every job in the system has a contact. This is like a person you would call up if you need to ask a question about the job. A contact can either be a client or an employee of a client (ClientEmployee).

class Job < ActiveRecord::Base
  belongs_to :contact, polymorphic: true
end

class Client < ActiveRecord::Base
  has_many :jobs, as: :contact
  has_many :employees, class_name: 'ClientEmployee'
end

class ClientEmployee < ActiveRecord::Base
  belongs_to :client
  has_many :jobs, as: :contact
end

Clients have the idea of commissioned_jobs. The clients commissioned jobs are those jobs for which the client is the contact OR one of the client's employees is the contact.

class Client < ActiveRecord::Base
  has_many :jobs, as: :contact
  has_many :employee_jobs, through: :employees, source: :jobs

  def commissioned_jobs
    jobs << employee_jobs
  end
end

Aside: That method is a bit of a hack because it returns an array rather than an ActiveRecord::Relation. It's also interesting that it blows up if I try to concat jobs into employee_jobs. It may or may not do for my purposes.

I would like to add a scope to Client called with_commissioned_jobs. This should return all the clients in the system who have jobs or who have employees who have jobs.

class Client < ActiveRecord::Base
  def self.with_commissioned_jobs
    # I can get clients with jobs using: joins(:jobs). How do 
    # I also include clients with employees who have jobs?
  end
end

How do I implement this method?

I'm using Rails 3.2.9.

Update:

I've made some progress and I now have two methods, each of which does half of what I need.

class Client < ActiveRecord::Base
  # Return all clients who have an employee with at least one job.
  def self.with_employee_jobs
    joins(employees: :jobs)
    # SQL: SELECT "clients".* FROM "clients" INNER JOIN "client_employees" ON "client_employees"."employer_id" = "clients"."id" INNER JOIN "jobs" ON "jobs"."contact_id" = "client_employees"."id" AND "jobs"."contact_type" = 'ClientEmployee'
  end

  # Return all clients who have at least one job.
  def self.with_jobs
    joins(:jobs)
    # SQL: SELECT "clients".* FROM "clients" INNER JOIN "jobs" ON "jobs"."contact_id" = "clients"."id" AND "jobs"."contact_type" = 'Client'
  end
end

Now all I need to do is combine these two method calls into one ActiveRecord::Relation. I can obviously do this:

  def self.with_commissioned_jobs
    with_jobs + with_employee_jobs
  end

The problem is that that returns an array rather than an instance of Relation and I can't chain more scopes on it.

Update 2:

Using merge doesn't appear to work either. Here is the AR query and the resulting SQL.

joins(:jobs).merge(joins(employees: :jobs))

SELECT "clients".* FROM "clients" INNER JOIN "jobs" 
  ON "jobs"."contact_id" = "clients"."id" 
  AND "jobs"."contact_type" = 'Client' 
  INNER JOIN "client_employees" 
  ON "client_employees"."employer_id" = "clients"."id" 
  INNER JOIN "jobs" "jobs_client_employees" 
  ON "jobs_client_employees"."contact_id" = "client_employees"."id" 
  AND "jobs_client_employees"."contact_type" = 'ClientEmployee'

By the way, here are the tests I'm trying to pass. The first test fails because there are zero results when I use merge.

describe "with_commissioned_jobs" do
  # A client with a job.
  let!(:client_with) { create :client }
  let!(:job) { create :job, contact: client_with }
  # A client who does not himself have a job, but who has an employee
  # with a job.
  let!(:client_with_emp) { create :client }
  let!(:employee) { create :client_employee, employer: client_with_emp }
  let!(:emp_job) { create :job, contact: employee }
  # A client with nothing. Should not show up.
  let!(:client_without) { create :client }

  it "should return clients with jobs and clients with employee jobs" do
    Client.with_commissioned_jobs.should == [client_with, client_with_emp]
  end

  it "should return a relation" do
    Client.with_commissioned_jobs.should be_instance_of(ActiveRecord::Relation)
  end
end

Answer 1

Have you considered the gem meta_where? The main thing seems to be that you want to return an ActiveRecord:Relation object for further chaining.

UPDATE 2: got it working with LEFT OUTER JOIN jobs twice with aliasing

  # scope for ::Client
  def self.with_commissioned_jobs
    self.joins("LEFT OUTER JOIN client_employees ON clients.id =client_employees.client_id").
        joins("LEFT OUTER JOIN jobs AS cjobs ON clients.id = cjobs.contact_id AND cjobs.contact_type = 'Client'").
        joins("LEFT OUTER JOIN jobs AS ejobs ON client_employees.id = ejobs.contact_id AND ejobs.contact_type = 'ClientEmployee'").
        where("cjobs.id IS NOT NULL OR ejobs.id IS NOT NULL")
  end

Seeing if it works:

    #c1 has no job
    c1 = Client.create

    #c2 has a job
    c2 = Client.create
    c2.jobs.create

    #c3 has no job, but has an employee with a job
    c3 = Client.create
    c3.employees.create
    c3.employees.first.jobs.create

    puts Client.all.inspect             #=> [#<Client id: 1>, #<Client id: 2>, #<Client id: 3>] 
    puts Client.with_commissioned_jobs  #=> [#<Client id: 2>, #<Client id: 3>]

    puts [c2,c3] == Client.with_commissioned_jobs.all    #=> true

Answer 2

Do you have an important reason to stick with polymorphism?

If a ClientEmployee always has a Client, maybe you should have Job.belongs_to :client. This makes your relation dead simple. I have found adding some redundant associations can be great performance optimizations as well, as long as it doesn't make it more difficult to keep your records coherent (i.e. Client/ClientEmployee relationships being in sync with Job.Client/Job.ClientEmployee assignments when both are present).

I really like polymorphism in rails but it can get tricky when you're trying to join across them as in this case. Even if you had separate Client and ClientEmployee id's, that would be more efficient in the db (two ints vs. int and string).

Answer 3

Try this :

 joins( :jobs, {employees: :jobs} )

It should join client's jobs as well as client employees' jobs. See the guides for a more thorough information.

Edit

In your case, you may use Relation.merge :

 joins( :jobs ).merge( joins(employees: :jobs) )