Laravel Eloquent - Many to Many, Through, Where

Question

I have three tables: products, categories and product_has_category

The categories table has a "type" field, which is either "gender" or "garment". So a product has many categories and a category has many products.

The trickier part comes in with how I have two different types of categories (i.e. gender and garment). A product can have only one "gender" category and only one "garment" category.

products table:

---------------
| id | style  |
---------------
| 1  | Style 1|
---------------
| 2  | Style 2|
---------------

categories table:

----------------------------
| id | type    | name      |
----------------------------
| 1  | gender  | men's     |
----------------------------
| 2  | gender  | women's   |
----------------------------
| 3  | garment | crew neck |
----------------------------
| 4  | garment | v neck    |
----------------------------
| 5  | garment | tank top  |
----------------------------

product_has_category table:

----------------------------
| product_id | category_id |
----------------------------
| 1          | 1           |
----------------------------
| 1          | 3           |
----------------------------
| 2          | 2           |
----------------------------
| 2          | 5           |
----------------------------

So, with the above data, we have:

Style 1 is a men's crew neck, and Style 2 is a women's tank top.

I'd like to be able to retrieve products in this type of manner:

// returns Style 1, men's, crew neck
$product = Product::with(['gender', 'garment'])->find(1);

// returns Style 2, women's, tank top
$product = Product::with(['gender', 'garment'])->find(2);

I think I understand how I can set up a standard many-to-many relationship in my models using a belongsToMany() method setting the junction table as 'product_has_category'.

In my category model I have the following relationship:

class Category extends Model
{

    public function products()
    {
        return $this->belongsToMany('App\Product', 'product_has_category', 'category_id', 'product_id');
    }

}

But I'm not sure how to set the relationships in the product model to get the categories by a given category type. Here's what I have in my product model, which makes sense in a way, but laravel is throwing an error about the category.type being an unknown column.

class Product extends Model
{

    public function gender()
    {
        return $this->belongsToMany('App\Category', 'product_has_category', 'product_id', 'category_id')->where('type', '=', 'gender');
    }

    public function garment()
    {
        return $this->belongsToMany('App\Category', 'product_has_category', 'product_id', 'category_id')->where('type', '=', 'garment');
    }
}

Can anyone point me in the right direction for how to set up these types of data relationships?

Have you read the documentation on the topic? If so why would you name your pivot table whatever_has_something, use recommendation: To define this relationship, three database tables are needed: users, roles, and role_user (last one is alphabetically ordered). Are you sure you need "category" system and not "tag" system? — Kyslik
– Kyslik, Commented Mar 20, 2018 at 19:37
Thanks Kyslik! Yes, I've read the documentation. I'm working with an older database and it's undesirable to change the table names. In the documentation there's the section about how laravel assumes an alphabetical pivot table name, but that we are free to override that convention and customize the pivot table name in the belongsToMany() relationship. If it's considered a "category" system or a "tag" system still doesn't address how there would be different "types" of categories or different "types" of tags, I don't think. But maybe I'm misunderstanding your question/suggestion. — CICSolutions
– CICSolutions, Commented Mar 20, 2018 at 19:52
This will help you in a long run stackoverflow.com/questions/26178315/… — Kyslik
– Kyslik, Commented Mar 20, 2018 at 20:32

DevK · Accepted Answer · 2018-03-20 20:30:52Z

2

I'm assuming your relationships work as intended.

Here's your problem:

public function gender()
{
    return $this->belongsToMany('App\Category', 'product_has_category', 'product_id', 'category_id')
    ->where('category.type', '=', 'gender'); // Here
}

public function garment()
{
    return $this->belongsToMany('App\Category', 'product_has_category', 'product_id', 'category_id')
    ->where('category.type', '=', 'garment'); // And here
}

When you chain query off of a relationship (in your case ->where('category.type'...), you're working on a related model's query. And as such you need to remove the category. part, since you're already working on a category query.

Like this:

public function gender()
{
    return $this->belongsToMany('App\Category', 'product_has_category', 'product_id', 'category_id')
        ->where('type', '=', 'gender'); // Removed 'category.'
}

public function garment()
{
    return $this->belongsToMany('App\Category', 'product_has_category', 'product_id', 'category_id')
        ->where('type', '=', 'garment'); // Removed 'category.'
}

Now if you call Product::with(['gender', 'garment'])->first() you will have these 2 categories separated.

answered Mar 20, 2018 at 20:30

DevK

10k2 gold badges33 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

CICSolutions Over a year ago

thanks devk! That sure was a silly mistake, unworthy of a post. You nailed it. Thanks so much for your help!

Collectives™ on Stack Overflow

Laravel Eloquent - Many to Many, Through, Where

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related