【南唐】《望江梅·闲梦远》 李煜：闲梦远，南国正芳春。船上管弦江面绿，满城飞絮滚轻尘。忙杀看花人。闲梦远，南国正清秋。千里江山寒色暮，芦花深处泊孤舟。

永夜

没有不值得去解决的问题，也没有不值得去学习的技术！

Using the IN Condition for Multiple Fields With Raw Expressions in Laravel 9

Check the generated SQL, which matches expectations

作者：

永夜

在

, , , ,
Views: 50

1. Reference: Using the IN condition for multiple fields in MySQL 8.

SQL

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

2. I needed to use the IN condition for multiple fields when using raw expression queries in Laravel 9. The whereRaw and orWhereRaw methods inject a raw “where” clause into your query.

PHP

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

3. Execution 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. See Figure 1.

Execution 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
Figure 1
Plaintext

"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. Instead of using ?, I directly concatenated the raw SQL. The query succeeded. See Figure 2.

Instead of using ?, directly concatenate the raw SQL. The query succeeded
Figure 2
PHP

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

5. Adjusted again to apply the IN condition to multiple fields. The query succeeded. See Figure 3.

Adjusted again to apply the IN condition to multiple fields. The query succeeded
Figure 3
PHP

$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;
PHP

$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. Checked the generated SQL, which matched expectations. See Figure 4.

Check the generated SQL, which matches expectations
Figure 4
SQL

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. Later, I encountered an SQL error. For details, see: Error when using raw expression queries in Laravel 9: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax;.

关于作者 · 持续学习与技术实践

我是一名拥有 15 年以上开发经验的技术从业者，自 2013 年起持续运营个人技术博客，记录工作与个人项目中遇到的真实问题，以及解决问题过程中的探索、实践和思考。

除了技术写作，我也在持续开发和维护自己的独立项目，探索新技术和新工具在实际场景中的应用。我始终相信，没有不值得去解决的问题，也没有不值得去学习的技术。

如果这篇文章对你有所帮助，欢迎浏览博客中的其他文章，也欢迎交流技术经验与想法。

关于我  ·  GitHub  ·  邮件联系

更多文章