CODEWITHSUNDEEP
laravelpivotrelation
Hardist
Hardist

Reputation: 1993

Laravel "Three-Way" Pivot Table?

OP:

I have a page called "Groceries" on my site. For this, I am using two tables:

Categories [$this->hasMany('App\Product')]
- id
- name

Products [$this->belongsTo('App\Category')]
- id
- category_id
- name

Simple Stuff. In my query I collect all categories with their corresponding products. groceries = Category::with('products')->get();

Then in my view I can access all categories with their products and list them nicely, all categories will be displayed once, and below the category name will be my products. All good. Example:

Category Alcohol
- Beer
- Wine
- Whisky

Category Bread
- Brown Bread
- White Bread

.. and so on.

And then it's where it gets tricky, at least for me. Up untill now I've been using the above method for one simple grocery list. But now I want to add another table, called "Groceries", in which I store grocery lists. Like so:

Groceries
- id
- name

With this, I think a pivot table would come in handy, but I am unsure about what to store in the pivot table and how to access all the data:

category_product?
grocery_product?
- id
- grocery_id
- product_id?
- category_id? I don't think I need to store this in the pivot table, it can be accessed through the Products model?
- number

I've been having a hard time to comprehend a "simple" pivot table, and now it gets even more tricky, with a "three-way" pivot table.

I wish to store the following in my pivot table:

The grocery id of the grocery list. But then I get stuck. What else should I store in the pivot table, in order for me to be able to still keep using the above method, but then with an extra parameter, like so:

$groceries = Grocery::find($id)->with('categories')->with('products'); ?

What I personally am thinking, is that I should use the following relation in my Groceries model?:

public function categories()
{
    return $this->hasMany('App\Category')->with('App\Product');
}

Any pointers in the right direction to help point my mind in the right direction would already help tons.

Edit:

Model User:

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

Model Category:

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

Model Products:

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

Model Groceries:

// No relations yet

Edit #2: On my groceries.index page I have a simple list of grocery lists for the current logged in user. They can create, edit or delete grocery lists.

Once the user clicks on "Create Grocery List", I show a view with all categories and their corresponding products, like so:

Category Alcohol
- [ ] Beer
- [ ] Wine
- [ ] Whisky

Category Bread
- [ ] Brown Bread
- [ ] White Bread

The [ ] stands for a checkbox. The user can select which products he or she wants to add to the list. Upon saving, I want to store the grocery list ID in a pivot table with the product ID's that were selected.

Then, on the edit form, I want to show the same view, but with the saved products already selected. So, I think the best way to accomplish this is to store the grocery ID in the pivot table, and it's selected products. Then, on the edit form, I simply check if the product ID exists, and if it does, the checkbox should be checked. But I am stuck with how the relations should be like and what I actually should be storing in my pivot table, product id's as I said or shouldn't I even use a pivot table at all?

Upvotes: 0

Views: 1575

Answers (1)

kalatabe
kalatabe

Reputation: 2989

Here's how I would go about it:

  1. A Product belongsTo a Category, in your case.
  2. Consequently, a Category hasMany Products.
  3. User hasMany "grocery lists", each one of which hasMany Products
  4. So a List also belongsTo one specific User
  5. Therefore, a Product will also belongsToMany Lists

This spares you the trouble of having to do a 3-way pivot, and Eloquent will let you conveniently walk through any and all relationships of any model that you might need when populating the check-box list in your description. Here's a simple schema to serve as an example - might need some tweaking!

I would, however, prefer Query Builder or plain raw DB queries instead of Eloquent methods for complex JOINs like these, as the Relationship Builder in Laravel actually does a WHERE id IN (1, 2, 3), which is a bit worse in terms of performance compared to a regular join. See these for reference:

https://laravel.com/docs/5.0/queries#raw-expressions

https://laravel.com/docs/5.1/queries#joins

Upvotes: 2

Related Questions