php - parent id for category and subcategory

Solution:

To get subcategories

$sub_categories = Category::whereNotNull('parent_id')->get();

To get sub-categories with parent

$sub_categories_with_parent = Category::with('parent')->whereNotNull('parent_id')->get();

To fetch categories

$categories = Category::whereNull('parent_id')->get();

To fetch categories with children

$categories_with_childern = Category::with('children')->whereNull('parent_id')->get();

You might have to redefine your relations as well:

public function parent()
{
    return $this->belongsTo(Category::class);
}

public function children()
{
    return $this->hasMany(Category::class , 'parent_id');
}

In migration define relation as well

$table->foreign('parent_id')->references('id')->on('categories')->onUpdate('cascade')->onDelete('cascade');

Make parent field nullable

$table->unsignedBigInteger('parent_id')->nullable()->default(123);

Solution:

The line down below is incorrect. Because with() is used to get relational data and parent_id is not a relation name.

$parent_id = Category::with('parent_id')->get();

If your route contains the id or slug of the category, you can use it, but I think it doesn't, because your index function doesn't accept any route parameter. So I assume you are trying to fetch all categories and subcategories. But in this case, the second line of the index function doesn't make sense at all.

If you want to all categories:

$categories = Category::where('parent_id', null)->with('children')->get();

I see you use 123 for top-level categories, and it looks high enough. But nullable is a better practice for that purpose.

If you need a specific category and its subcategories:

// web.php
Route::get('category/{slug}', [CategoryController::class, 'index']);

// CategoryConteroller.php
public function index($slug)
{
    $category = Category::where('slug', $slug)->with('children')->get();
}