Concatenate non empty CONCAT_GROUP to parent column in MySQL

Question

I need to get the list of categories from store_cat, with the child COUNT from store_item (amount of products) and GROUP_CONCAT from store_cat_attributes (list of attributes). The thing is, using CONCAT function I need to attach the GROUP_CONCAT value with name column in the parent table (store_cat), and that's where it gets tricky.

This works fine:

SELECT
    store_cat.id_cat AS id,
    store_cat.name AS name,
    GROUP_CONCAT(store_cat_attribute.name SEPARATOR ", ") AS attributes,
    COUNT(store_item.id_item) AS products,
    store_cat.flg_public AS flg_public
FROM store_cat
LEFT JOIN store_item ON store_item.id_cat = store_cat.id_cat
LEFT JOIN store_cat_attribute ON store_cat_attribute.id_cat = store_cat.id_cat
WHERE store_cat.id_store = 1
GROUP BY store_cat.id_cat
ORDER BY name

But this is what I would actually need. The problem is that, when I execute this query, the store_cat.name value shows an empty value when there are no attributes:

SELECT
    store_cat.id_cat AS id,
    CONCAT(store_cat.name, " (", GROUP_CONCAT(store_cat_attribute.name SEPARATOR ", "), ")") AS name,
    COUNT(store_item.id_item) AS products,
    store_cat.flg_public AS flg_public
FROM store_cat
LEFT JOIN store_item ON store_item.id_cat = store_cat.id_cat
LEFT JOIN store_cat_attribute ON store_cat_attribute.id_cat = store_cat.id_cat
WHERE store_cat.id_store = 1
GROUP BY store_cat.id_cat ORDER BY name

Basically, the idea is that the store_cat.name column should contain the attributes list with CONCAT and GROUP_CONCAT, just like this:

• Comidas
• Correas (S, M, L, XL)
• Juguetes
• Medicinas

Here's the current SQLfiddle. By the way, something is off with the attributes order in the current GROUP_CONCAT. It is displaying (XL, S, M, L) instead of (S, M, L, XL).

Problems to solve:

1. Use GROUP_CONCAT to concatenate the attributes to the category name only when there are attributes.

2. Use the store_cat_attributes.position to set the order for the GROUP_CONCAT values.

Any ideas? Thanks!

Answer 1

The following expression should return the results that you expect :

CONCAT(
    store_cat.name,
    IFNULL(
        CONCAT(
            ' (', 
            GROUP_CONCAT(
                store_cat_attribute.name 
                ORDER BY store_cat_attribute.position 
                SEPARATOR ', '
             ),
            ')'
        ),
        ''
    )
) AS name

Basically, this just tries to GROUP_CONCAT() the attributes, and if the result is NULL then it turns the attribute list to an empty string. Please note that GROUP_CONCAT support ORDER BY.

I also fixed the GROUP BY clause : in non-ancient versions of MySQL, all non-aggregared columns must appear in the where clause (you are missing store_cat.name).

Demo on DB Fiddle with your sample data :

SELECT 
    store_cat.id_cat AS id,
    CONCAT(
        store_cat.name,
        IFNULL(
            CONCAT(
                ' (', 
                GROUP_CONCAT(store_cat_attribute.name ORDER BY store_cat_attribute.position SEPARATOR ', '),
                ')'
            ),
            ''
        )
    ) AS name, 

    COUNT(store_item.id_item) AS products, 
    store_cat.flg_public AS flg_public 
FROM 
    store_cat 
    LEFT JOIN store_item ON store_item.id_cat = store_cat.id_cat 
    LEFT JOIN store_cat_attribute ON store_cat_attribute.id_cat = store_cat.id_cat 
WHERE store_cat.id_store = 1 
GROUP BY store_cat.id_cat, store_cat.name
ORDER BY name;

| id  | flg_public | name                  | products |
| --- | ---------- | --------------------- | -------- |
| 3   | 1          | Comidas               | 0        |
| 2   | 1          | Correas (S, M, L, XL) | 4        |
| 1   | 1          | Juguetes              | 2        |
| 4   |            | Medicinas             | 0        |