我已经使用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小提琴在这里尝试一下。
如果你想达到你的期望值,你必须使用GROUP BY
。对于CodeIgniter,您可以在此处找到它,您可以在本部分中找到指南页面$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();
}
正如您在此处看到的,grouping by
我要打印的列名,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
语句对其进行设置。
这些例子会给你一个好主意,祝你好运。
编辑
这应该有效。
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
,以便它根据需要提供uniq数据。
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小提琴
我只会执行三个简单的查询。获取产品。获取属性。获取维度。 然后将属性和维度映射到相应的产品。 这就是任何ORM执行紧急加载的方式。
我不熟悉代码点火器,但我认为它应该看起来像这样:
$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);
}
现在,您可以在一个嵌套良好的结构中获取所有数据。有了echo json_encode($products, JSON_PRETTY_PRINT)
您将获得:
{
"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"
}
]
}
}
您可以在视图层或导出代码中遍历它,并以所需的任何方式输出。
更新
在MySQL 5.7 +中,您可以获得与具有单个查询的单个JSON字符串相同的结果:
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;
数据库小提琴
或者,也许您想要这样"简单"的东西:
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;
这将返回:
| 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 |
数据库小提琴
但是,如果您需要与问题中完全相同的结果,那么您可以尝试以下结果:
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;
结果:
| 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) |
数据库小提琴
您可以使用MySQL函数GROUP_CONCAT来获得所需的结果:
http://sqlfiddle.com/#!9/c51040/3
你需要这样做 lile
function getProductList() {
return $this->db->select('p.name as product_name
, GROUP_CONCAT(a.name SEPARATOR ",") as attribute_name
, min(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("p.name");
->get();
}
使用子查询尝试此操作。 代码点火器代码 :
$this->db->select("select p.name as pname, p.description as pdesc, t2.aname, t2.adesc,t3.dname,t3.dvalue");
$this->db->from("products p");
$this->db->join("(select pa.product_id,GROUP_CONCAT(a.name) as aname, GROUP_CONCAT(a.description) as adesc from product_attributes as pa
left join attributes as a on a.id = pa.attribute_id
group by pa.product_id) t2","t2.product_id=p.id","left");
$this->db->join("(select group_concat(name) as dname,group_concat(value) as dvalue,pd.product_id from product_dimensions pd
left join dimensions d on d.id = pd.dimension_id
group by pd.product_id) t3","t3 on t3.product_id = p.id","left");
$this->db->group_by("p.id");
$this->db->get()->result_array();
MySql 查询 :
select p.name as pname, p.description as pdesc, t2.aname, t2.adesc,t3.dname,t3.dvalue
from products as p
left join (select pa.product_id,GROUP_CONCAT(a.name) as aname, GROUP_CONCAT(a.description) as adesc from product_attributes as pa
left join attributes as a on a.id = pa.attribute_id
group by pa.product_id) t2 on t2.product_id=p.id
left join
(select group_concat(name) as dname,group_concat(value) as dvalue,pd.product_id from product_dimensions pd
left join dimensions d on d.id = pd.dimension_id
group by pd.product_id) t3 on t3.product_id = p.id
group by p.id
点击这里查看结果