Query builder conditional parameters

Question

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

In User model

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

and in Position

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

(the same for skills)

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');
}

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.

I have tried:

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();
}

But what I am wanting is something more like

$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.

Answer 1

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.

So I'd suggest trying this:

$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();

Answer 2

I solved this problem myself recently.

What I ended up doing was the following:

1. Get all relevant data into an array.

2. Filter that array to match parameter values

3. Return the filtered array.

You can even cache step 1 and recache when you add or update an element.

Go ahead and try an implementation. If you should get stuck, please update your question with relevant code.