laravel 中 in 多列特殊查询类型解决方案

本篇文章主要是结合我之前面试的各种经历和实战开发中遇到的问题解决经验整理的，希望这篇《laravel 中 in 多列特殊查询类型解决方案》对你有很大帮助！欢迎收藏，分享给更多的需要的朋友学习~

SQL 查询中有一种 in 多列特殊查询类型，大概是这个样子

<?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($columns), '?')));
            $rawValues = implode(',', array_fill(0, count($values), $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 注册

<?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

今天关于《laravel 中 in 多列特殊查询类型解决方案》的内容介绍就到此结束，如果有什么疑问或者建议，可以在golang学习网公众号下多多回复交流；文中若有不正之处，也希望回复留言以告知！