I have implemented an EAV model using MySQL (phpmyadmin) for an e-commerce website developed with CodeIgniter Framework (PHP). The EAV Model goes this way:
Table: Products
id | name | description
-------------------------
1 | prod_1 | lorem
2 | prod_2 | ipsum
Table: Attributes
id | name | description
-------------------------
1 | attr_1 | dolor
2 | attr_2 | sit
3 | attr_3 | amet
Table: Product_Attributes
id | prod_id | attr_id
-------------------------
1 | 1 | 1
2 | 1 | 2
3 | 2 | 1
4 | 2 | 2
5 | 2 | 3
I have generated a result using multiple joins, which looks as follows:
product_name | attribute_name | product_description
---------------------------------------------------
prod_1 | attr_1 | lorem
prod_1 | attr_2 | lorem
prod_2 | attr_1 | ipsum
prod_2 | attr_2 | ipsum
prod_2 | attr_3 | ipsum
The query used for above result is as follows:
function getProductList() {
return $this->db->select('p.name as product_name, a.name as attribute_name, p.description as product_description')
->from('products as p')
->join('product_attributes as pa', 'pa.prod_id = p.id', 'LEFT')
->join('attributes as a', 'a.id = pa.attr_id', 'LEFT')
->get();
}
But, what I want as a result of the query is as follows:
product_name | attribute_name | product_description
-------------------------------------------------------------
prod_1 | (attr_1, attr_2) | lorem
prod_2 | (attr_1, attr_2, attr_3) | ipsum
The drawback of the current query result is that I have to perform a nested loop on the result to display a list of products and their attributes, which affects the performance. I'm open for any suggestion(s) to improve the performance of the query or its result.
--EDIT--
I also have other tables linked with the Products table. Say, for example, there's an additional table as follows:
Table: Dimensions
id | name | value
-----------------
1 | length | 20
2 | breadth| 15
3 | height | 20
Table: Product_Dimensions
id | prod_id | dim_id
-------------------------
1 | 1 | 1
2 | 1 | 2
3 | 1 | 3
4 | 2 | 1
5 | 2 | 2
Thus, the expected output modified as follows:
product_name | attribute_name | product_description| dimension_name | dimension_value
----------------------------------------------------------------------------------------------------------
prod_1 | (attr_1, attr_2) | lorem | (length, breadth, height) | (20, 15, 20)*
prod_2 | (attr_1, attr_2, attr_3) | ipsum | (length, breadth) | (20, 15)
But, the obtained output is as follows:
product_name | attribute_name | product_description| dimension_name | dimension_value
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
prod_1 | (attr_1, attr_2, attr_1, attr_2, attr_1, attr_2) | lorem | (length, breadth, height, length, breadth, height) | (20, 15, 20, 20, 15, 20)
prod_2 | (attr_1, attr_2, attr_3, attr_1, attr_2, attr_3) | ipsum | (length, breadth, length, breadth, length, breadth) | (20, 15, 20, 15, 20, 15)
--EDIT--
When used DISTINCT under GROUP_BY, the output gets modified as follows:
product_name | attribute_name | product_description| dimension_name | dimension_value
----------------------------------------------------------------------------------------------------------
prod_1 | (attr_1, attr_2) | lorem | (length, breadth, height) | (20, 15)*
prod_2 | (attr_1, attr_2, attr_3) | ipsum | (length, breadth) | (20, 15)
*You can see the difference between the expected and obtained output. The intended duplicates also get erased by using DISTINCT.
SQL Fiddle to try your hands on here.
I would just execute three simple queries. Get products. Get attributes. Get dimensions. Then map attributes and dimensions to corresponding products. This is how probably any ORM is performing eager loading.
I'm not familiar with codeigniter, but I think it should look something like this:
$query = $this->db->get('products');
$products = [];
foreach ($query->result() as $row) {
// init empty arrays for attributes & dimensions
$row->attributes = [];
$row->dimensions = [];
// should be indexed by id, so we can find id later to map attributes & dimensions
$products[$row->id] = $row;
}
$attributes = $this->db
->select('pa.product_id, a.name')
->from('product_attributes as pa')
->join('attributes as a', 'a.id = pa.attribute_id')
->get();
$dimensions = $this->db
->select('pd.product_id, d.name, d.value')
->from('product_dimensions as pd')
->join('dimensions as d', 'd.id = pd.dimension_id')
->get();
foreach ($attributes as $attr) {
$products[$attr->product_id]->attributes[] = $attr->name;
}
foreach ($dimensions as $dim) {
$products[$dim->product_id]->dimensions[] = $dim;
unset($dim->product_id);
}
Now you get all your data in a nicely nested structure. With echo json_encode($products, JSON_PRETTY_PRINT) you would get:
{
"1": {
"id": 1,
"name": "Product_1",
"description": "Lorem",
"attributes": [
"Attribute_1",
"Attribute_2"
],
"dimensions": [
{
"name": "length",
"value": "15"
},
{
"name": "breadth",
"value": "25"
},
{
"name": "height",
"value": "15"
}
]
},
"2": {
"id": 2,
"name": "Product_2",
"description": "Ipsum",
"attributes": [
"Attribute_1",
"Attribute_2",
"Attribute_3"
],
"dimensions": [
{
"name": "length",
"value": "15"
},
{
"name": "breadth",
"value": "25"
}
]
}
}
You can traverse it in the view layer or your export code, and output in any way you need.
In MySQL 5.7+ you can get the same result as a single JSON string with a single query:
select json_arrayagg(json_object(
'id', p.id,
'name', p.name,
'description', p.description,
'attributes', (
select json_arrayagg(a.name)
from product_attributes pa
join attributes a on a.id = pa.attribute_id
where pa.product_id = p.id
),
'dimensions', (
select json_arrayagg(json_object(
'name', d.name,
'value', d.value
))
from product_dimensions pd
join dimensions d on d.id = pd.dimension_id
where pd.product_id = p.id
)
))
from products p;
Or maybe you want something "simple" like this:
select p.*, (
select group_concat(a.name order by a.id separator ', ')
from product_attributes pa
join attributes a on a.id = pa.attribute_id
where pa.product_id = p.id
) as attributes, (
select group_concat(d.name, ': ', d.value order by d.id separator ', ')
from product_dimensions pd
join dimensions d on d.id = pd.dimension_id
where pd.product_id = p.id
) as dimensions
from products p;
which will return:
| id | name | description | attributes | dimensions |
| --- | --------- | ----------- | ------------------------------------- | ----------------------------------- |
| 1 | Product_1 | Lorem | Attribute_1, Attribute_2 | length: 15, breadth: 25, height: 15 |
| 2 | Product_2 | Ipsum | Attribute_1, Attribute_2, Attribute_3 | length: 15, breadth: 25 |
But if you need the result exactly as in the question, then you can try this one:
select
p.name as product_name,
(
select concat('(', group_concat(a.name order by a.id separator ', '), ')')
from product_attributes pa
join attributes a on a.id = pa.attribute_id
where pa.product_id = p.id
) as attribute_name,
concat('(', group_concat(d.name order by d.id separator ', '), ')') as dimension_name,
concat('(', group_concat(d.value order by d.id separator ', '), ')') as dimension_value,
p.description as product_description
from products p
left join product_dimensions pd on pd.product_id = p.id
left join dimensions d on d.id = pd.dimension_id
group by p.id;
Result:
| product_name | product_description | attribute_name | dimension_name | dimension_value |
| ------------ | ------------------- | --------------------------------------- | ------------------------- | --------------- |
| Product_1 | Lorem | (Attribute_1, Attribute_2) | (length, breadth, height) | (15, 25, 15) |
| Product_2 | Ipsum | (Attribute_1, Attribute_2, Attribute_3) | (length, breadth) | (15, 25) |