Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel query where a column must have specified multiple variable

I dont know how to describe this problem. Lets say i have this table in my database:

| Team | Zone |
| ---- | ---- |
| Alfa | A    |
| Alfa | BB   |
| Alfa | CCC  |
| Beta | A    |
| Beta | CCC  |
| Beta | DDDD |
| Crit | BB   |
| Crit | CCC  |

And then I make some form in my view, that can search the team name based on zone. The form looks like this
enter image description here

What i wanted are:

  • If i enter ‘A’ and ‘CCC’ in the said form, it will show team Alfa and Beta
  • If i enter ‘BB’ and ‘CCC’ in the said form, it will show team Alfa and Crit
  • If i enter only ‘BB’ in the said form, it will show team Alfa and Crit

The problem is, i haven’t figured out the query or the code to get that result. I have tried the following code:

$find = DB::table('team_zone_area')->select(
    'team'
);
        
if(isset($req->search_zones)){
    $find = $find->where('zone', $req->search_zones[0]);

    if(count($req->search_zones) > 1){
        for($j = 1; $j < count($req->search_zones); $j++){
            $find = $find->where('zone', $req->search_zones[$j]);
        }
    }
}

$find = $find->whereNull('deleted_at')
        ->groupBy('team')
        ->get();

It show zero result if i search for zone ‘A’ and ‘CCC’, or ‘BB’ and ‘CCC’, but it will show correct team if i just enter zone ‘BB’

Then i tried to tweak the code if(count($req->search_zones) > 1) into this:

if(count($req->search_zones) > 1){
    for($j = 1; $j < count($req->search_zones); $j++){
        $find = $find->orWhere('zone', $req->search_zones[$j]);
    }
}

Now it shows all teams (Alfa, Beta, Crit) when i enter ‘A’ and ‘CCC’, instead of just Alfa and Beta

Anyone have idea what the query supposed to be?

Tags:

3

Answers


  1. 0

    I am so lost, if you want to enter zones and look what it comes out of them (find all of them), then it is a normal where, do a where for each entry and then get:

    $query = DB::table('team_zone_area')->select('team');

foreach ($request->search_zones as $zone) {
    $query->where('zone', $zone);
}

$result = $query->whereNull('deleted_at')
    ->groupBy('team')
    ->get();
    Login or Signup to reply.
  2. 0

    You need a whereIn query instead of a regular one:

    $find = DB::table('team_zone_area')
    ->select('team');
        
if(isset($req->search_zones)){
    $find = $find->whereIn('zone', $req->search_zones);
}

//Aditional you don't need the groupBy clause if you just need the team
$find = $find
    ->whereNull('deleted_at')
    ->get();
    Login or Signup to reply.
  3. 0

    You can use this

    $zones = ['A', 'CCC']; # should be array `$request->zones`

$query = DB::table('team_zone_area')
    ->select('team')
    ->whereIn('zone', $zones) # use `whereIn`
    ->whereNull('deleted_at')
    ->groupBy('team')
    ->havingRaw('COUNT(DISTINCT zone) = ?', [count($zones)]) #make sure this is not empty
    ->get();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search