Laravel查询HAVING子句提示average_rating列不存在问题咨询

Hey there! Let's dig into why you're hitting that "unknown column average_rating" error even though you've clearly defined the alias in your SELECT clause.

Possible Root Causes #

• Database alias restrictions: Not all databases let you reference SELECT aliases in the HAVING clause. For example, PostgreSQL strictly enforces this rule—even if your generated SQL looks valid, it'll throw an error if you're using a database that doesn't support alias references in HAVING.
• Laravel query builder parsing quirk: While MySQL does support using aliases in HAVING, sometimes Laravel's query builder struggles to resolve the alias correctly when combining conditional clauses like when() with having().

Fix Options to Try #

1. Switch to havingRaw() (Most Reliable Fix)

The simplest and most database-agnostic solution is to replace your having() calls with havingRaw(), repeating the exact calculation from your selectRaw() instead of relying on the alias. This bypasses any alias resolution issues entirely:

->when($request->from_rating, function($query, $from_rating){
    $query->havingRaw('COALESCE(ROUND(AVG(reviews.rating),1), 0) >= ?', [$from_rating]);
})
->when($request->to_rating, function($query, $to_rating){
    $query->havingRaw('COALESCE(ROUND(AVG(reviews.rating),1), 0) <= ?', [$to_rating]);
})

Using parameter binding (? with the value array) keeps your query safe from SQL injection while eliminating the alias dependency.

2. Check MySQL Strict Mode (If Using MySQL)

If you're on MySQL, head to your config/database.php file and check if strict mode is enabled. Sometimes strict mode can cause unexpected behavior with aliases in HAVING clauses. You can temporarily disable it to test:

'mysql' => [
    // ... other config settings
    'strict' => false,
],

Note: Disabling strict mode isn't ideal for production, but it's a quick way to confirm if this is the issue. If it works, stick with the havingRaw() approach for production compatibility.

3. Verify Request Parameter Names

While your generated SQL looks correct, it's worth double-checking that $request->from_rating and $request->to_rating are being passed properly. A small typo (like fromRating instead of from_rating) could lead to unexpected behavior, though this is less likely given your SQL output.

Why This Works #

Some databases evaluate the HAVING clause before resolving SELECT aliases, which means the alias doesn't exist when the database tries to check the condition. By repeating the calculation in havingRaw(), you remove the need for the database to resolve the alias, making the query work across all database systems and avoiding Laravel parsing quirks.

内容的提问来源于stack exchange，提问作者Nika Kurashvili