In condition for multiple fields when using native expression query in Laravel 9
1. Reference: In MySQL 8, the IN condition is used for multiple fields.
SELECT * FROM `customers` WHERE ( name, email ) IN (( '123', '123@outlook.com' ),
( '我是客户姓名33', '1303842899@qq.com' ))
2. When you need to use the native expression query in Laravel 9, the in condition is used for multiple fields. WhereRaw and OrwhereRaw methods inject the native “Where” into your query.
$customers = DB::table('customers')
->whereRaw('email in ?', ['(\'123@outlook.com\', \'12414dfgfdg@78.com\')'])
->get();
print_r($customers);exit;
3. Execute error: 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. as shown in Figure 1
![执行报错：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](https://www.shuijingwanwq.com/wp-content/uploads/2024/03/1-4.png)
Figure 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, no longer use ?, but directly splice the native SQL. The query was successful. as shown in Figure 2
Figure 2
$customers = DB::table('customers')
->whereRaw('email in (\'123@outlook.com\', \'12414dfgfdg@78.com\')')
->get();
print_r($customers);exit;
5. Adjust again so that the IN condition is used for multiple fields. The query was successful. as shown in Figure 3
Figure 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. Check the generated SQL, which is in line with expectations. as shown in Figure 4
Figure 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. Follow up to find SQL errors, please refer to: When using native expression query in Laravel 9, error: sqlstate[42000]: Syntax Error or Access Violation: 1064 you have an error in your SQL syntax; .
