Eloquent recursive self join in addition to another table join

Question

Frist, sorry for the not so clear title, I hope I can explain it better here:

I have a MenuItem model which has recursive self join to represent multilevel menu. In addition, the menu item actually might have a foreign key to Post model. The model is like this:

class MenuItem extends Model
{
    use HasFactory;

    public function parentItem()
    {
        return $this->belongsTo(MenuItem::class, 'parent_menu_id', 'id');
    }

    public function childrenItems()
    {
        return $this->hasMany(MenuItem::class, 'parent_menu_id', 'id');
    }

    public function allChildrenItems()
    {
        return $this->childrenItems()->with('allChildrenItems');
    }

    public function post()
    {
        return $this->belongsTo(Post::class, 'post_id', 'id');
    }

}

to retrieve all menu items recursively I do this and it works perfectly:

$menu = MenuItem::with('allChildrenItems')->whereNull('parent_menu_id')->get();

I had to add the condition whereNull to retrieve first only the top level menu items, and the recursive join will bring the rest of items based on the hirarchy.

The problem is that I also need to join every menu item with the proper post. I tried to update the above eloquent query to be like this:

$menu = MenuItem::with('allChildrenItems')->whereNull('parent_menu_id')
                ->leftJoin('posts', 'posts.id', '=', 'menu_items.post_id')
                ->get();

But that left join only works for the top level collection whereNull('parent_menu_id'), and will not be resented in the recursive join (the children menu items).

How can I add a join to both parent and children items here?

Answer 1

In your parentItem and childrenItems relationship, add with('post') to it so that post will be added to every level.

public function parentItem()
{
    return $this->belongsTo(MenuItem::class, 'parent_menu_id', 'id')
        ->with('post');
}

public function childrenItems()
{
    return $this->hasMany(MenuItem::class, 'parent_menu_id', 'id')
        ->with('post');
}