SQL 查问中有一种 in 多列非凡查问类型，大略是这个样子 select * from order where (id,buyer) in(( 1, '张三' ),( 2, '李四' )) and order.deleted_at is null。laravel 的查问构建器并没有间接反对该类型的查问。我这边通过查问构建器 Macroable 新增了一个 whereIns 查询方法不便该类型的查问。

应用示例

<?phpuse App\Models\Order;Order::query()    // ->whereRaw("(id,buyer) in(( 1, '张三' ),( 2, '李四' ))")    ->whereIns(['id', 'buyer'], [        ['buyer' => '张三', 'id' => 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

<?phpnamespace 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();            $values = array_map(function ($value) use ($columns) {                if (array_is_list($value)) {                    return $value;                }                return array_reduce($columns, function ($sortedValue, $column) use ($value) {                    $sortedValue[$column] = $value[$column] ?? trigger_error(                        sprintf(                            '%s: %s',                            'The value of the column is not found in the array.',                            $column                        ),                        E_USER_ERROR                    );                    return $sortedValue;                }, []);            }, $values);            $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 即可

<?phpnamespace 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);    }    ...}

原文链接

  • https://github.com/guanguans/guanguans.github.io/issues/46