Just to let you know, the code you have given is kind of not readable :/
Weird behaviour on paginating with join and `having` clause
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?
I believe this is down to the paginate() method calling a COUNT(*). Does your query work if you use simplePaginate(10) instead of paginate(10)?
This is discussed more at https://github.com/laravel/framework/issues/3105
Please or to participate in this conversation.