在laravel中查询

PROBLEM

Hello! I need to do query where I response all tasks who have list_id = id and also I need to response all sub tasks who have task_id = taskID. But I can't figure out how to make right query for taskID..

MY CODE

$response['lists'] = Lists::findorfail($id);
$response['tasks'] = Tasks::latest()->where('list_id','=',$id)->get();

Upper code work properly, i get all tasks by list id.

$taskID = Tasks::latest()->where('list_id','=',$id)->get('id');

$response['subtasks'] = Subtasks::latest()->where('task_id','=',$taskID)->get();

At the moment I have this query, but it's not working, I just show it to better explain what I want to do.

There is image to see what I meant, how it must look like, when work properly

In that image you can see, that I have 2 tasks on list (i get it using upper code), on that popup form you can see that 1st task have 2 sub tasks (same as 2nd task), so i want to make query to output right sub tasks for each task.

$response['subtasks'] = Subtasks::latest()->where('task_id','=',4)->get();

I use this NOT correct code, just to show example. 'task_id','=',4 this mean that I get all sub tasks for task with id = 4. But every task now have these sub tasks. So i need query to get task id automatically and output right.

I hope you understand what I want to get.

RELATION STRUCTURE

There you can see relationships of tables. There you can see output after adding code from answer.

This line of code:

$taskID = Tasks::latest()->where('list_id','=',$id)->get('id');

will return a collection with elements are Tasks with list_id = $id

so you cant not use $taskID as id number in your query like:

$response['subtasks'] = Subtasks::latest()->where('task_id','=',$taskID)->get();

Instead you can do something like this:

// this will return an array of taskID
$taskIDs = $response['tasks']->pluck('task_id');

// then you can do it like 
foreach ($taskIDs as $taskID) {
    $response['subtasks'][$taskID] = Subtasks::latest()->where('task_id','=',$taskID)->get();
}

Updated: you can also use like this:

//This will return a collection of all subtasks    
$subtasks = Subtasks::latest()->get();

//This will turn subtasks in to group according to tasks_id
$response['subtasks'] = $subtasks->groupBy('task_id');

for all method related to Collection of Laravel you can refer it here

Regards,