Skip to content

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

Open
@guanguans

Description

@guanguans
Owner

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

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('The value of the column is not found in the array.: %s', $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 中注册 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);
    }
    ...
}

原文链接

Activity

changed the title [-]laravel 中多列 in 特殊查询类型解决方案[/-] [+]laravel 中 in 多列特殊查询类型解决方案[/+] on Jul 5, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

      Development

      No branches or pull requests

        Participants

        @guanguans

        Issue actions

          laravel 中 in 多列特殊查询类型解决方案 · Issue #46 · guanguans/guanguans.github.io