ruby-on-rails – 连接ActiveRecord ::通过多态关联查询的关系

我的应用程序有一个Job模型.系统中的每个作业都有联系人.如果您需要询问有关工作的问题,这就像您打电话的人.联系人可以是客户端或客户的员工(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

客户有inved_jobs的想法.客户委托的工作是那些客户是联系人的工作,或者客户的一个员工是联系人.

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

旁白：该方法有点像黑客,因为它返回一个数组而不是一个ActiveRecord :: Relation.有趣的是,如果我尝试将作业连接到employee_jobs,它就会爆炸.它可能会或可能不会为我的目的.

我想向客户端添加一个名为with_commissioned_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

我该如何实现这种方法？

我正在使用Rails 3.2.9.

更新：

我已经取得了一些进展,现在我有两种方法,每种方法都有我需要的一半.

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

现在我需要做的就是将这两个方法调用合并到一个ActiveRecord :: Relation中.我显然可以这样做：

def self.with_commissioned_jobs
    with_jobs + with_employee_jobs
  end

问题是返回一个数组而不是一个Relation实例,我不能在其上链接更多的范围.

更新2：

使用合并似乎也不起作用.这是AR查询和生成的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'

顺便说一句,这是我试图通过的测试.第一个测试失败,因为当我使用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

解决方法

你考虑过宝石 meta_where吗？主要的似乎是你想要返回一个ActiveRecord：Relation对象以进一步链接.

更新2：使用别名使用LEFT OUTER JOIN作业两次

# 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

看它是否有效：

#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

ruby-on-rails – 连接ActiveRecord ::通过多态关联查询的关系

解决方法

猜你在找的Ruby相关文章