CODEWITHSUNDEEP
phpmysqllaravel-5eloquentlaravel-query-builder
Orbitall
Orbitall

Reputation: 661

Laravel Eloquent - Query builder cant find column with having function

I have a pivot table 'game_genre'(with game_id and genre_id). The game and genre model has a belongsToMany relationship similar to example below.

I have been attempting to gather the games which contain both genre_id of 60 and 55 together. I have been getting the correct result using the following SQL query, but when using the following query builder I end up getting a column not found error when using the having() function.

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'genre_id' in 'having clause'

Im not sure how else to structure the query builder?

MODEL:

class Game extends Model
{    
    public function genres()
    {
         return $this->belongsToMany('App\Genre');
    }
}

SQL:

SELECT *
FROM game_genre
WHERE genre_id = 55 OR genre_id = 60
GROUP BY game_id
HAVING COUNT(DISTINCT genre_id) = 2;

CONTROLLER:

$game = Game::whereHas('genres', function ($query)
{
    $query->where('genre_id', '55')
        ->orWhere('genre_id', '60')
        ->groupBy('game_id')
        ->having('genre_id','=', 2);
})->get();

Upvotes: 0

Views: 2142

Answers (2)

simon
simon

Reputation: 2946

You forgot the aggregate function (in this case COUNT) in your HAVING condition:

$query->where('genre_id', '55')
    ->orWhere('genre_id', '60')
    ->groupBy('game_id')
    ->havingRaw('COUNT(DISTINCT genre_id) = 2');

Instead of adding several where() and orWhere() to your query, you could also use whereIn() which takes an array: 

$myArray = [55,60];
$query->whereIn('genre_id', $myArray)
    ->groupBy('game_id')
    ->havingRaw('COUNT(DISTINCT genre_id) = 2');

Upvotes: 3

Amit Gupta
Amit Gupta

Reputation: 17658

You can use the following query to get the Games which contain both genre_id of 60 and 55:

$games = Game::whereHas('genres', function ($query) {
                $query->where('genre_id', '55');
            })
            ->whereHas('genres', function ($query) {
                $query->where('genre_id', '60');
            })
            ->get();

Upvotes: 0

Related Questions