CODEWITHSUNDEEP
phpmysqllaravellaravel-5
Simon Zohrabyan
Simon Zohrabyan

Reputation: 103

How to make smart filter with product attributes in Laravel

I'm making e-commerce with Laravel 5.8 and now I'm trying to make smart filtering.

I have 4 tables (products, attributes, attribute_values, product_attributes).

products

id      name         direction_id
----------------------------------
1       Product 1    1
2       Product 2    2
3       Product 3    1
4       Product 4    1

attributes

id     name
-------------------
1      HDD
2      RAM

attribute_values

id  attribute_id    value
------------------------------
1   1               1TB
2   1               500GB
3   2               8GB
4   2               32GB

product_attributes

id   product_id   value_id
---------------------------
1    1            1
2    1            3
3    2            1
4    2            4
5    3            1
6    4            4

Now I want to display only one direction's products' attributes and values. E.g. If direction_id = 1 I want to show only HDD (1TB), RAM(8GB, 32GB).

Any ideas how to make this kind of filter and also filter the products. I only came up with this solution of showing attributes and values.

For attributes

select distinct a.*
from products p
left join product_attributes pa
on pa.product_id = p.id
left join attribute_values v
on v.id = pa.value_id 
left join attributes a
on a.id = v.attribute_id
where p.direction_id = 1

For values

select distinct v.*
from products p
left join product_attributes pa
on pa.product_id = p.id
left join attribute_values v
on v.id = pa.value_id 
where p.direction_id = 1

Upvotes: 1

Views: 1107

Answers (1)

Lexxusss
Lexxusss

Reputation: 552

Solution in Laravel will implement relations and will look like this:

// class Product extends Model ('products' table)
public function attributeValues()
{
    return $this->belongsToMany(AttributeValue::class, 'product_attributes', 'product_id', 'value_id');
}


// class AttributeValue extends Model ('attribute_values' table)
public function attribute()
{
    return $this->belongsTo(Attribute::class, 'attribute_id', 'id');
}





// retrieve engine for direction '1':
$attributesValues = [];
Product::query()
->where(['direction' => 1])
->each(function(Product $product) use (&$attributesValues) {
    $product->attributeValues()->each(function(AttributeValue $attributeValue) use (&$attributesValues) {
        $attributesValues[$attributeValue->attribute->name][] = $attributeValue->value;
    });
});

dd($attributesValues);

Upvotes: 2

Related Questions