Level 102
Either add the same query to a where() or add a ->havingRaw('task_action IS NOT NULL')
Sep 14, 2022
5
Level 3
limit results based on the existence of a sub query column
Hi, can someone help me, I just need to limit the results based on the relation sub query column (task_action).
here's my current query
$orders = Order::select('id')
->with(['templateIndexOne' => function ($q) {
$q->addSelect(['task_action' => Task::select('action')
->whereColumn('mapping_id', 'template_mappings.id')
->latest('task_id')
->limit(1)
]);
}])
->has('templateIndexOne')
->where('user_id', auth()->id())
->paginate();
return $orders;
I only need to display all Orders where templateIndexOne.task_action = makejpglocal.
"data":[
{
"id":1452824,
"template_index_one":{
"id":138657,
"user_id":17,
"template_id":163409,
"template_index":1,
"match_item_only":185983536,
"match_product_code":null,
"match_variation":null,
"copy_template_index":null,
"copy_template_mapping_id":null,
"functions":null,
"created_at":"2022-09-09T10:36:15.000000Z",
"updated_at":"2022-09-09T10:36:15.000000Z",
"match_type":null,
"match_value":null,
"order_item_id":1452824,
"task_action":"findapproved"
}
},
{
"id":1452825,
"template_index_one":{
"id":138672,
"user_id":17,
"template_id":163408,
"template_index":1,
"match_item_only":185957465,
"match_product_code":null,
"match_variation":null,
"copy_template_index":null,
"copy_template_mapping_id":null,
"functions":null,
"created_at":"2022-09-12T06:07:23.000000Z",
"updated_at":"2022-09-12T06:07:23.000000Z",
"match_type":null,
"match_value":null,
"order_item_id":1452825,
"task_action":"makejpglocal"
}
}
],
Expected Result:
"data":[
{
"id":1452825,
"template_index_one":{
"id":138672,
"user_id":17,
"template_id":163408,
"template_index":1,
"match_item_only":185957465,
"match_product_code":null,
"match_variation":null,
"copy_template_index":null,
"copy_template_mapping_id":null,
"functions":null,
"created_at":"2022-09-12T06:07:23.000000Z",
"updated_at":"2022-09-12T06:07:23.000000Z",
"match_type":null,
"match_value":null,
"order_item_id":1452825,
"task_action":"makejpglocal"
}
}
],
Level 102
Or if it's a relationship also , you can add ->has('taskAction')
Level 3
Hi @Sinnbeck Thanks for the reply.
yes, it's a relation but a subquery column.
Level 102
@Szyfr I have updated the having clause. But does any of these work for you?
Level 3
@Sinnbeck Thank you.
I think whereHas with havingRaw works, I'll need to test it more.
Added:
->whereHas('templateIndexOne', function ($q) {
$q->addSelect(['task_action' => Task::select('action')
->whereColumn('mapping_id', 'template_mappings.id')
->latest('task_id')
->limit(1)
])
->havingRaw('task_action = ?', ['makejpglocal']);
})
Query:
$orders = Order::select('id')
->with(['templateIndexOne' => function ($q) {
$q->addSelect(['task_action' => Task::select('action')
->whereColumn('mapping_id', 'template_mappings.id')
->latest('task_id')
->limit(1)
]);
}])
->has('templateIndexOne')
->whereHas('templateIndexOne', function ($q) {
$q->addSelect(['task_action' => Task::select('action')
->whereColumn('mapping_id', 'template_mappings.id')
->latest('task_id')
->limit(1)
])
->havingRaw('task_action = ?', ['makejpglocal']);
})
->where('user_id', auth()->id())
->whereNull('archive_at')
->paginate();
return $orders;
Please or to participate in this conversation.