Framework: Eloquent withCount having is working fine on MySql, but generates an error on Postgres

[gncvalente]

• Laravel Version: 6.0
• PHP Version: 7.3
• Database Driver & Version: psql

Description:

The following Eloquent query is working fine on mysql, but is generating an error "column products_count does not exists" in postgresql

ProductCategory::withCount('products')->having('products_count', '>' , 0)->get();

Steps To Reproduce:

Two models ProductCategory (has many) and Product (belongs to)
The query is a way to retrieve only categories that has products.

[driesvints]

Not sure what's going on here. Hopefully someone can help out.

[mfn]

@gncvalente please paste the SQL query generated (for both MySQL and PgSQL), thanks

[staudenmeir]

The SQL standard doesn't allow HAVING clauses to access derived columns like products_count. MySQL is less strict about that.

There are multiple solutions, a subquery is one of them:

$query = ProductCategory::withCount('products');

ProductCategory::fromSub($query, 'alias')
    ->where('products_count', '>', 0)
    ->get();

If you don't need the count in the results, you can also use has():

ProductCategory::has('products')->get();

[gncvalente]

Great thankyou !