Recursive relationship and nested eager loading with constraints-CodePudding

I'm tryng to create nested eager loading with a where constraint on a recursive relationship

Models and query simulation:

Model Hierarchy

<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;

class Hierarchy extends Model
{
    protected $table = 'hierarchy';
    protected  $primaryKey = 'id_hierarchy';

    protected $fillable = [        
        'name',
        'parent_id'
    ];

    /**
     * @return HasMany 
     * 
     * This method implement recursive relationship
     */
    public function children()
    {
        return $this->hasMany(Hierarchy::class, 'parent_id')->with('children');
    }

    /**
    * @return HasMany
    */
    public function grandchildren()
    {
        return $this->hasMany(Grandchild::class, 'id_hierarchy');        
    }
}

Model Grandchild

<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;

class Grandchild extends Model
{
    protected $table = 'grandchildren';
    protected  $primaryKey = 'id';

    protected $fillable = [        
        'id_hierarchy',
        'id_something'
        'name'
    ];

     /**
     * @return BelongsTo
     */
    public function hierarchy()
    {
        return $this->belongsTo(Hierarchy::class, 'id_hierarchy');
    }

}

The following query does not return the grandchildren as it was supposed to;

 public function read($id) 
{

   $data = Hierarchy::query()
           ->whereNull('parent_id')
           ->with(['children.grandchildren' => function ($query) use($id)  {
                  $query->where('id_something',  $id);
           }])
           ->get();
}

The problem is in the constrain, because with the following query it returns the grandchildrren (although not filtered because it doesn't have the where condition)

$data = Hierarchy::query()
        ->whereNull('parent_id')
        ->with(['children.grandchildren'])
        ->get();

Thanks in advance for suggestions to resolve this issue.

Edited:

As the code is a simulation of the real case, I added 'id_something' to be clearer what is involved.

'id_something' is related to another model that is not represented here

CodePudding user response：

Assuming that HomeCity is one of the related models for GrandChild and the relationship is defined as

//GrandChild.php
public function home_city()
{
    return $this->hasMany(HomeCity::class);
}

Then the query to return GrandChild records who live in HomeCity (id_something is a column on home_city table) identified by $id may written as:

public function read($id) 
{

   $data = Hierarchy::query()
           ->whereNull('parent_id')
           ->with(['children' => function ($query) use($id)  {
               $query->with(['grandchildren' => function($query) use($id) {
                   $query->whereHas('home_city', fn($query) => $query->where('id_something',  $id);
               }]);                  
           }])
           ->get();
}