Thursday, 24 October 2019

Retrieve single row as a query result for an EAV model in CodeIgniter

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.



from Retrieve single row as a query result for an EAV model in CodeIgniter

No comments:

Post a Comment