首页 文章

Laravel 5.4来自单个输入的Eloquent过滤器

提问于
浏览 次
1

我正在尝试使用laravel eloquent进行查询，以便将公司表与地址和电话号码连接起来，这样用户就可以输入单个输入字段，例如地址行或号码，它将查询多个表/列，只返回有潜在的匹配 .

我已经查看过wherehas但是如果数字和地址表各有匹配，它似乎会停止所有结果 . 我可以让它工作我相信这些 table 上有一个标准的左连接，但如果可能的话我想要一个干净的解决方案 . 这是我正在下面工作，实际上并没有返回任何过滤结果，所以我有点挠头 .

目标是单个输入，易于使用，但搜索多个列/表并仅返回匹配的行 . 是否有wherehas选项，但可能有多种情况？希望有人能理解我正在做的事情 .

public function addresses(){
    return $this->hasMany(Company_addresses::class, 'company_id', 'id');
}

public function digits(){
    return $this->hasMany(Company_digit::class, 'company_id', 'id');
}

public static function search($request)
{
    $filters = $request->all();
    $sortby = isset($filters['sortby']) ? $filters['sortby'] : 'created_at';
    $orderby = isset($filters['orderby']) ? $filters['orderby'] : 'desc';

    return static::with([
        'addresses' => function ($query) use ($filters) {
        $query->where('address_line_1', "LIKE", '%'.$filters['name'].'%')
            ->orWhere('address_line_2', "LIKE", '%'.$filters['name'].'%')
            ->orWhere('city', "LIKE", '%'.$filters['name'].'%')
            ->orWhere('county', "LIKE", '%'.$filters['name'].'%')
            ->orWhere('post_code', "LIKE", '%'.$filters['name'].'%');
        },
        'digits' => function ($query) use ($filters) {
        $query->where('number', "LIKE", '%'.$filters['name'].'%')
            ->orWhere('extension', "LIKE", '%'.$filters['name'].'%');
        }])
        ->orderBy('companies.'.$sortby, $orderby)
        ->select(['id', 'registered_name', 'trading_name', 'created_at', 'type'])
        ->paginate(20);
}
laravel laravel-5 laravel-eloquent

2 回答

  • 1

    这就是你如何用关系方法做到这一点 .

    你确实需要使用 ->whereHas() ，但你需要在 ->where(function ($q) ...) 方法中包装 ->whereHas('addresses', ...) 和 ->orWhereHas('digits', ...) .

    像这样：

    public static function search($request)
{
    $filters = $request->all();
    $sortby = isset($filters['sortby']) ? $filters['sortby'] : 'created_at';
    $orderby = isset($filters['orderby']) ? $filters['orderby'] : 'desc';

    return self::where(function ($q) use ($filters) {
            $q->whereHas('addresses', function ($q) use ($filters) {
                $q->where('address_line_1', "LIKE", '%'.$filters['name'].'%')
                    ->orWhere('address_line_2', "LIKE", '%'.$filters['name'].'%')
                    ->orWhere('city', "LIKE", '%'.$filters['name'].'%')
                    ->orWhere('county', "LIKE", '%'.$filters['name'].'%')
                    ->orWhere('post_code', "LIKE", '%'.$filters['name'].'%');
            })
            ->orWhereHas('digits', function ($q) use ($filters) {
                $q->where('number', "LIKE", '%'.$filters['name'].'%')
                    ->orWhere('extension', "LIKE", '%'.$filters['name'].'%');
            });
        })
        ->with(['addresses', 'digits'])
        ->orderBy('companies.'.$sortby, $orderby)
        ->select(['id', 'registered_name', 'trading_name', 'created_at', 'type'])
        ->paginate(20);
}

    虽然上面的代码应该工作，但我建议将搜索方法从 static 方法重写为scope . 我肯定会以保持 $request 的方式重写它，因为请求与模型无关 .

    像这样：

    // Use it like: \App\Company::search($request->all())->paginate(20);
public function scopeSearch($q, $filters)
{
    $sortBy = isset($filters['sortby']) ? $filters['sortby'] : 'created_at';
    $orderBy = isset($filters['orderby']) ? $filters['orderby'] : 'desc';

    return $q->where(function ($q) use ($filters) {
            $q->whereHas('addresses', function ($q) use ($filters) {
                $q->where('address_line_1', "LIKE", '%'.$filters['name'].'%')
                    ->orWhere('address_line_2', "LIKE", '%'.$filters['name'].'%')
                    ->orWhere('city', "LIKE", '%'.$filters['name'].'%')
                    ->orWhere('county', "LIKE", '%'.$filters['name'].'%')
                    ->orWhere('post_code', "LIKE", '%'.$filters['name'].'%');
            })
            ->orWhereHas('digits', function ($q) use ($filters) {
                $q->where('number', "LIKE", '%'.$filters['name'].'%')
                    ->orWhere('extension', "LIKE", '%'.$filters['name'].'%');
            });
        })
        ->with(['addresses', 'digits'])
        ->orderBy('companies.'.$sortby, $orderby)
        ->select(['id', 'registered_name', 'trading_name', 'created_at', 'type']);
}
    回复于
  • 0

    既然你在我能理解之前得到了答案 . 这是一个简单的代码重构，我将在澄清问题后发布 .

    public static function search()
{
    $name = request('name');

    return static::whereHas('addresses', function ($query) use ($name) {
            $query->where('address_line_1', "like", "%{$name}%")
                ->orWhere('address_line_2', "like", "%{$name}%")
                ->orWhere('city', "like", "%{$name}%")
                ->orWhere('county', "like", "%{$name}%")
                ->orWhere('post_code', "like", "%{$name}%");
        })->orWhereHas('digits', function ($query) use ($name) {
            $query->where('number', "like", "%{$name}%")
                ->orWhere('extension', "like", "%{$name}%");
        })
        ->orderBy('companies.'.request('sortby', 'created_at'), request('orderby', 'desc'))
        ->select(['id', 'registered_name', 'trading_name', 'created_at', 'type'])
        ->paginate(20);
}
    回复于

相关问题