Laravel Eloquent group results by relation

Question

I am struggling to get Laravel Eloquent to retrieve and group results in the way that i'd like.

Basically I am creating a 'My Agenda' page which shows all task in order of their due date, but grouped together if 2 or more Tasks (in order) belong to the same Stage, and likewise if 2 or more stages belong to the same project.

My data is Projects -> (has many) Stages -> (has many) Tasks

I would like to output my data as follows:

Project B
    Stage 2
        Task 1 (due 1st Sep)
        Task 3 (due 2nd Sep)
    Stage 1
        Task 2 (due 3rd Sep)

Project A
    Stage 1
        Task 2 (due 4th Sep)

Project B <---- repeated as Project A has a stage->task due before these tasks due
    Stage 3
       Task 2 (due 5th Sep)


Project A  <---- repeated as Project B has a stage->task due before
    Stage 1 <---- repeated
        Task 1 (due 6th Sep)

Any ideas how I can achieve this? I am open to doing this on the front end with JS/Vue/Lodash.

Thanks in advance!

M

Answer 1

I think you can do it this way:

First, let's combine all the tables with JOIN. If you want to see all projects and stages that don't have any relational data, you can use LEFT JOIN, or maybe RIGHT JOIN, I don't know which one will work.

$tasks = Task::orderBy("due")
  ->join("stages", "stages.id", "=", task.stage_id)
  ->join("projects", "projects.id", "=", stages.project_id)
  ->select("pick the columns you want to have")
  ->get();

I think you should aim for this type of array as your output, so you won't have any issues because of the repeated key names.

/*
$output = [
  [
    'project'=> A,
    'stages'=> [
       stage_name => [task 1, task 2],
       stage_name => [task 4, task 8],
    ],
  ],
  [
    'project'=> B,
    'stages'=> [
       stage_name => [task 5],
    ],
  ],
  [...]
];
*/

To create that type of array, the function down below should work.

$output = [];

foreach($tasks => $task) {
   $project = $task['project_name'];
   $lastEntry = $output[count($output) - 1];
   if ( count($output) > 0 && $lastEntry['project'] == $project) {
      // this means $task should be inserted in the last array.
      // You should check for stages.
      if (array_key_exists($task['stage_name'], $lastEntry['stages'])) {
        $lastEntry['stages'][$task['stage_name']][] = $task;  
      } else {
        $lastEntry['stages'][$task['stage_name']] = [$task];
      }
      // I think $lastEntry['stages'][$task['stage_name']][] = $task; will work without checking stage names, but I can't be sure, you need to try it.
   } else {
      // This means you should create a new item in $output.
      $output[] = [
        'project' => name,
        'stages' => [
          'stage_name' => [$task];
        ]
      ]
   }
}

I created those codes here directly. There can be typos and everything, but the logic should work.