DavidPetrov
5 months ago

whereHas in advanced leftJoin

Posted 5 months ago by DavidPetrov

So I was having the following query which was working just fine returning all the orders with paid documents having positive debt (difference between order's price and the sum of paid documents' prices):

$query->leftJoin('documents', function($j){ 
                return $j->on('orders.id', '=', 'documents.order_id')->where('documents.is_paid', true);})
                ->havingRaw('price_to_customer - sum(documents.price) > ?', [0])
                ->groupby('orders.id'); //hope it's understandable

Now I need to extend the following query adding a check for the document's category's type in the body of the join clause (after on) as an additional constraint. I tried intuitively:

$query->leftJoin('documents', function($j){ 
                return $j->on('orders.id', '=', 'documents.order_id')
                        ->whereHas('category', function($q){
                            return $q->where('type', 'revenue');
                        })->where('documents.is_paid', true);
                    })
                ->havingRaw('(case when price_to_customer - abs(sum(documents.price)) < 1 then null else price_to_customer - sum(documents.price) end) > ?', [0])
                ->groupby('orders.id');

but it doesn't work properly and I think it's generating a conflictig query in general. What this produces is the following error:

Column not found: 1054 Unknown column 'has' in 'on clause' (SQL: select `orders`.*, (select count(*) from `montages` where `orders`.`id` = `montages`.`order_id`) as `montages_count` from `orders` left join `documents` on `orders`.`id` = `documents`.`order_id` and `has` = category and `documents`.`is_paid` = 1 where exists (select * from `order_categories` where `orders`.`order_category_id` = `order_categories`.`id` and `program_name` in (order, offer, service, ra_workshop, company_expense, income, expense, income_only)) group by `orders`.`id` having (case when price_to_customer - abs(sum(documents.price)) < 1 then null else price_to_customer - sum(documents.price) end) > 0 order by `number` desc limit 51 offset 0)

As you can notice, it appears around this part: andhas= category and and I find it really odd that the whereHas method is adding has as an additional required column in the query...

Anyway, I hope you get what I'm trying to achieve. Any ideas? Thanks in advance!

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