Selecting specific fields in recursive model association in Laravel

35 views Asked by At

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.

0

There are 0 answers