共计 2579 个字符,预计需要花费 7 分钟才能阅读完成。
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'], [['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
<?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();
$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
即可
<?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);
}
...
}
原文链接
- https://github.com/guanguans/guanguans.github.io/issues/46
正文完