DavidPetrov's avatar

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?

0 likes
5 replies
agilasadi's avatar

Just to let you know, the code you have given is kind of not readable :/

DavidPetrov's avatar

@AGILASADI - I'm sorry for that, really forgot to get rid of the absolute value stuff. Updating the question. It's just really that people always ask for the real code so after gathering some experience I've really stopped removing even obviously innocent parts of code because problems often tend to hide namely there :D

If there's anything left unclear, please let me know. I really do find this an interesting key issue in the query builder logic and would love to engage in public investigation!

1 like
DavidPetrov's avatar

@ROBSTAR - Jeez, you saved my life here. Now that I've found the problem and you have linked me to some resources I see it's a common issue since a while, probably marked as wontfix due to incompatibility. Anyway, you saved my life here! I guess a simple compromise with the pagination links is worth the results! Using simplePaginateas of right now!

Please or to participate in this conversation.