Laravel自定义查询构建器跨关联表查询返回空值问题

问题场景 #

我有purchases表和purchase_company_details表，想要实现只要任一表的对应字段匹配搜索值就返回记录，但当前当搜索值仅存在于其中一个字段时会返回空值。

示例 #

• 搜索PO时：purchases表的po_no字段和purchase_company_details表的name字段中，name字段有匹配值，能正常返回数据；
• 搜索PO-时：PO-仅存在于po_no字段，name字段无匹配，此时返回空值。

现有查询日志 #

[2023-02-21 14:20:09] local.DEBUG: array (
  0 => 
  array (
    'query' => 'select count(*) as aggregate from "purchases" where "po_no"::text like ? and exists (select * from "purchase_company_details" where "purchases"."id" = "purchase_company_details"."purchase_id" and "name"::text like ?)',
    'bindings' => 
    array (
      0 => '%PO-07%',
      1 => '%PO-07%',
    ),
    'time' => 4.63,
  ),
)

请求URL参数 #

api/v1/purchase?page=1&order_column=id&order_direction=desc&filter_match=or&f[1][column]=po_no&f[1][operator]=contains&f[1][query_1]=PO-&f[2][column]=seller_details.name&f[2][operator]=contains&f[2][query_1]=PO

自定义查询构建器代码 #

...
class CustomQueryBuilder
{

    protected $defaultFilterMatch = 'and';

    public function apply($query, $data)
    {
        return $query->when(isset($data['f']), function ($query) use ($data) {
            foreach ($data['f'] as $filter) {
                $this->applyFilter($filter, $query, $data);
            }
        });
    }

    protected function applyFilter($filter, $query, $data)
    {
        $filter['match'] = $data['filter_match'] ?: $this->defaultFilterMatch;
        if (strpos($filter['column'], '.') !== false) {
            // nested column
            list($relation, $relatedColumn) = explode('.', $filter['column']);
            $filter['column'] = $relatedColumn;
            $filter['match'] = 'and';

            $query->whereHas($relation, function ($q) use ($filter) {
                $this->{camel_case($filter['operator'])}($filter, $q);
            });
        } else {
            // normal column
            $this->{camel_case($filter['operator'])}($filter, $query);
        }
    }

...
// contains:q1
    protected function contains($filter, $query)
    {
        return $query->where($filter['column'], 'like', '%' . $filter['query_1'] . '%', $filter['match']);
    }

...

}

问题核心 #

单参数查询正常，但多字段查询无法实现“任一字段匹配即返回”的需求。当前生成的SQL用and关联主表条件和关联表的exists条件，导致必须两个条件同时满足才会返回结果。

解决方案 #

问题出在两处：一是处理关联字段时强制覆盖了全局的filter_match配置，二是多条件叠加时未按or逻辑处理关联表查询。修改步骤如下：

1. 重构apply方法，统一包裹过滤条件 #

将所有过滤条件放入闭包中，根据全局filter_match配置决定是用and还是or关联：

public function apply($query, $data)
{
    return $query->when(isset($data['f']), function ($query) use ($data) {
        $matchType = $data['filter_match'] ?: $this->defaultFilterMatch;
        
        // 用闭包包裹所有过滤条件，统一处理逻辑关联
        $query->where(function ($q) use ($data, $matchType) {
            foreach ($data['f'] as $filter) {
                $filter['match'] = $matchType;
                $this->applyFilter($filter, $q, $data);
            }
        });
    });
}

2. 修改applyFilter方法，取消强制设置match为and #

关联字段的匹配逻辑遵循全局配置，根据match值选择whereHas或orWhereHas：

protected function applyFilter($filter, $query, $data)
{
    if (strpos($filter['column'], '.') !== false) {
        list($relation, $relatedColumn) = explode('.', $filter['column']);
        $filter['column'] = $relatedColumn;
        
        // 根据全局匹配类型选择对应的关联查询方法
        $method = $filter['match'] === 'or' ? 'orWhereHas' : 'whereHas';
        $query->$method($relation, function ($q) use ($filter) {
            $this->{camel_case($filter['operator'])}($filter, $q);
        });
    } else {
        $this->{camel_case($filter['operator'])}($filter, $query);
    }
}

3. 调整contains方法，正确处理逻辑类型 #

根据match值选择where或orWhere：

protected function contains($filter, $query)
{
    $method = $filter['match'] === 'or' ? 'orWhere' : 'where';
    return $query->$method($filter['column'], 'like', '%' . $filter['query_1'] . '%');
}

修改后生成的SQL会变成：

select count(*) as aggregate from "purchases" 
where (
    "po_no"::text like ? 
    or exists (select * from "purchase_company_details" where "purchases"."id" = "purchase_company_details"."purchase_id" and "name"::text like ?)
)

此时只要主表字段或关联表字段任一匹配，就会返回对应记录。

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