mysql - Rails 中的嵌套连接

Question

我的 Rails 应用程序中有 3 个模型 Company、User、Loan

公司.rb

class Company < ApplicationRecord
  has_many :users
end

---

Company id: 1, name: "A", created_at: "2018-02-06 07:34:17", updated_at: "2018-02-06 07:34:17"
Company id: 2, name: "B", created_at: "2018-02-06 07:34:19", updated_at: "2018-02-06 07:34:19"
Company id: 3, name: "C", created_at: "2018-02-06 07:34:21", updated_at: "2018-02-06 07:34:21"

用户.rb

class User < ApplicationRecord
  belongs_to :company
  has_many :loans
end

---

User id: 1, name: "Sachin", company_id: 1, created_at: "2018-02-06 07:35:53", updated_at: "2018-02-06 08:02:41", user_status: true
User id: 2, name: "Ghanshyam", company_id: 1, created_at: "2018-02-06 07:36:01", updated_at: "2018-02-06 08:02:41", user_status: false
User id: 3, name: "Anand", company_id: 1, created_at: "2018-02-06 07:36:06", updated_at: "2018-02-06 08:02:41", user_status: false
User id: 4, name: "Ghanshyam Rahul", company_id: 1, created_at: "2018-02-06 07:36:15", updated_at: "2018-02-06 08:02:41", user_status: false
User id: 5, name: "Anand", company_id: 3, created_at: "2018-02-06 07:43:56", updated_at: "2018-02-06 08:02:41", user_status: false
User id: 6, name: "Ghanshyam", company_id: 3, created_at: "2018-02-06 07:43:58", updated_at: "2018-02-06 08:02:41", user_status: false

贷款.rb

class Loan < ApplicationRecord
  belongs_to :user
end

---

Loan id: 1, name: "loan 1", user_id: 1, created_at: "2018-02-06 07:44:46", updated_at: "2018-02-06 07:44:46"
Loan id: 5, name: "loan 5", user_id: 1, created_at: "2018-02-06 07:44:54", updated_at: "2018-02-06 07:44:54"
Loan id: 6, name: "loan 6", user_id: 6, created_at: "2018-02-06 07:45:33", updated_at: "2018-02-06 07:45:33"
Loan id: 7, name: "loan 7", user_id: 6, created_at: "2018-02-06 07:45:34", updated_at: "2018-02-06 07:45:34"
Loan id: 8, name: "loan 8", user_id: 6, created_at: "2018-02-06 07:45:36", updated_at: "2018-02-06 07:45:36"

我想写下连接查询(单个查询)，如果用户有任何贷款，则获取公司列表，如下所示:

Company id: 1, name: "A", created_at: "2018-02-06 07:34:17", updated_at: "2018-02-06 07:34:17"
Company id: 3, name: "C", created_at: "2018-02-06 07:34:21", updated_at: "2018-02-06 07:34:21"

Answer 1

Company.joins(users: [:loans]).group('companies.id')

为了避免可能出现的公司重复，我会按公司 ID 字段对公司表进行分组。