Crawdingle Crawdingle - 29 days ago 10
PHP Question

How can I select certain columns from my recursive child call

I'm pretty new to Eloquent and I'm having issues wrapping my head around something.

Basically I have a table which I'm recursively grabbing children from within the same table.

public function children() {
return $this->hasMany(static::class, 'parent_org_id');
}

public function childrenRec()
{
return $this->children()->with('childrenRec');
}


Where childrenRec is a recursive call to all children based on 'parent_org_id'

I'm calling it from the following in a static function, as of right now I only want the id and the name_en of the org

self::select('id','name_en')->where('parent_org_id','=',0)->with('childrenRec')->get()->toArray();


which is grabbing the top level org (my top level org has a parent_org_id of 0).

My issue is that in the recursively grabbed children it doesn't limit it to the id and the name_en

My question boils down to:
How can I select only certain columns from my recursive child calls, as well is this the 'proper' way of doing things?

My returned array looks like this.

array:1 [▼
0 => array:4 [▼
"id" => 1
"name_en" => "Org Unit"
"org_type" => null
"children_rec" => array:2 [▼
0 => array:27 [▼
"id" => 2
"name_en" => "My First Orgunit."
"code" => null
"abbreviation" => null
"address1" => "222 Street Street"
"address2" => null
"city_id" => 1
"province_id" => 14
"postalcode" => "C161L7"
"country_id" => 38
"contact_name" => null
"contact_title" => null
"email" => "test@test.com"
"fax" => "902-555-5555"
"phone1" => "5125125125125"
"phone2" => null
"org_type_id" => 1
"parent_org_id" => 1
"ref_id" => 79
"has_users" => 1
"created_at" => "2016-11-02 18:47:55"
"updated_at" => "2016-11-02 18:47:55"
"org_type" => array:4 [▶]
"children_rec" => array:1 [▶]
]
1 => array:27 [▶]
]
]
]


Thanks in advance.

Answer

To access the relation query in the with() method you use an array with the name of the relationship as the key and a closer with an instance of the query build injected. One 'gotcha' that it took me forever to track down a solution to when doing this is, your parent and children queries need to include the key that that associates their relationship because the relationship is attached after both queries are ran separately using the columns defined in the relation on the model. In your situation it would be:

self::select('id','name_en')
    ->where('parent_org_id','=',0)
    ->with(['childrenRec' => function($query) {
        return $query->select('id', 'name_en', 'parent_org_id');
    }])
    ->get()
    ->toArray();

If you don't include the parent_org_id in the subquery the relationships won't get attached.

Docs

Comments