How to handle showing multiple categories at one table in Laravel-CodePudding

I'm working on an Online Store project using Laravel 8 and in this project there are 4 types of categories:

Main Category (such as supermaket)
Superior Category (such as dairy & ...)
Secondary Category (such as cheese & ...)
Secondary Sub Category (such as cream cheese & ...)

And for each Category I have made a separated Model and table.

And all tables goes with same fields:

So this is the capture of superior_categories with parent id of 1 which is the id of supermarket main category.

And this is the One To Many relationship for the MainCategory Model:

class MainCategory extends Model
{
    use HasFactory;
    protected $fillable = ['name','short_name','description','pciture'];
    protected $table = 'main_categories';

    public function superiors()
    {
        return $this->hasMany(SuperiorCategory::class);
    }
}

Now I need to show ALL the categories at one table, so my question is should I retrieve all the categories separately like this:

public function index()
    {
        $mainCategory = MainCategory::all();
        $superiorCategory = SuperiorCategory::all();
        $cat = Cat::all();
        $subcat = Subcat::all();

        return view('admin.categories.index', compact('mainCategory','superiorCategory','cat','subcat'));
    }

Or there is another better way for doing this.

In fact, how to handle showing multiple categories at one table properly

CodePudding user response：

this is a bad structure for multi level categories in your db, you just need a table for categories and store parent category or other level in a column like category_id enter image description here

CodePudding user response：

There's no clean way to do this, here is however a dirty way:

class MainCategory extends Model
{
    use HasFactory;
    protected $fillable = ['name','short_name','description','pciture'];
    protected $table = 'main_categories';
    protected $with = [ 'parent' ];

    public function parent()
    {
        return $this->hasMany(SuperiorCategory::class);
    }

    public function getAncestorsAttribute() {
        return collect($this->parent)->concat($this->parent?->ancestors??[]);
    }
}

Then if you do $model->ancestors you should get them. However this will have to do one query per ancestor every time you load one of the MainCategory models which may end up being very expensive.

This assumes that your SuperiorCategory model has a similar arrangement