DavidPetrov
4 months ago

Weird behaviour on paginating with join and `having` clause

Posted 4 months ago by DavidPetrov

So after continuosly trying a lot of options related to a query I've come to a very strange matter I'd consider a problem with the query builder... since I can't find any other logical explanation.

Basically I've got two tables: orders and revenues. Order has many Revenues and I want to only load the orders whose price is greater than the sum of the paid revenues, in other words - orders with hanging debts on them. price_to_customer is an existing column in the orders table, is_paid quite obviously is a boolean column in my revenues table.

Building my query I've come to a very strange situation:

This works perfectly just as expected:

$orders = App\Order::leftJoin('revenues', function($join){ 
                    return $join->on('orders.id', '=', 'revenues.order_id')->where('revenues.is_paid', true);
                })
                ->havingRaw('price_to_customer - sum(revenues.price) > ?', [0])
                ->groupby('orders.id')
                ->get();

Whereas this:

$orders = App\Order::leftJoin('revenues', function($join){ 
                    return $join->on('orders.id', '=', 'revenues.order_id')->where('revenues.is_paid', true);
                })
                ->havingRaw('price_to_customer - sum(revenues.price) > ?', [0])
                ->groupby('orders.id')
                ->paginate(10);

throws a very odd error:

Column not found: 1054 Unknown column 'price_to_customer' in 'having clause' (SQL: select count(*) as aggregate from `orders` left join `revenues` on `orders`.`id` = `revenues`.`order_id` and `revenues`.`is_paid` = 1 group by `orders`.`id` having price_to_customer - (case when abs(sum(revenues.price)) < 1 then null else sum(revenues.price) end) > 0)

So it comes that ->paginate() leads to an existing table column not being recognized by the query? Any ideas as of what could be causing this behaviour?

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