Level 11
You mean ->withCount(table)->orderBy(table_count)?
Hard to answer without a specific example.
2 likes
Aug 4, 2023
7
Level 4
Sorting with closure with Eloquent query builder
On the collection, I can do the sorting with a closure like this
$collection = collect([
['name' => 'Desk', 'colors' => ['Black', 'Mahogany']],
['name' => 'Chair', 'colors' => ['Black']],
['name' => 'Bookcase', 'colors' => ['Red', 'Beige', 'Brown']],
]);
$sorted = $collection->sortBy(function (array $product, int $key) {
return count($product['colors']);
});
$sorted->values()->all();
How can I do the same with the Eloquent query builder?
Level 4
What I wanted to do is this. I wanted the list to start with the tasks that the current user created.
$taskList = Task::query();
//Lots of where causes on the query builder here...
//...
//With Spatie package, I turn the query builder to collection with..
// $taskList = $taskList->orderBy('id', 'DESC')->get();
$taskList = $taskList->sortBy(function ($task) {
if ($task->created_by_id == auth()->user()->id) {
return 1;
} else {
return 2;
}
});
$taskList = $taskList->paginate($items ?? 10)
->appends($request->all());
I ended up working around it by installing the Spatie package to enable the pagination on the collection. I'm not so sure it is a scalable solution though. Would it cause a memory problem if the task list goes very large?
I guess I might be loading the whole list in memory in one go instead of one page at a time with normal pagination?
Thanks for your youtube video on the pagination with collection btw 🙏
I think you mentioned in the video that this is rare and I don't think what I'm trying to do is that rare. So I think I must be missing some tricks here.
Level 51
If you're going to use PHP logic, then you will not be able to it with Eloquent.
Eloquent converts the logic into SQL. Then we aren't able to add PHP logic their.
If you want to use it like you mentioned, then need to fetch the result first then add the closure as you did in original post example. But as you mentioned it's a memory problem.
1 like
Level 122
You can use a computed column in the database to get the results direct from the database sorted with the owner first
$taskList = $taskList->select('*')
->selectRaw('(case when created_by_id = ? then 0 else 1 end) as owner', [auth()->user()->id])
->orderBy('owner')
->paginate($items ?? 10);
Sets a new column on the query result of owner and then orders by that column.
2 likes
Level 4
Thank you all 👍
Please or to participate in this conversation.