我正确使用GROUP_CONCAT吗?
问题描述:
我正在选择属性并将它们连接到映射表,并将它们映射到过滤器(如位置,目标和属性类型)。我正确使用GROUP_CONCAT吗?
我的目标是抓住所有的属性,然后将它们加入到表中,然后基本上获取显示所有位置,属性所属目标和属性类型本身的数据。
这里是我的查询:
SELECT p.slug AS property_slug,
p.name AS property_name,
p.founder AS founder,
IF (p.display_city != '', display_city, city) AS city,
d.name AS state,
type
GROUP_CONCAT(CONVERT(subcategories_id, CHAR(8))) AS foo,
GROUP_CONCAT(CONVERT(categories_id, CHAR(8))) AS bah
FROM properties AS p
LEFT JOIN destinations AS d ON d.id = p.state
LEFT JOIN regions AS r ON d.region_id = r.id
LEFT JOIN properties_subcategories AS sc ON p.id = sc.properties_id
LEFT JOIN categories_subcategories AS c ON c.subcategory_id = sc.subcategories_id
WHERE 1 = 1
AND p.is_active = 1
GROUP BY p.id
之前,我做了GROUP BY
和GROUP_CONCAT
我的数据是这样的:
id name type category_id subcategory_id state
--------------------------------------------------------------------------
1 The Hilton Hotel 1 1 2 7
1 The Hilton Hotel 1 1 3 7
1 The BlaBla Resort 2 2 5 7
的GROUP BY
和GROUP_CONCAT
成为后...
id name type category_id subcategory_id state
--------------------------------------------------------------------------
1 The Hilton Hotel 1 1, 1 2, 3 7
1 The BlaBla Resort 2 1 3 7
这是gra的首选方式一口气将所有可能的财产映射到GROUP_CONCAT
变成这样的CSV?
使用这些数据,我可以呈现类似...
<div class="property" categories="1" subcategories="2,3">
<h2>{property_name}</h2>
<span>{property_location}</span>
</div>
然后使用JavaScript显示,如果基于/隐藏在具有说,一个subcategory="2"
属性会隐藏每一个锚用户点击.property
它的subcategories
属性值中没有2
。
答
我相信你想是这样的:
CREATE TABLE property (id INT NOT NULL PRIMARY KEY, name TEXT);
INSERT
INTO property
VALUES
(1, 'Hilton'),
(2, 'Astoria');
CREATE TABLE category (id INT NOT NULL PRIMARY KEY, property INT NOT NULL);
INSERT
INTO category
VALUES
(1, 1),
(2, 1),
(3, 2);
CREATE TABLE subcategory (id INT NOT NULL PRIMARY KEY, category INT NOT NULL);
INSERT
INTO subcategory
VALUES
(1, 1),
(2, 1),
(3, 2),
(5, 3),
(6, 3),
(7, 3);
SELECT id, name,
CONCAT(
'{',
(
SELECT GROUP_CONCAT(
'"', c.id, '": '
'[',
(
SELECT GROUP_CONCAT(sc.id ORDER BY sc.id SEPARATOR ', ')
FROM subcategory sc
WHERE sc.category = c.id
),
']' ORDER BY c.id SEPARATOR ', ')
FROM category c
WHERE c.property = p.id
), '}')
FROM property p;
这将输出这样的:
1 Hilton {"1": [1, 2], "2": [3]}
2 Astoria {"3": [5, 6, 7]}
的最后一个字段是正确形成JSON
哪一类的ID映射到子类别的ID的数组。
答
您应该添加DISTINCT,并可能ORDER BY:
GROUP_CONCAT(DISTINCT CONVERT(subcategories_id, CHAR(8))
ORDER BY subcategories_id) AS foo,
GROUP_CONCAT(DISTINCT CONVERT(categories_id, CHAR(8))
ORDER BY categories_id) AS bah
这是“去标准化”如果你想这样称呼它。如果这是用于渲染的最佳表示是另一个问题,我认为它很好。有人可能会说这是黑客攻击,但我猜这不算太坏。
顺便说一句,逗号似乎在“类型”后缺少。