I have 4 tables similar to this:

items (id)

subcategories (id | category_id)

item_subcategory (item_id | subcategory_id) --pivot table

categories (id)

---

The items and subcategories are connected to the pivot table with many to many relationship. How can I get all the items under each categories? Below is an example.

• Category 1
  • Item 1
  • Item 2
  • Item 3
• Category 2
  • Item 4
  • Item 5
• Category 3
  • Item 2
  • Item 3

CodePudding user response：

There can be 3 ways we could use from my perspective, I'll share all.

1. Linking Relations: You can link relations on top of other relations eg.

Category::with(['subcategories' => function ($query) {
   $query->whereHas('items');
}])->get();

It will get all the subcategories first, then the related items something like that =>

[{
    "id": 1,
    "title": "Category 1",
    "subcategories": [{
        "id": 1,
        "category_id": 1,
        "title": "Subcategory 1",
        "items": [{
            "id": 1,
            "title": "Item 1",
            "pivot": {
                "subcategory_id": 1,
                "item_id": 1
            }
        }]
    }]
}]

2. Add category id into a pivot table and make new relation eg:

public function items()
{
   return $this->belongsToMany(Item::class, ItemSubcategory::class);
}

It will allow you to directly fetch products without loading subcategories first, something like that.

[{
    "id": 1,
    "title": "Category 1",
    "items": [{
        "id": 1,
        "title": "Item 1",
        "pivot": {
            "category_id": 1,
            "item_id": 1
        }
    }]
}]

3. Using hasManyThrough, you can create a relation by depending on other classes if your item table contains a subcategory id.

    public function items()
    {
        return $this->hasManyThrough(Item::class, ItemSubcategory::class, null, 'id', null, 'item_id');
    }

CodePudding user response：

I don't understand completely but maybe this will be a solution for you, in your model

public function Categories()
    {
        return $this->hasMany(Category::class,'id', 'categoryID');
    }

CodePudding user response：

You don't need four tables for this, it can be simplified to just three tables.

Note that I am making assumptions and simplifying things here. I don't know all of the table columns you have in place. You can add/remove whatever you want.

Database table migrations

Category table - should be self explanitory, the main thing here is that it has a foreign key column (category_id) that references itself (another category) for managing relationships to subcateogires.

Schema::create('categories', function (Blueprint $table) {
    $table->id();
    $table->unsignedBigInteger('category_id')->nullable();
    $table->timestamps();
});

Item table - should be self explanitory

Schema::create('items', function (Blueprint $table) {
    $table->id();
    $table->timestamps();
    $table->string('name');
});

Category and Item pivot table - manage relationships between Items and Categories

Schema::create('category_item', function (Blueprint $table) {
    $table->id();
    $table->timestamps();
    $table->foreignId('category_id')->constrained();
    $table->foreignId('item_id')->constrained();
});

Model relationships

class Category extends Model
{
    use HasFactory;

    public function items()
    {
        return $this->belongsToMany(Item::class);
    }
}

class Item extends Model
{
    use HasFactory;

    public function category()
    {
        return $this->belongsToMany(Category::class);
    }
}

Example routes

Route::get('/items/{item}', function(Request $request, Item $item) {
    dd($item->category);
});

Route::get('/categories/{category}', function (Request $request, Category $category) {
    dd($category->items);
});

If you navigate to '/categories/1' (for example) you should get a dump of all the items associated with the category id specified in the URL.