Laravel n级别

Question

I have
Created two tables:
1. categories

Schema::create('categories', function (Blueprint $table) {
        $table->increments('id');
        $table->string('title');            
        $table->timestamps();
});

2. category_closure

Schema::create('category_closure', function (Blueprint $table) {
        $table->increments('id');
        $table->integer('ancestor', false, true);
        $table->integer('descendant', false, true);
        $table->integer('depth', false, true);

        $table->foreign('ancestor')->references('id')->on('categories')->onDelete('cascade');
        $table->foreign('descendant')->references('id')->on('categories')->onDelete('cascade');
        $table->timestamps();
    });

In category table I have my categories data and relations in category_closure

categories                  category_closure
+----+-----------------+   +----+----------+------------+-------+  
| id | title           |   | id | ancestor | descendant | depth |  
+----+-----------------+   +----+----------+------------+-------+  
| 1  | Real Estate     |   | 1  |     1    |      1     |   0   |  
| 2  | Vehicles        |   | 2  |     2    |      2     |   0   |  
| 3  | Clothing        |   | 3  |     3    |      3     |   0   |  
| 4  | Apartments      |   | 4  |     1    |      4     |   1   |  
| 5  | Used Cars       |   | 5  |     2    |      5     |   1   |  
| 6  | Women Clothing  |   | 6  |     3    |      6     |   1   |  
| 7  | Women T-shirt   |   | 7  |     6    |      7     |   2   |
| 8  | Men's Clothing  |   | 8  |     3    |      8     |   1   |    

Now for example category Clothing
3 - 3 returns clothing - clothing 3 - 6 returns clothing - women's clothing 3 - 8 returns clothing - men's clothing etc.

Question

I can't get my head around how to define Eloquent relation in my Models to get categories with descendants.

Category Model

class Category extends Model
{
    protected $table = 'categories';    
    protected $hidden = ['id'];
    protected $fillable = ['title'];

    public function nodes()
    {
        return $this->belongsTo(CategoryClosure::class, 'ancestor');
    }
    public function leafs()
    {
        return $this->belongsTo(CategoryClosure::class, 'descendant');
    }    
}

And CategoryClosure

class CategoryClosure extends Model
{
    protected $table = 'category_closure';
    protected $fillable = ['ancestor', 'descendant', 'depth'];

    public function categories()
    {
        return $this->hasMany(Category::class, 'ancestor');
    }
    public function subcategories()
    {
        return $this->hasMany(Category::class, 'descendant');
    }
}

How to get tree from closure table like this?

Update So far what I have achieved in mysql level

SELECT * FROM categories JOIN trees ON (categories.id = trees.descendant) WHERE trees.ancestor = 1;

this gives me this output if category 1 is selected

    +----+-------------+-------------+----------+------------+------------+---------------------+---------------------+----+----------+------------+-------+---------------------+---------------------+
    | id | title       | slug        | position | real_depth | deleted_at | created_at          | updated_at          | id | ancestor | descendant | depth | created_at          | updated_at          |
    +----+-------------+-------------+----------+------------+------------+---------------------+---------------------+----+----------+------------+-------+---------------------+---------------------+
    |  1 | Real Estate | real_estate |        0 |          0 | NULL       | 2016-04-04 15:00:00 | 2016-04-04 15:00:00 |  1 |        1 |          1 |     0 | 2016-04-04 20:00:00 | 2016-04-04 20:00:00 |
    |  4 | Apartments  | apartments  |        0 |          1 | NULL       | 2016-04-04 15:00:00 | 2016-04-04 15:00:00 |  2 |        1 |          4 |     1 | 2016-04-04 20:00:00 | 2016-04-04 20:00:00 |
    |  5 | Houses      | houses      |        1 |          1 | NULL       | 2016-04-04 15:00:00 | 2016-04-04 15:00:00 |  3 |        1 |          5 |     1 | 2016-04-04 20:00:00 | 2016-04-04 20:00:00 |
    +----+-------------+-------------+----------+------------+------------+---------------------+---------------------+----+----------+------------+-------+---------------------+---------------------+

So I know it's working, now how to transfer this to view...

Answer 1

I suggest you to use this package: https://packagist.org/packages/franzose/closure-table so you won't need to reinvent the wheel. It provides two methods to get the whole tree:

$tree = Category::getTree();

or

$treeByCondition = Category::getTreeWhere('position', '>=', 1);