How I can traverse a tree structure using closure tables in order to create a json at Eloquent models?

Dimitrios Desyllas - Jan 10 -

- Dev Community

At this question:

Jan 10 '24 Comments: 2 Answers: 1

In my application I have the following tables:

CREATE TABLE files (
    id bigint IDENTITY(1,1) NOT NULL
    name nvarchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    folder bigint NULL,
    [type] nvarchar(10) NOT NULL CHECK ([type] IN ('FILE', 'FOLDER')) DEFAULT 'FOLDER',
    CONSTRAINT PK__3213E83FDB19A582 PRIMARY KEY (id),
    CONSTRAINT folder_fk FOREIGN KEY (folder) REFERENCES files(id),
);

…

Open Full Question

I emulate a filesystem in my database. And I want to generate a Json by traversing the nessesary models. So far I use this approach:

$json = Files::with('parentFolder','files')->whereNull('folder')->get()->toJson();

But my gut instinct says that this approach is slow and I am trying to use a closure table. But I am kinda stuck....

I mean I made the nessesary tables and models but Idk how to use the closure in order to gereneate my Json.

Can I have some help upon that?