laravel中where IN多列特殊查询类型代码示例

Laravel  /  管理员 发布于 1个月前   125

SQL 查询中有一种 in 多列特殊查询类型 比如：

select * from order where (id,buyer) in(( 1, '张三' ),( 2, '李四' )) and order.deleted_at is null

laravel 的查询构建器并没有直接支持该类型的查询。

我这边通过查询构建器 Macroable 新增了一个 whereIns 查询方法方便该类型的查询。

使用示例

<?php
use App\Models\Order;
Order::query()
    // ->whereRaw("(id,buyer) in(( 1, '张三' ),( 2, '李四' ))")
    ->whereIns(['id', 'buyer'], [[1, '张三'], [2, '李四']])
    ->dump()
    ->get();
// "select * from `order` where (id,buyer) in ((?,?),(?,?)) and `order`.`deleted_at` is null"
// array:4 [
//   0 => 1
//   1 => "张三"
//   2 => 2
//   3 => "李四"
// ]

新建QueryBuilderMacro

<?php
namespace App\Support\Macros;
use Illuminate\Contracts\Support\Arrayable;
class QueryBuilderMacro
{
    public function whereIns(): callable
    {
        /* @var Arrayable|array[] $values */
        return function (array $columns, $values, string $boolean = 'and', bool $not = false) {
            /** @var \Illuminate\Database\Eloquent\Builder $this */
            $type = $not ? 'not in' : 'in';
            $rawColumns = implode(',', $columns);
            $values instanceof Arrayable and $values = $values->toArray();
            $rawValue = sprintf('(%s)', implode(',', array_fill(0, count($values), '?')));
            $rawValues = implode(',', array_fill(0, count($columns), $rawValue));
            $raw = "($rawColumns) $type ($rawValues)";
            return $this->whereRaw($raw, $values, $boolean);
        };
    }
    public function whereNotIns(): callable
    {
        return function (array $columns, $values) {
            /** @var \Illuminate\Database\Eloquent\Builder $this */
            return $this->whereIns($columns, $values, 'and', true);
        };
    }
    public function orWhereIns(): callable
    {
        return function (array $columns, $values) {
            /** @var \Illuminate\Database\Eloquent\Builder $this */
            return $this->whereIns($columns, $values, 'or');
        };
    }
    public function orWhereNotIns(): callable
    {
        return function (array $columns, $values) {
            /** @var \Illuminate\Database\Eloquent\Builder $this */
            return $this->whereIns($columns, $values, 'or', true);
        };
    }
}

AppServiceProvider注册QueryBuilderMacro

<?php
namespace App\Providers;
use App\Support\Macros\QueryBuilderMacro;
use Illuminate\Database\Eloquent\Builder as EloquentBuilder;
use Illuminate\Database\Eloquent\Relations\Relation;
use Illuminate\Database\Query\Builder as QueryBuilder;
use Illuminate\Support\ServiceProvider;
class AppServiceProvider extends ServiceProvider
{
    ...
    public function boot()
    {
        QueryBuilder::mixin($queryBuilderMacro = $this->app->make(QueryBuilderMacro::class));
        EloquentBuilder::mixin($queryBuilderMacro);
        Relation::mixin($queryBuilderMacro);
    }
    ...
}