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

原文地址

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

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

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

开始实验

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

  • version: Laravel 5.5
  • Model:
    • customers -> trial_records 一对多
    • customers -> onetoone_records 一对多
  • 需求:
    • trial_recordsonetoone_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()

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

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() 只是对两个 querybindings 的简单递归合并。

在我这里的情况并不适用,因为我这里是把 $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 段,因为我的业务中,这些数据确实应该存在于第二个 queryjoin 段。

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

当然,如果你有其他的想法,欢迎表达!

本作品采用《CC 协议》,转载必须注明作者和本文链接
一冉再
《L01 基础入门》
我们将带你从零开发一个项目并部署到线上,本课程教授 Web 开发中专业、实用的技能,如 Git 工作流、Laravel Mix 前端工作流等。
《G01 Go 实战入门》
从零开始带你一步步开发一个 Go 博客项目,让你在最短的时间内学会使用 Go 进行编码。项目结构很大程度上参考了 Laravel。
讨论数量: 0
(= ̄ω ̄=)··· 暂无内容!

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!