Laravel中带AND条件的多属性筛选查询失效问题排查

问题背景 #

正在修复Laravel应用中的筛选器Bug，该筛选器每次请求可接收1个或多个由attribute_id和value组成的筛选条件。示例筛选条件如下：

[
    [
        'attribute_id' => '33',
        'value' => 'A',
    ],
    [
        'attribute_id' => '31',
        'value' => '2024',
    ],
]

需求为仅当同时满足所有AND条件时返回结果，仅满足其一则返回空数组，但当前AND条件逻辑失效。

尝试的SQL语句（存在问题，且不想通过新增带别名的内连接实现）：

SELECT
  `contacts`.* 
FROM
  `contacts` 
INNER JOIN 
  `contact_attributes`
  ON `contact_attributes`.`contact_id` = `contacts`.`id` 
WHERE
  `contacts`.`company_id` = 7
  AND (
    `contact_attributes`.`attribute_id` = 33
    AND CAST(REPLACE(contact_attributes.value, '"', '') AS CHAR) = ('A')
  )
  AND (
    `contact_attributes`.`attribute_id` = 31
    AND CAST(REPLACE(contact_attributes.value, '"', '') as CHAR) = ('2024')
  )

问题原因 #

当前SQL逻辑错误：单条contact_attributes记录不可能同时拥有attribute_id=33和attribute_id=31，WHERE条件永远无法成立，因此返回空结果。

解决方案 #

方法1：GROUP BY + HAVING统计匹配条件数 #

通过分组统计每个联系人匹配的筛选条件数量，仅保留匹配数量等于总筛选条件数的记录，无需多次JOIN：

SELECT
  `contacts`.* 
FROM
  `contacts` 
INNER JOIN 
  `contact_attributes`
  ON `contact_attributes`.`contact_id` = `contacts`.`id` 
WHERE
  `contacts`.`company_id` = 7
  AND (
    (`contact_attributes`.`attribute_id` = 33 AND CAST(REPLACE(contact_attributes.value, '"', '') AS CHAR) = 'A')
    OR (`contact_attributes`.`attribute_id` = 31 AND CAST(REPLACE(contact_attributes.value, '"', '') AS CHAR) = '2024')
  )
GROUP BY
  `contacts`.`id`
HAVING
  COUNT(DISTINCT `contact_attributes`.`attribute_id`) = 2 -- 2为筛选条件的总数量

逻辑说明：

• 用OR列出所有筛选条件，选中匹配任意单个条件的记录
• 按联系人ID分组后，统计匹配的不同attribute_id数量，只有数量等于筛选条件总数的联系人，才同时满足所有要求

方法2：Laravel查询构造器动态实现 #

如果筛选条件是动态生成的，用Laravel查询构造器更灵活：

$filters = [
    ['attribute_id' => '33', 'value' => 'A'],
    ['attribute_id' => '31', 'value' => '2024'],
];

$filterCount = count($filters);

$contacts = DB::table('contacts')
    ->join('contact_attributes', 'contact_attributes.contact_id', '=', 'contacts.id')
    ->where('contacts.company_id', 7)
    ->where(function ($query) use ($filters) {
        foreach ($filters as $filter) {
            $query->orWhere(function ($subQuery) use ($filter) {
                $subQuery->where('contact_attributes.attribute_id', $filter['attribute_id'])
                    ->whereRaw("CAST(REPLACE(contact_attributes.value, '"', '') AS CHAR) = ?", [$filter['value']]);
            });
        }
    })
    ->groupBy('contacts.id')
    ->havingRaw('COUNT(DISTINCT contact_attributes.attribute_id) = ?', [$filterCount])
    ->get();

逻辑说明：

• 遍历动态筛选条件，用orWhere包裹每个独立筛选规则
• 通过GROUP BY和HAVING确保匹配的条件数量等于总筛选数，实现AND逻辑

内容的提问来源于stack exchange，提问作者Juan Pablo B