List Tree Categories in Laravel 5.1

Question

I’m starting with Laravel 5.1 and I’m a little lost yet.

I need to list categories, subcategories and "sub-subcategories".

Table:

Categorias
------------------------
id            | int
nome          | varchar
categoria_pai | int

Dice:

id  | nome         | categoria_pai
----------------------------------
1   | Informática  | NULL
2   | Mouse        | 1
3   | Sem fio      | 2
4   | Eletrônicos  | NULL

Model:

class Categoria extends Model
{
    protected $table = 'categorias';
    protected $fillable = ['nome', 'descricao', 'categoria_pai'];

    public function produtos() {
      $this->belongsToMany('App\Produto');
    }

    public function categoriaPai()
    {
        return $this->belongsTo('App\Categoria');
    }

    public function categoriaFilho()
    {
        return $this->hasMany('App\Categoria');
    }
}

Controller:

Method that mounts select when adding another category. It works, but I don’t know how to filter it to make optgroup and separate.

public function create()
{
    $categorias = Categoria::all()->lists('nome', 'id');
    return view('categorias.adicionar', compact('categorias'));
}

I have no idea how to start, I’ve looked for similar things but I haven’t found.

With pure PHP I’ve already made, but using the Laravel, I’m cracking my skull.

Answer 1

In case I need to list all and there may be infinite levels

So to list all subcategories of a category independent of the amount of levels. You can do it this way:

mysql> select * from  categoria;
+----+------+--------------+
| id | nome | categoria_id |
+----+------+--------------+
|  1 | cat1 |         NULL |
|  2 | cat2 |            1 |
|  3 | cat3 |            2 |
|  4 | cat4 |            2 |
+----+------+--------------+




class Categoria extends Model{
  ...
   public function subcategorias()
    {
        return $this->hasMany(Categoria::class, 'categoria_id','id');
    }

    public function allSubcategorias()
    {
        return $this->subcategorias()->with('allSubcategorias');
    }
}

Search all subcategories this way:

$categoria = Categoria::where('id', 1)->with('allSubcategorias')->first();
return $categoria;

Upshot:

    
{
    id: 1,
    nome: "cat1",
    categoria_id: null,
    all_subcategorias: [
        {
            id: 2,
            nome: "cat2",
            categoria_id: 1,
            all_subcategorias: [
                {
                    id: 3,
                    nome: "cat3",
                    categoria_id: 2,
                    all_subcategorias: [ ]
                },
                {
                    id: 4,
                    nome: "cat4",
                    categoria_id: 2,
                    all_subcategorias: [ ]
                }
            ]
        }
    ]
}

SQL generated:

    select * from `categoria` where `id` = 1 limit 1;
    select * from `categoria` where `categoria`.`categoria_id` in 1;
    select * from `categoria` where `categoria`.`categoria_id` in 2;
    select * from `categoria` where `categoria`.`categoria_id` in (3, 4);

Answer 2

In the relationship lacked only specify the relationship column:

class Categoria {

    public function categoriaPai() {
        return $this->belongsTo('App\Categoria','categoria_pai');
    }

    public function categoriaFilho() {
        return $this->hasMany('App\Categoria','categoria_pai');
    }

}

And to capture from a specific record, you could do it this way:

$categoria = App\Categoria::find(1);
$subcategorias = $categoria->categoriaPai()->get();

foreach ($subcategorias as $sub) {
    $subsubcategorias = $sub->categoriaPai()->get();
}