Laravel is not possible if the request

Question

I am working on a filter for some products. I have most of the work, but I encountered an error with an impossible offer where.

The table contains several rows for one product, and I try to match several criteria for each product, which leads to its failure.

If you have an opinion on this, or perhaps it can be fixed, I would really appreciate it.

The database table is as follows:

    --------------------------------------------
    | id | FilterKey | filterValue | product_id |
    --------------------------------------------
    | 1 | Color | Gunmetal | 1 |
    | 2 | Color | Silver | 1 |
    | 3 | Size | 750cc | 1 |
    | 4 | Size | 1000cc | 1 |
    | 5 | | Color | Red | 2 |
    | 6 | Color | Blue | 2 |
    | 7 | Size | 750cc | 2 |
    | 8 | Size | 1000cc | 2 |
    --------------------------------------------

And the filter is as follows:

public function scopeFilterProduct($query, $filters)
{
    $this->filters = $filters;
    if (count ($this->filters) === 1 && isset($this->filters[0]))
    {
        return $query;
    }
    $query->join('product_filters', 'products.id', '=', 'product_filters.product_id')->Where(function($query){
        foreach ($this->filters as $filter => $vals)
        {
            $this->filter = $filter;
            $this->vals = $vals;
            $query->Where(function ($query){

                $query->Where('filterKey', $this->filter); 

                $query->Where(function($query){
                    foreach ($this->vals as $val){

                        $query->orWhere('filterValue', $val); 
                    }
                    $this->vals = null;
                });

            });
            $this->filter = null;
        };
    }); 
    return $query;
}

Then the following SQL statement is output:

select
  distinct
    `products`.`id`
  , `product_id`
from
 `products`
inner join
 `product_filters`
on
 `products`.`id` = `product_filters`.`product_id`
where 
     (
         (`filterKey` = 'Colour' and (`filterValue` = 'gunmetal'))
       and
         (`filterKey` = 'Size' and (`filterValue` = '750cc'))
     )
     and
       `products`.`deleted_at` is null

If selected, as shown in the screenshot, only “product one” should be present on the page.

Answer 1

The volume that you added is, in my opinion, incorrect. In my opinion, even your database structure is incorrect. Here is how I would structure it:

Filter table

. , Colour, Size .. :

-----------------
|id | name      |
-----------------
|1  | Colour    |
|2  | Size      |
-----------------

, :

class Filter extends Model
{
    protected $fillable = ['id', 'name'];

    public function products()
    {
        return $this->belongsToMany(Product::class, 'products_filters');          
    } 
}

:

class Product extends Model
{
    public function filters()
    {
        return $this->belongsToMany(Filter::class, 'products_filters');          
    } 
}

products_filters

, :

--------------------------------------------
|id | filter_id | filterValue | product_id |
--------------------------------------------
|1  | 1         | Gunmetal    | 1          |
|2  | 1         | Silver      | 1          |
|3  | 2         | 750cc       | 1          |
|4  | 2         | 1000cc      | 1          |
|5  | 1         | Red         | 2          |
|6  | 1         | Blue        | 2          |
|7  | 2         | 750cc       | 2          |
|8  | 2         | 1000cc      | 2          |
--------------------------------------------

, . .

Unqiue .

$ids = [];
$products = new \Illuminate\Support\Collection();

foreach($filters as $filter) {
    foreach($filter->products as $product) {
        if(!in_array($product->id, $ids)) {
            $ids[] = $product->id;
            $products->push($product);
        }
    }
}

return view('results', compact('products'));

, :

@foreach($products as $product)
// Your product block HTML
@endforeach