php - 在 CodeIgniter 中检索单行作为 EAV 模型的查询结果

Question

我已经使用 MySQL (phpmyadmin) 为使用 CodeIgniter Framework (PHP) 开发的电子商务网站实现了 EAV 模型。EAV 模型是这样的:

表:产品

id | name   | description
-------------------------
1  | prod_1 | lorem
2  | prod_2 | ipsum

表:属性

id | name   | description
-------------------------
1  | attr_1 | dolor
2  | attr_2 | sit
3  | attr_3 | amet

表:Product_Attributes

id | prod_id | attr_id
-------------------------
1  | 1       | 1
2  | 1       | 2
3  | 2       | 1
4  | 2       | 2
5  | 2       | 3

我使用多个连接生成了一个结果，如下所示:

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

用于上述结果的查询如下:

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();
}

但是，我想要的查询结果如下:

product_name | attribute_name           | product_description
-------------------------------------------------------------
prod_1       | (attr_1, attr_2)         | lorem
prod_2       | (attr_1, attr_2, attr_3) | ipsum

当前查询结果的缺点是我必须对结果进行嵌套循环以显示产品及其属性的列表，这影响了性能。我愿意接受任何改进查询或其结果性能的建议。

--编辑--

我还有其他表与 Products 表链接。比如说，有一个额外的表如下:

表格:维度

id | name   | value
-----------------
1  | length | 20
2  | breadth| 15
3  | height | 20

表:Product_Dimensions

id | prod_id | dim_id
-------------------------
1  | 1       | 1
2  | 1       | 2
3  | 1       | 3
4  | 2       | 1
5  | 2       | 2

因此，预期输出修改如下:

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)

但是，得到的输出如下:

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)

--编辑--

当在 GROUP_BY 下使用 DISTINCT 时，输出被修改如下:

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)

*您可以看到预期输出和获得输出之间的差异。使用 DISTINCT 也可以删除预期的重复项。

SQL Fiddle 试试 here .

Answer 1

如果你想达到你的期望值，你必须使用GROUP BY .对于 CodeIgniter，您可以在这里找到它，您可以找到它 Guide page在这部分 $this->db->group_by().

function getProductList() {
  return $this->db->select('p.name as product_name, concat(\'( \', GROUP_CONCAT(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')
                  ->group_by(array('p.name', 'p.description'))
                  ->get();
}

正如您在此处看到的，分组依据 我希望打印的列名，concat 您希望检索的列名。希望这会有所帮助，祝你好运。

已编辑

如果您想在多个列上使用 concat 或任何聚合函数，您必须按照 GROUP_CONCAT(a.name) 进行操作，如下所示。

function getProductList() {
      return $this->db->select('p.name as product_name, 
                GROUP_CONCAT(a.name) as attribute_name, 
                GROUP_CONCAT(a.name) as attribute_name_2, 
                GROUP_CONCAT(a.name) as attribute_name_3, 
                p.description as product_description')
                  ->...
                  ->group_by(array('p.name', 'p.description'))
                  ->get();
}

请注意，如果您想要concat 列，您还没有使用group by 语句设置它。

These examples会给你一个好主意，祝你好运。

已编辑

这应该有效。

function getProductList() {
      return $this->db->select('p.name as product_name, 
                GROUP_CONCAT(a.name) as attribute_name, 
                p.description as product_description, 
                GROUP_CONCAT(d.name) as dimension_name, 
                GROUP_CONCAT(d.value) as dimension_value')
                  ->...
                  ->group_by(array('p.name', 'p.description'))
                  ->get();
}

已编辑

您可以在 group_concat 中使用 distinct，这样它就可以根据需要提供唯一的数据。

function getProductList() {
      return $this->db->select('p.name as product_name, 
                GROUP_CONCAT(DISTINCT a.name) as attribute_name, 
                p.description as product_description, 
                GROUP_CONCAT(DISTINCT d.name) as dimension_name, 
                GROUP_CONCAT(DISTINCT d.value) as dimension_value')
                  ->...
                  ->group_by(array('p.name', 'p.description'))
                  ->get();
}

已编辑

正如我在评论部分告诉你的，如果你想做你想做的事，你必须使用子查询，这就是你可以做到的。

function getProductList() {
  return $this->db->select('pname, pdesc, aname, adesc, group_concat(dname), group_concat(dvalue)')
                  ->from('( select
                        p.name as pname,
                        p.description as pdesc,
                        GROUP_CONCAT(DISTINCT a.name) as aname,
                        GROUP_CONCAT(DISTINCT a.description) as adesc,
                        d.name as dname, 
                        d.value as dvalue

                        from products as p
                        left join product_attributes as pa on pa.product_id = p.id
                        left join attributes as a on a.id = pa.attribute_id
                        left join product_dimensions as pd on pd.product_id = p.id
                        left join dimensions as d on d.id = pd.dimension_id

                        group by p.id, p.description, d.name) as d' )
                  ->group_by(array('pname', 'pdesc', 'aname', 'adesc'))
                  ->get();
}

sql fiddle