如何将目标SQL转为ActiveRecord scope？修复日期筛选异常

如何将以下SQL转换为ActiveRecord scope？

SELECT * FROM trxes 
WHERE date="2022-05-01" 
AND id IN (SELECT lines.trx_id FROM lines WHERE lines.category_id = 15) 
OR id IN (SELECT id FROM trxes WHERE trxes.category_id = 15)

当前使用的scope如下，但会返回2022年5月以外的Trx记录：

scope :category_id, -> (cat_id) { where(lines: Line.where(category_id: cat_id)).or(Trx.where(category_id: cat_id)) }

执行Trx.date("2022-May-01").category_id(15)后生成的SQL显示，日期条件仅作用于OR的前半部分，导致后半部分Trx不受日期限制。

补充信息：简单Trx拥有Fuel、Groceries等分类，无Line对象；复杂Trx分类为Split，包含多个带分类的Line对象。需求是查询分类为Fuel的Trx，即自身分类为Fuel的简单Trx，以及包含分类为Fuel的Line的复杂Trx。

模型关联：

class Trx < ApplicationRecord
  belongs_to :category, optional: true
  has_many :lines, dependent: :destroy
...
class Line < ApplicationRecord
  belongs_to :trx
  belongs_to :category, optional: true
...
class Category < ApplicationRecord
  has_many :lines
  has_many :trxes
...

期望合并以下两个ActiveRecord查询的结果：

Trx.where(date:"2022-May-01").where(category_id: 15)
Trx.where(date:"2022-May-01").where(lines: Line.where(category_id: 15))

问题出在当前scope的逻辑上——or方法直接拼接前后两个条件，导致日期条件仅绑定到where(lines: ...)分支，而Trx.where(category_id: cat_id)不受外层日期约束。要让日期条件作用于整个OR逻辑的两边，可采用以下几种方法：

方法1：正确嵌套OR条件 #

修改scope，用self.where代替直接调用Trx.where，让OR的两个分支继承当前查询上下文（包括日期条件）：

scope :category_id, -> (cat_id) {
  where(lines: Line.where(category_id: cat_id))
    .or(self.where(category_id: cat_id))
}

调用Trx.date("2022-May-01").category_id(15)时，日期条件会自动应用到OR的两边，生成的SQL如下：

SELECT "trxes".* FROM "trxes" 
WHERE "trxes"."date" = '2022-05-01' 
AND (
  EXISTS (SELECT 1 FROM "lines" WHERE "lines"."trx_id" = "trxes"."id" AND "lines"."category_id" = 15) 
  OR "trxes"."category_id" = 15
)

方法2：用子查询构建IN条件 #

贴近原始SQL的写法，用子查询生成IN条件，同时保证日期条件作用于整个查询：

scope :category_id, -> (cat_id) {
  where(
    id: Line.where(category_id: cat_id).select(:trx_id)
  ).or(
    where(category_id: cat_id)
  )
}

方法3：使用UNION合并查询（Rails 6+支持） #

直接合并你期望的两个查询结果，自动带上外层日期条件：

scope :category_id, -> (cat_id) {
  query1 = where(category_id: cat_id)
  query2 = where(lines: Line.where(category_id: cat_id))
  query1.union(query2)
}

效果验证 #

无论采用哪种方法，调用Trx.date("2022-May-01").category_id(15)后，都会同时筛选出两类符合条件的Trx：

• 自身分类为15的简单Trx
• 包含分类为15的Line的复杂Trx

内容的提问来源于stack exchange，提问作者chug