在 Laravel 9 中使用原生表达查询时，in 条件用于多个字段

1、参考：在 MySQL 8 中，in 条件用于多个字段 。

SELECT * FROM `customers` WHERE ( name, email ) IN (( '123', '123@outlook.com' ),
( '我是客户姓名33', '1303842899@qq.com' ))

2、需要在 Laravel 9 中使用原生表达查询时，in 条件用于多个字段。whereRaw 和 orWhereRaw 方法将原生的「where」注入到你的查询中。

$customers = DB::table('customers')
	->whereRaw('email in ?', ['(\'123@outlook.com\', \'12414dfgfdg@78.com\')'])
	->get();
print_r($customers);exit;

3、执行报错：SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘?’ at line 1 。如图1

图1

"message": "SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1 (SQL: select * from `customers` where email in ('123@outlook.com', '12414dfgfdg@78.com'))",

4、不再使用 ?，而是直接拼接出原生 SQL。查询成功。如图2

图2

$customers = DB::table('customers')
	->whereRaw('email in (\'123@outlook.com\', \'12414dfgfdg@78.com\')')
	->get();
print_r($customers);exit;

5、再次调整，以使 in 条件用于多个字段。查询成功。如图3

图3

$queryBuilder = app('Modules\Order\Models\Customer')::query();
$customers = $builder->whereRaw('(name,email) in ((\'123\',\'123@outlook.com\'),(\'我是客户姓名33\',\'1303842899@qq.com\'))')
	->get();
print_r($customers);exit;

$queryBuilder = app('Modules\Order\Models\Customer')::query();
print_r($criteria['customers']);
$str = '';
foreach ($criteria['customers'] as $key => $customer) {
	$str .= ($key == 0) ? '(\'' : ',(\'';
	$str .= implode('\',\'', $customer) . '\')';
}
print_r($str);
$customers = $builder->whereRaw('(name,email) in (' . $str . ')')->get();
print_r($customers);exit;

6、查看生成的 SQL，符合预期。如图4

图4

select
  `id`,
  `name`,
  `email`,
  `type`
from
  `customers`
where
  (name, email) in (
    ('王某人', '44445@163.com'),
    ('客户姓名', '44445@163.com'),
    ('王某某', '4444@163.com'),
    ('王某某', '44445@163.com'),
    ('李某某', '4444@163.com'),
    ('客户姓名', '4444@163.com')
  )
order by
  `id` desc

7、后续发现 SQL 报错，具体可参考：在 Laravel 9 中使用原生表达查询时，报错：SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; 。