Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
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!
whereHas only works for Models, joining works on tables. So what Eloquent does is use the where syntax because it thinks you want to check for the column "has" (like you could do wherePrice(10) to check the "price" column).
So if you need to use a join instead of a whereHas in your original query you need to put that where differently and cannot use whereHas.
@FTIERSCH - Okay, I got this one, didn't know it could work that way! But the problem still persists, I really am clueless as to how to achieve what I need as described...
Either you program the function that whereHas does yourself (which is a subselect) or you use another join to add another where condition. Probably your documents have a connection to "category" so basically you just add another join that adds that connection and then you can have your "where('category.type', 'revenue')" condition
@FTIERSCH - Sorry for the late response, but you answer was actually what I needed! This is how my query looks now and it's working, although it's unimaginably slow (taking ~30s to load)...
$query->leftJoin('documents', function($j){
return $j->on('orders.id', '=', 'documents.order_id')
->where('documents.is_paid', true)
->leftJoin('document_categories', 'documents.document_category_id', '=', 'document_categories.id')
->where('document_categories.type', 'revenue');
})
->havingRaw('price_to_customer - sum(documents.price) > ?', [1])
->groupby('orders.id');
I suppose the slowing is comnig from the fact that the documents table contains ~10k records, whereas the orders table contains an additional ~6k, thus things are getting complicated when looping keys...
Regarding functionality though it's all fine. Any optimization ideas are welcome! Thanks a lot!
Might be too late, but for anyone like me coming across this thread, you can use "Sub query joins"
For example
$docQuery = Document::query()->whereHas(...);
$query->leftJoinSub($docQuery, 'documents', 'orders.id', 'documents.id')
Please or to participate in this conversation.