skip to Main Content

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?

3

Answers


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