I have the following table in mysql:

Schema::create('tb__menu', function (Blueprint $table) {
    $table->increments('id');
    $table->unsignedInteger('idmenu')->nullable();
    $table->string('name', 250);
    $table->foreign('idmenu')->references('id')->on('tb__menu');
    $table->engine = "InnoDB";
});

Here some records:

id   |   idmenu |  name
1        NULL      root
2        1         folder 1
3        1         folder 2
4        2         folter 1 A
5        2         folder 1 B
6        2         folder 1 C
7        3         folder 2 A
8        4         folder 1 A A

This is the model of the table:

class Menu extends Model
{
    protected $table = 'tb__menu';

    protected $fillable = [
        'id',
        'idmenu',
        'name',
    ];

    protected $casts = [
        'id' => 'integer',
        'idmenu' => 'integer',
        'name' => 'string'
    ];

    public function menu() {
        return $this->belongsTo(Menu::class, 'idmenu');
    }

    public function menus() {
        return $this->hasMany(Menu::class, 'idmenu');
    }
}

And this is the controller:

class MenuTC extends Controller
{
    public function index()
    {
        return /* NEED */
    }
}

How can I get a json structure of the menu table records?, for example:

[
    {
        "id": 1,
        "name": "root",
        "childrens": [
            {
                "id": 2,
                "name": "folder 1",
                "childrens": [
                    {
                        "id": 4,
                        "name": "folder 1 A",
                        "childrens": [
                            {
                                "id": 8,
                                "name": "folder 1 A A"
                            }
                        ]
                    },
                    {
                        "id": 5,
                        "name": "folder 1 B"
                    },
                    {
                        "id": 6,
                        "name": "folder 1 C"
                    }
                ]
            },
            {
                "id": 3,
                "name": "folder 2",
                "childrens": [
                    {
                        "id": 7,
                        "name": "folder 2 A"
                    }
                ]
            }
        ]
    }
]

I would appreciate any help you can give me.

PD: In SQL server, I could do this with a recursive query, but in Laravel I have no idea how to do it.

CodePudding user response：

create a recursive relationship like below

public function childMenu(){
   return $this->hasMany(Menu::class,'idmenu','id');
}

public function recursiveChildMenu(){
 return $this->childMenu()->with('recursiveChildMenu');
}

then in controller

Menu::with('recursiveChildMenu')->get();