通過這次聯合 (union) 查詢瞭解 Laravel DB 的資料繫結

一冉再發表於2019-04-10

通過這次聯合(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 段。

所以遇到這樣的問題，大家還是應該靈活去應對。

當然，如果你有其他的想法，歡迎表達！

更多文章歡迎訪問一冉再的部落格

資料庫學習（六）聯合查詢ｕｎｉｏｎ
2019-01-29
資料庫
MYSQL學習筆記24: 多表查詢(聯合查詢,Union, Union All)
2024-03-10
MySql筆記
MySQL--操作簡記（聯結表，組合查詢（UNION））
2018-04-03
MySql
Laravel 通過子查詢建立動態關聯
2021-01-28
Laravel
通過shell和sql結合查詢效能sql
2014-08-18
SQL
indexedDB 通過索引查詢資料
2019-07-27
Index索引
通過Web API查詢資料
2020-12-14
WebAPI
Laravel Eloquent 關聯模型查詢快取資料
2020-11-15
Laravel模型快取
MySQL 合併查詢union 查詢出的行合併到一個表中
2023-02-01
MySql
透過union all把sql查詢的結果拼接起來
2017-10-25
SQL
通過原生js實現資料的雙向繫結
2021-07-15
JS
C#通過ODBC查詢HANA資料庫資料
2014-06-06
C#資料庫
jpa 聯合查詢資料，查詢使用者資訊與部門資訊
2019-05-25
查詢集合操作union與union all
2016-11-15
一次通過DB_LINK抽取資料過慢原因分析
2012-03-22
Laravel 查詢某天資料 whereDate
2019-04-04
Laravel
mysql求交集：UNION ALL合併查詢，inner join內連線查詢，IN/EXISTS子查詢
2018-10-15
MySql
通過shell指令碼生成查詢表資料的sql
2014-08-13
指令碼SQL
查詢繫結變數的值
2013-06-05
變數
c# combox繫結oracle查詢的資料集（列)
2012-02-28
C#Oracle
多表聯合查詢 - 基於註解SQL
2021-06-02
SQL
SQL Server 多表聯合查詢取最新一條資料
2012-06-07
SQLServer
thinkPHP多表聯合查詢
2014-09-04
PHP
WPF多表關聯資料繫結
2012-01-30
基於聯合查詢的注入
2018-07-26
DB2查詢資料庫大小
2012-06-11
DB2資料庫
jpa動態查詢與多表聯合查詢
2019-01-08
Laravel 關聯查詢限制條數和分組查詢顯示為零的結果
2019-03-22
Laravel
如題，執行原生的關聯查詢使用 Laravel 的 ORM 或者 Db 如何寫呢
2019-08-24
LaravelORM
MySQL聯結查詢和子查詢
2018-02-24
MySql
JPA 之多表聯合查詢
2020-08-18
SQL 三表聯合查詢
2019-09-26
SQL
按月分表聯合查詢
2019-09-11
01-sql-聯合查詢
2020-10-18
SQL
從最簡單的資料劫持瞭解vue雙向繫結原理
2018-06-19
Vue
mybatis多表聯合查詢的寫法
2024-04-19
MyBatis
透過shell和sql結合查詢效能sql
2015-12-26
SQL
python通過淘寶IP資料庫查詢地區
2017-11-05
Python資料庫