可能具有多个连接的 Rails 高级查询

您想要做的是将所有捐赠加入其类别和与该类别关联的作物，然后过滤与给定字符串匹配的类别名称或作物名称。让我们从在 SQL 中执行此操作开始：

SELECT d.*
FROM donations d
JOIN categories ca       ON ca.id = d.category_id
JOIN categories_crops cc ON cc.category_id = ca.id
JOIN crops cr            ON cr.id = cc.crop_id
WHERE
  ca.name LIKE 'foo%'
OR
  cr.name LIKE 'foo%'

So suppose your data set looks like the following:

    donations           categories           crops        categories_crops 
----------------   ------------------   ------------   ---------------------
id | category_id   id | name            id | name      crop_id | category_id
================   ==================   ============   =====================
1  | 1             1  | leafy greens    1 | lettuce    1       | 1
2  | 1             2  | dark greens     2 | broccoli   2       | 2
3  | 2                                  3 | kale       3       | 1
                                                       3       | 2

The JOINs above will produce a pre-filtered set that looks something like this, slimmed down for easier representation:

donations X categories X categories_crops X crops
-------------------------------------------------
donation_id | category.name | crops.name
=================================================
1           | leafy greens  | lettuce
1           | leafy greens  | kale
2           | leafy greens  | lettuce
2           | leafy greens  | kale
3           | dark greens   | broccoli
3           | dark greens   | kale

As you can see, each donation ID appears next to all the crops that were under the category it donated to (note that, at this point, I'm not sure if I've got your data model right or wrong. Either way, the principles I'm describing should help you and others). So, if we filter by leafy, we'd get back donations 1 and 2. If we filter by kale, we'd get back donations 1, 2, and 3.

Hopefully that all makes sense, and we can get to the final step -- translating the SQL to ActiveRecord. It's pretty direct:

Donations
  .joins(category: :crops)
  .where('categories.name LIKE :term OR crops.name LIKE :term', term: "%#{search_term}%")

The join may need to be category: { categories_crops: :crops }), or something else, depending on exactly how your relations are set up. The idea is to traverse the relations you've defined to build the join like described above.

---

EDIT: Per OP's clarification in comments, the data model also contains an implicit hierarchy of categories, inferred from the crop -> category relationship but substituting the crop name for the category of the same name. Implicit relationships, where additional logic is needed but not made obvious from the database design, can be tough to query -- often a sign that the data model might need updating. But let's start by updating the query.

我们还需要包括“属于”正在搜索的类别的类别，因此我们需要一个额外的连接。由于并非每个类别都有父类别，因此它应该是外连接。然而，加入标准会很奇怪。我的第一个想法是创建一个临时表，将类别与其父类别名称连接起来，然后将其加入。所以查询变成：

SELECT d.*
FROM donations d
JOIN categories ca       ON ca.id = d.category_id
JOIN categories_crops cc ON cc.category_id = ca.id
JOIN crops cr            ON cr.id = cc.crop_id
LEFT JOIN (
  SELECT ca2.name as parent_category_name, ca3.id as child_category_id
  FROM categories_crops cc2
  JOIN categories ca2 ON ca2.id = cc2.category_id
  JOIN crops cr2 ON cr2.id = cc2.crop_id
  JOIN categories ca3 ON ca3.name = cr2.name
) cp
ON cp.child_category_id = ca.id

WHERE
  ca.name LIKE 'foo%'
OR
  cr.name LIKE 'foo%'
OR
  cp.parent_category_name LIKE 'foo%'

翻译成 Rails：

Donations
  .joins(category: :crops)
  .joins(<<-SQL)
    LEFT JOIN (
      SELECT ca2.name as parent_category_name, ca3.category_id as child_category_id
      FROM categories_crops cc2
      JOIN categories ca2 ON ca2.id = cc2.category_id
      JOIN crops cr2 ON cr2.id = cc2.crop_id
      JOIN categories ca3 ON ca3.name = cr2.name
    ) cp ON cp.child_category_id = categories.id
  SQL
  .where('categories.name LIKE :term OR crops.name LIKE :term OR cp.parent_category_name LIKE :term', term: "%#{search_term}%")