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?

Answer 1

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.