SQL query to Eloquent

I have the following SQL query that does exactly what I want:

select * from locations 
where (select count(*) from machines 
            where game_id = 1 
            and locations.id = machines.location_id) > 0 
and (select count(*) from machines 
        where game_id = 2 
        and locations.id = machines.location_id) > 0

However the amount of game_ids can be from 1 to "unlimited". I'm trying to create a scope query in my Location.php (so I can continue to filter the model) that don't use DB::raw() because I don't want my eloquent to be locked to SQL, so in the future I can change database driver type without have to rewrite the query.

So far I could do this:

public function scopeWithGames($query, $games, $gamesCoexist)
{
    if(count($games) === 0) {
        return $query;
    }
        
    if(!$gamesCoexist) {
        return $query->whereHas('machines', function($machines) use($games) {
            $machines->whereIn('game_id', $games);
        });
    }
}

however if $gamesCoexist == true I want to obtain All locations which have at least one machine for each game in $games array.

To help understand the relations between the models here is schema with the models and how they relate with each other.

tiagotavares

8 years ago

Level 15

Hey!

If I got it right you want all locations that have the all the games from the array you are passing in as parameter of the scope (when games can't coexist).

If that is the case:

public function scopeWithGames($query, $games, $gamesCoexist)
{
    if(! count($games)) {
        return $query;
    }

    if($gamesCoexist) {
        return $query->whereHas('machines', function($machines) use($games) {
            foreach($games as $game) {
                $machines->where('game_id', $game);
            }
        });
    }
        
    return $query->whereHas('machines', function($machines) use($games) {
        $machines->whereIn('game_id', $games);
    });
}

Hope this helps

MarioMacedo

8 years ago

Level 13

@tiagotavares You understood what I want however the query with the foreach loop doesn't work as expected

return $query->whereHas('machines', function($machines) use($games) {
        foreach($games as $game) {
            $machines->where('game_id', $game);
        }
});

For example if I call the scope with an array of games [1,2]and with $gamesCoexist = true it returns an empty collection when it should return a collection with length 12.

tiagotavares

8 years ago

Best Answer

Level 15

Didn't understood the requirements properly, I think this scope solves your problem.

return $query->where(function($location) use($games) {
    foreach($games as $game) {  
        $location->whereHas('machines', function($query) use($game) {
            $query->where('game_id', $game);
        });
    }
});

Cheers!

1 like

MarioMacedo

8 years ago

Level 13

@tiagotavares thank you very much. It's perfect. Works like a charm. Already marked as best reply.

Please or to participate in this conversation.