查询构建器条件参数 [英] Query builder conditional parameters

问题描述

我有一个用户表，该表具有基于belongsToMany的相关数据

I have a user table that has related data on a belongsToMany basis

 users
    id
    first_name

 skills
    id
    name

 positions
    id
    name

 position_user
    position_id
    user_id
    created_at
    updated_at

 skill_user
   skill_id
   user_id
   created_at
   updated_at

在用户模型中

public function positions()
{
    return $this->belongsToMany('App\Position')->withTimestamps();
}

并处于位置

public function users()
{
    return $this->belongsToMany('App\User')->withTimestamps();
}

(技能相同)

我当前正在将以下内容传递给视图:

I am currently passing the following to a view:

$users = User::with('skills')
->with('skills')
->with('positions')
->get();

我希望能够搜索各种技能和职位组合，但是很难找到一个优雅的解决方案.

I want to be able to search on various combinations of skills and positions but am having difficulty creating an elegant solution.

如果我选择一个或多个职位，将其传递给控制器​​，我可以将信息返回为:

If I select a position or positions, pass that to the controller I can return info as:

if (Request::get('positions'))
{
    $positions = Request::get('positions');
}

positions数组看起来像哪里 数组:3 [? 0 =>分析师" 1 =>律师" 2 =>建筑师"]

Where the positions array could look like array:3 [? 0 => "Analyst" 1 => "Attorney" 2 => "Architect"]

if($positions)
{
    $users = User::with('skills')
    ->with('skills')
    ->with('positions')
    ->orWhereHas('positions', function($thisquery) use ($positions)
    {
        $thisquery->whereIn('name', $positions);
    })
    ->get();
}

如果我也用同样有效的技能做同样的事情.

If I do the same with skills that works as well.

我需要做的是将它们组合在一起-尤其是因为我将在搜索功能中添加更多相关的表.

What I need to do is to combine them - especially since I will be adding more related tables into the search function.

我尝试过:

if(($positions)&&($skills))
{
    $users = User::with('skills')
    ->with('skills')
    ->with('positions')
    ->orWhereHas('positions', function($thisquery) use ($positions)
    {
       $thisquery->whereIn('name', $positions);
    })
    ->orWhereHas('skills', function($thisquery) use ($skills)
    {
        $thisquery->whereIn('name', $skills);
    })
    ->get();
}

但是我想要的更像是

$users = User::with('skills')
->with('skills')
->with('positions');
if($skills)
{
    $users->orWhereHas('skills', function($thisquery) use ($skills)
    {
       $thisquery->whereIn('name', $skills);
    });
 }

if($positions)
{
    $users->orWhereHas('positions', function($thisquery) use ($positions)
    {
         $thisquery->whereIn('name', $positions);
    });
}
$users->get();

但是不起作用-只是返回空结果集.

However that doesn't work -just returns empty resultset.

我如何以一种优雅的方式实现这一目标?

How can I achieve this in an elegant way?

或者也许有更好的方法来实现搜索功能-实际上我只是想对某些参数进行过滤.

Or is there perhaps a better way to implement a search function - really I am just wanting to filter on certain parameters.

推荐答案

我认为您在这里需要在哪里"条件.或者其中意味着匹配一个或另一个规则或两个规则.只有两个规则都匹配，和哪里"才有效.

I think you need "and where" condition here. Or where means matching either one or another or both rules. "And where" would only work if both rules match.

所以我建议尝试一下:

$users = User::with('skills')
->with('skills')
->with('positions');
if($skills)
{
    $users->whereHas('skills', function($thisquery) use ($skills)
    {
       $thisquery->whereIn('name', $skills);
    });
 }

if($positions)
{
    $users->whereHas('positions', function($thisquery) use ($positions)
    {
         $thisquery->whereIn('name', $positions);
    });
}
$users = $users->get();

这篇关于查询构建器条件参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！