I have a "programs" table to store programs with many fields including "name" field or attribute. The table has "parent_id" field so that the table can store nested program hierarchy. Here is the Program.php model definition of "children" relationship:
public function children()
{
return $this->hasMany(Program::class, 'parent_id')->with(['children']);
}
Everything works fine until I have to do a custom query limiting the number of fields i.e. to just "id" and "name" fields pair to generate a JSON object for some nested dropdown/checkbox fields. (There are 100+ fields in this table)
For this I wanted to query using "select" method of query. Here is the code:
$query = $query->with([
'children' => function ($query) use ($notIn) {
$subquery = $query->select('id', 'name', 'parent_id');
return $subquery;
}
]);
The above code works only for one level though. See example output:
[
{
"id": 1,
"name": "Program 1",
"children": [
{
"id": 3,
"name": "Program 3",
"parent_id": 1,
"children": []
},
{
"id": 11,
"name": "New program here copy",
"parent_id": 1,
"children": [
{
"id": 2,
"parent_id": 11,
"name": "Program 2",
"type": "default",
"budget_summary": null,
(...includes all fields)
"children": [
{
"id": 12,
"parent_id": 11,
"name": "Program 2",
"type": "default",
"budget_summary": null
(...includes all fields)
"children": []
}
]
}
]
}
]
}
]
How do I limit the number of fields in the levels deeper than first level? I have tried many things suggested there but none has worked yet.