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

Question

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),
);

create table files_closure (
    ancestor bigint NOT NULL,
    decedant bigint NOT NULL,
    "depth" int NOT NULL,
    CONSTRAINT files_closure_pk PRIMARY KEY (ancestor,decedant),
    CONSTRAINT files_closure_ancestor_fk FOREIGN KEY (ancestor) REFERENCES files(id),
    CONSTRAINT files_closure_decedant_fk FOREIGN KEY (decedant) REFERENCES files(id),
);

And each table has its own Model as well:

Files

class Files extends Model
{
  protected $table='files';
  public $timestamps = false;
  
  public function parentFolder():HasOne
  {
     return $this->hasOne(self::class,'id','folder');
  }

  public function files(): HasMany
  {
    return $this->hasMany(self::class, 'folder');
  }
}

And for the closure table:

class FilesClosure extends Model
{
  protected $table='files_closure';
  public $timestamps = false;
  
  public function ancestor():HasOne
  {
     return $this->hasOne(Files::class,'ancestor','id');
  }

  
  public function decedant():HasOne
  {
     return $this->hasOne(Files::class,'ancestor','id');
  }
}

And I want to create a json structure as fast as possible (in the least ammout of execution time without causing database overhead):

{
  files: [
      {
        name:"My Music",
        type: "FOLDER",
        files: [
           {
             name: "Halford",
             type: "FOLDER",
             files: [
               {
                 name: "Locked-and-loaded.mp3",
                 type: "FILE"
               },
               {
                 name: "Another Song.mp3",
                 type: "FILE"
               }
             ]
           }
        ]
      }
  ]
}

If I avoided using the closure table I could retrieve the data as:

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

But that results having multiple queries and have slow response time especially on large datasets. Therefore I want to use the closure table but I am stuck on how I can do this.

Answer 1

Check below where I modified the Laravel Files model to efficiently handle hierarchical data using a closure table. The approach involves a single query that joins the files table with the files_closure table to retrieve the hierarchical data, respecting the folder-file structure. This data is then processed in PHP to construct a tree-like structure. A static method getHierarchicalData in the Files model fetches the data and another set of methods buildTree and buildBranch are used to organize this data into a nested tree format, reflecting the folder-file hierarchy. This method minimizes the number of database queries, especially beneficial for large datasets, and the final tree structure is converted into a JSON format, suitable for the required output.

class Files extends Model
{
    protected $table = 'files';
    public $timestamps = false;

    // New method to retrieve hierarchical data
    public static function getHierarchicalData()
    {
        // Get hierarchical data
        $data = DB::table('files as f')
            ->join('files_closure as fc', 'f.id', '=', 'fc.decedant')
            ->select('f.*', 'fc.ancestor', 'fc.depth')
            ->orderBy('fc.ancestor')
            ->orderBy('fc.depth')
            ->get();

        return self::buildTree($data);
    }

    protected static function buildTree($data)
    {
        $items = [];
        foreach ($data as $item) {
            $items[$item->ancestor][] = $item;
        }

        $tree = self::buildBranch($items, null);
        return $tree;
    }

    protected static function buildBranch(&$items, $parentId)
    {
        $branch = [];
        foreach ($items[$parentId] as $item) {
            if (isset($items[$item->id])) {
                $item->files = self::buildBranch($items, $item->id);
            } else {
                $item->files = [];
            }
            $branch[] = $item;
        }
        return $branch;
    }
}

In the controller, you call:

$json = json_encode(Files::getHierarchicalData());

Hydrate some models using the query

public static function getHierarchicalData()
{
    // Get hierarchical data
    $data = DB::table('files as f')
        ->join('files_closure as fc', 'f.id', '=', 'fc.decedant')
        ->select('f.*', 'fc.ancestor', 'fc.depth')
        ->orderBy('fc.ancestor')
        ->orderBy('fc.depth')
        ->get();

    // Hydrate Files models from the raw data
    $models = Files::hydrate($data->toArray());

    return self::buildTree($models);
}