SQL query to Eloquent

Posted 1 year ago by MarioMacedo

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.

Please sign in or create an account to participate in this conversation.

Reply to

Use Markdown with GitHub-flavored code blocks.