CODEWITHSUNDEEP
phpmysqllaraveleloquent
K1-Aria
K1-Aria

Reputation: 1146

Laravel Eloquent nested whereHas returns everything

i have this tables with relations:

-categories (has many products)
-products (has many features)
-features

im trying to get a special category using its slug, and get the products in category, and i also want to filter the products by some features using (id)

my code:

$category = Category::whereHas('products', function ($query) {
    $query->whereHas('features', function ($query2) {
        $query2->where('id', 21); // Example id (products where has features with '21' id)
    });
})->where('slug', 'category-slug')
  ->with('products:id,title', 'products.features')->get();

but the code returns that category with all products (with or without features with id 21)

what is the solution?

Upvotes: -1

Views: 264

Answers (2)

tahabas
tahabas

Reputation: 89

I think it is better to use a pivot table which stores product features.

+------------------+
| categories       |
| features         |
| feature_product  |
| products         |
+------------------+

Notice that pivot table name should be in alphabetical order prior to Laravel convention.

Columns of feature_product:

+------------+---------+------+-----+---------+----------------+
| Field      | Type    | Null | Key | Default | Extra          |
+------------+---------+------+-----+---------+----------------+
| id         | int(11) | NO   | PRI | NULL    | auto_increment |
| product_id | int(11) | YES  |     | NULL    |                |
| feature_id | int(11) | YES  |     | NULL    |                |
+------------+---------+------+-----+---------+----------------+

Now add relation to your product Model:

public function features() {
    return $this->belongsToMany(Feature::class);
}

The query:

$products = Product::select()
        ->join('feature_product', function ($join) {
            $join->on('feature_product.product_id', 'products.id')
                ->where('feature_id', 21);
        })
        ->join('categories', function ($join) {
            $join->on('products.category_id', 'categories.id')
                ->where('slug', 'slug-name');
        }) 
        ->get();

Now get features per product:

foreach ($products as $product) {
         $product->features;
}

Upvotes: 0

Kevin Bui
Kevin Bui

Reputation: 3045

You might want to try the dot notation:

$category = Category::whereHas('products.features', function ($query) {
        $query->whereKey(21);
    })
    ->where('slug', 'category-slug')
    ->with('products:id,title', 'products.features')->get();

Upvotes: 1

Related Questions