Question 174170 in Laravel Question posted in
The official Laravel Documentation can be found here.

Sorting with Laravel relationships

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 🙂

Tags:

2

Answers


  1. 0

    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
        ];
    }
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search