Sorting with Laravel relationships

Question

I have two tables tasks and task_hours, And I'm using one to many relationship in task model.(task can have many hours logs). And displaying task data at frontend in table (datatable) using with relationships to task_hours (as per query below). I need to sort by tasks table's columns (task_name, created_at) also I need to sort by total_hours which is in task_hours table, I'm using jQuery DataTable in front-end and getting column name and direction in request at my controller function.

This is my query that is only sorts for tasks table for now but, how can I tell if it is main table's column to sort or relationship table's column to sort ?

 $obj1 = Task::with("task_hours");
 $data = $obj1->orderBy($sort_col, $dir)->get()->toArray();

Let me know what is the best possible way to sort with laravel relationships? I want to do this while using relationships only and not with join query.

Thanks in advance :)

@Sumitkumar wish I could but, I've to manage in existing flow. — Kavan Prajapati, May 23 '23 at 09:54
This [help](https://stackoverflow.com/questions/18143061/laravel-orderby-on-a-relationship)? — francisco, May 23 '23 at 11:09

Sumit kumar · Answer 1 · 2023-05-23T11:19:36.087

To solve this problem: As you are using with the Task as Task instance with relation object task_hours. So, to deal with this situation instead of using with use join and addSelect:

$obj = Task::leftJoin('task_hours', 'task_hours.task_id', 'tasks.id')
             ->select('tasks.*')
             ->addSelect('task_hours.total_hours as total_hours');
$data = $obj->orderBy($sort_col, $dir);

This shall fix your issue. Since we cannot sort through with columns we are using normal sql joins and sorting it from sql queries.

Full Solution

Not optimized but it will get you going since you're using serverside true in datatables:

if($request->ajax()){
        $obj = Task::leftJoin('task_hours', 'task_hours.task_id', '=', 'tasks.id')
                        ->select('tasks.*')
                        ->addSelect('task_hours.total_hours as total_hours');
        //Initialize to data from request
        $order_col = $request->order[0]['column'];
        $order_col_name = $request->columns[$order_col]['data'] ?? $request->columns[$order_col]['name'];
        $order_dir = $request->order[0]['dir'];

        $take = $request->length ?? 10;
        $skip = $request->start ?? 0;

        $totalRecords = $obj->count();

        $data = $obj
                    ->orderBy($order_col_name, strtoupper($order_dir))
                    ->skip($skip)->take($take)
                    ->get();
        return [
            "draw" => $request->draw,
            "recordsTotal" => $totalRecords,
            "recordsFiltered" => $totalRecords,
            "data" => $data
        ];
    }

score 0 · Answer 2 · answered May 23 '23 at 11:55

Since you are getting all the data anyway you can sort in PHP rather than the database:

$obj1 = Task::with("task_hours");
$data = $obj1->get()->sortBy(function ($val) use ($sort_col) {
   if (array_key_exists($sort_col, $val->getAttributes())) {
      return $val->getAttribute($sort_col);
   }
   if (array_key_exists($sort_col, $val->task_hours->getAttributes())) {
      return $val->task_hours->getAttribute($sort_col);
   }
   return null; 
})->toArray();

This avoids the join that would will need to do if you want to order within the SQL query.

Sorting with Laravel relationships

2 Answers2

Full Solution