原文地址

通过这次联合(union)查询了解 Laravel 预处理的数据绑定

通过这次联合(union)查询了解 Laravel 预处理的数据绑定

最近做一个需求，业务要求把两种不同的记录放在同一个列表中进去筛选查询，需要查询两种记录关联的客户信息。虽然两种记录字段差距很大，但是好在我们需要的字段不多，用联合（union）查询也能做。

开始实验

这里先描述本次实验的大致情况

• version: Laravel 5.5
• Model:
  • customers -> trial_records 一对多
  • customers -> onetoone_records 一对多
• 需求：
  • 将 trial_records 和 onetoone_records 联合起来并关联 customers，通过记录的状态，记录的开始时间和客户的创建时间来筛选查询结果

第一次尝试

为了方便对比，这次的查询条件都是写死了

联合查询

查询上课时间在 4 月份的，记录状态为成功(2)的

$classStartAt = Carbon::parse('2019-04-01 10:07:57')->startOfMonth();
$classEndAt   = Carbon::parse('2019-04-01 10:07:57')->endOfMonth();

$queryOne = DB::table('trial_records')->select([
    'id',
    'customer_id',
    'status',
    DB::raw('@type := 2 as type'),
    'start_at'
]);

$queryTwo = DB::table('onetoone_records')->select([
    'id',
    'customer_id',
    'status',
    DB::raw('@type := 1 as type'),
    'start_time as start_at'
]);

//是的，两个表用于表示开始时间的字段名不同
$queryOne->whereBetween('start_at', [$classStartAt, $classEndAt]);
$queryTwo->whereBetween('start_time', [$classStartAt, $classEndAt]);

$queryOne->where('status', 2);
$queryTwo->where('status', 2);

$queryUnion = $queryOne->union($queryTwo);

关联客户表

将联合查询结果起个别名，通过 customer_id 关联

$query = DB::table(DB::raw('(' . $queryUnion->toSql() . ') as data'))
->join('customers', function ($q) {
    /** @var JoinClause $q */
    $q->on('customers.id', '=', 'data.customer_id')->whereNull('customers.deleted_at');
});

最后的条件

对客户的创建时间筛选

$startAt = Carbon::parse('2019-03-01 10:07:57')->startOfMonth();
$endAt   = Carbon::parse('2019-03-01 10:07:57')->endOfMonth();

$query->whereBetween('customers.created_at', [$startAt, $endAt]);

$res = $query->select([
    'customers.name',
    'customers.id as customer_id',
    'customers.created_at',
    'data.status',
    'data.start_at',
    'data.type',
    'data.id',
])->get();

return response()->json($res)->setStatusCode(200)

执行程序

Postman 测试

执行结果

{
    "msg": "SQLSTATE[HY093]: Invalid parameter number (SQL: select `customers`.`name`, `customers`.`id` as `customer_id`, `customers`.`created_at`, `data`.`status`, `data`.`start_at`, `data`.`type`, `data`.`id` from ((select `id`, `customer_id`, `status`, @type := 2 as type, `start_at` from `trial_records` where `start_at` between 2019-03-01 00:00:00 and 2019-03-31 23:59:59 and `status` = ?) union (select `id`, `customer_id`, `status`, @type := 1 as type, `start_time` as `start_at` from `onetoone_records` where `start_time` between ? and ? and `status` = ?)) as data inner join `customers` on `customers`.`id` = `data`.`customer_id` and `customers`.`deleted_at` is null where `customers`.`created_at` between ? and ?)",
    "code": 400,
    "status": "FAILED",
    "data": []
}

SQLSTATE[HY093]: Invalid parameter number (SQL: select customers.name, customers.id as customer_id, customers.created_at, data.status, data.start_at, data.type, data.id from ((select id, customer_id, status, @type := 2 as type, start_at from trial_records where start_at between 2019-03-01 00:00:00 and 2019-03-31 23:59:59 and status = ?) union (select id, customer_id, status, @type := 1 as type, start_time as start_at from onetoone_records where start_time between ? and ? and status = ?)) as data inner join customers on customers.id = data.customer_id and customers.deleted_at is null where customers.created_at between ? and ?)

报错了，看到爆出错误的 sql 中有很多问号，除了第一个start_at 有查询时间，剩下的都是问号。

分析

为什么呢？我们都知道问号是预处理的占位符，这显然是数据没有绑定上。

回看代码，关联客户表的时候，我们将 $queryUnion 的 sql 拼接了，但是原属于 $queryUnion 的数据绑定并没有绑定到 $query 上。

OK，找到了原因，我们继续。

第二次尝试

修改关联客户表这一段，继续查询

修改代码

mergeBindings

$query = DB::table(DB::raw('(' . $queryUnion->toSql() . ') as data'))
->mergeBindings($queryUnion)
->join('customers', function ($q) {
    /** @var JoinClause $q */
    $q->on('customers.id', '=', 'data.customer_id')->whereNull('customers.deleted_at');
});

执行代码

{
    "msg": "操作成功",
    "code": 200,
    "data": [],
    "status": "OK"
}

这次没报错，但是空数据了。

查看日志

日志中的 sql 长这样

select `customers`.`name`,
       `customers`.`id` as `customer_id`,
       `customers`.`created_at`,
       `data`.`status`,
       `data`.`start_at`,
       `data`.`type`,
       `data`.`id`
from ((select `id`, `customer_id`, `status`, @type := 2 as type, `start_at`
       from `trial_records`
       where `start_at` between '2019-04-01 00:00:00' and '2019-04-30 23:59:59'
         and `status` = '2')
      union
      (select `id`, `customer_id`, `status`, @type := 1 as type, `start_time` as `start_at`
       from `onetoone_records`
       where `start_time` between '2019-03-01 00:00:00' and '2019-03-31 23:59:59'
         and `status` = '2019-04-01 00:00:00')) as data
       inner join `customers` on `customers`.`id` = `data`.`customer_id` and `customers`.`deleted_at` is null
where `customers`.`created_at` between '2019-04-30 23:59:59' and '2'

这样的 sql 能查询出来数据才怪了，我们可以看到数据的绑定一团乱麻了已经。
为什么会这样呢？

源码分析

我们查看 Illuminate\Database\Query\Builder

get()

runSelect 就是查询

public function get($columns = ['*'])
{
    $original = $this->columns;

    if (is_null($original)) {
        $this->columns = $columns;
    }

    $results = $this->processor->processSelect($this, $this->runSelect());

    $this->columns = $original;

    return collect($results);
}

runSelect()

看到了 getBindings()

protected function runSelect()
{
    return $this->connection->select(
        $this->toSql(), $this->getBindings(), ! $this->useWritePdo
    );
}

getBindings()

属性 bindings 被转成一维数组返回

public function getBindings()
{
    return Arr::flatten($this->bindings);
}

Arr::flatten() 是一个多维数组转一位数组的方法

// Illuminate\Support\Arr
public static function flatten($array, $depth = INF)
{
    $result = [];

    foreach ($array as $item) {
        $item = $item instanceof Collection ? $item->all() : $item;

        if (! is_array($item)) {
            $result[] = $item;
        } elseif ($depth === 1) {
            $result = array_merge($result, array_values($item));
        } else {
            $result = array_merge($result, static::flatten($item, $depth - 1));
        }
    }

    return $result;
}

看来问题的关键是 bindings
我们继续分析这个属性

bindings

bindings 是一个二维数组，注意是公开属性

public $bindings = [
    'select' => [],
    'join'   => [],
    'where'  => [],
    'having' => [],
    'order'  => [],
    'union'  => [],
];

看到这里我已经大概明白为什么
bindings 这么设计并不难理解， laravel 会将所有绑定的数据分好类，这样即使你的query 没有按照 select > join > where > having > order > union 这样的顺序来写，查询的时候也一样能够对应准确的占位符号。

在我们这个例子中为什么就发生了偏差呢？
是 mergeBindings 有什么问题吗？

mergeBindings()

这就是递归合并了两个不同 query 的 bingdings，注意这是一个公开的方法

public function mergeBindings(self $query)
{
    $this->bindings = array_merge_recursive($this->bindings, $query->bindings);

    return $this;
}

打印 bindings

修改代码打印 bindings

\Log::info($queryUnion->bindings);
$query = DB::table(DB::raw('(' . $queryUnion->toSql() . ') as data'))
    ->mergeBindings($queryUnion)
    ->join('customers', function ($q) {
        /** @var JoinClause $q */
        $q->on('customers.id', '=', 'data.customer_id')->whereNull('customers.deleted_at');
    });
\Log::info($query->bindings);

查看结果

$queryUnion->bindings

{
    "select": [],
    "join": [],
    "where": [
        {
            "date": "2019-04-01 00:00:00.000000",
            "timezone_type": 3,
            "timezone": "Asia/Shanghai"
        },
        {
            "date": "2019-04-30 23:59:59.999999",
            "timezone_type": 3,
            "timezone": "Asia/Shanghai"
        },
        2
    ],
    "having": [],
    "order": [],
    "union": [
        {
            "date": "2019-04-01 00:00:00.000000",
            "timezone_type": 3,
            "timezone": "Asia/Shanghai"
        },
        {
            "date": "2019-04-30 23:59:59.999999",
            "timezone_type": 3,
            "timezone": "Asia/Shanghai"
        },
        2
    ]
}

$query->bindings

{
    "select": [],
    "join": [],
    "where": [
        {
            "date": "2019-04-01 00:00:00.000000",
            "timezone_type": 3,
            "timezone": "Asia/Shanghai"
        },
        {
            "date": "2019-04-30 23:59:59.999999",
            "timezone_type": 3,
            "timezone": "Asia/Shanghai"
        },
        2
    ],
    "having": [],
    "order": [],
    "union": [
        {
            "date": "2019-04-01 00:00:00.000000",
            "timezone_type": 3,
            "timezone": "Asia/Shanghai"
        },
        {
            "date": "2019-04-30 23:59:59.999999",
            "timezone_type": 3,
            "timezone": "Asia/Shanghai"
        },
        2
    ]
}

看到这里，其实已经很清楚了。mergeBindings() 只是对两个 query 的 bindings 的简单递归合并。

在我这里的情况并不适用，因为我这里是把 $queryUnion 的结果集作为一个表进行了内链接查询，在 $queryUnion 中的 bindings 应该全部放到 $query->bindings['join'] 中才对。

明白了原因就能找到应对的方案了。

第三次尝试

修改关联查询代码

将 $queryUnion->bindings 转成一维数组，放进 $queryUnion->bindings['join']，然后在合并

$queryUnion->bindings['join'] = Arr::flatten($queryUnion->bindings);

$query = DB::table(DB::raw('(' . $queryUnion->toSql() . ') as data'))
    ->mergeBindings($queryUnion)
    ->join('customers', function ($q) {
        /** @var JoinClause $q */
        $q->on('customers.id', '=', 'data.customer_id')->whereNull('customers.deleted_at');
    });

执行

很好，是我想要的结果

{
    "msg": "操作成功",
    "code": 200,
    "data": [
        {
            "name": "test",
            "customer_id": 93902,
            "created_at": "2019-03-14 14:10:45",
            "status": 2,
            "start_at": "2019-04-11 18:34:03",
            "type": 2,
            "id": 1
        },
        {
            "name": "test",
            "customer_id": 93905,
            "created_at": "2019-03-14 15:10:45",
            "status": 2,
            "start_at": "2019-04-12 20:34:03",
            "type": 1,
            "id": 2
        }
    ],
    "status": "OK"
}

相应的 sql

select `customers`.`name`,
       `customers`.`id` as `customer_id`,
       `customers`.`created_at`,
       `data`.`status`,
       `data`.`start_at`,
       `data`.`type`,
       `data`.`id`
from ((select `id`, `customer_id`, `status`, @type := 2 as type, `start_at`
       from `trial_records`
       where `start_at` between '2019-04-01 00:00:00' and '2019-04-30 23:59:59'
         and `status` = '2')
      union
      (select `id`, `customer_id`, `status`, @type := 1 as type, `start_time` as `start_at`
       from `onetoone_records`
       where `start_time` between '2019-04-01 00:00:00' and '2019-04-30 23:59:59'
         and `status` = '2')) as data
       inner join `customers` on `customers`.`id` = `data`.`customer_id` and `customers`.`deleted_at` is null
where `customers`.`created_at` between '2019-04-01 00:00:00' and '2019-04-30 23:59:59'

总结

这个问题的解决办法没有什么太好的能够统一解决的，只能说我们需要了解有这个问题，那么编写代码的时候应当注意，注意避坑。

在我这样的业务中，我选择了将第一个 query 中的 bingdings 全部放到 join 段，因为我的业务中，这些数据确实应该存在于第二个 query 的 join 段。

所以遇到这样的问题，大家还是应该灵活去应对。

当然，如果你有其他的想法，欢迎表达！

本作品采用《CC 协议》，转载必须注明作者和本文链接