ORDER BY within GROUP_CONCAT and GROUP BY with a joined table

Question

Scenario:

I have three tables. One table for supplier, another for categories and the last one is a indexer table.

Every supplier can choose multiple categories that will be registered in the indexer table.

The field parent_id on indexer_table is to register the id of the supplier and the child_id is to register the id of the category. There is another field called is_main, to check if it is the main category of the supplier.

I'm working with this query and works really well.

SELECT 
    t1.id, t1.name, t1.company, t1.description,
    t2.categories, t2.categories_id 
        FROM supplier_table t1 
        LEFT JOIN (
            SELECT 
                tin2.parent_id, tin2.child_id, tin2.is_main,  
                GROUP_CONCAT(tin1.id) AS categories_id, 
                GROUP_CONCAT(tin1.title) AS categories FROM 
                (SELECT id, title FROM table_categories WHERE status = 1) tin1 
                    JOIN table_indexer tin2 ON tin2.child_id = tin1.id GROUP BY tin2.parent_id 
        ) t2 ON t1.id = t2.parent_id  
            WHERE t1.id IN (:id)

Basically, this query returns all info that I need from the supplier. The ids and names of the categories are concatenated on categories and categories_id field respectively and it is grouped by the supplier id (parent_id), giving me this output:

Supplier Object
(
    [id] => 1
    [name] => Supplier Name
    [company] => SUpplier Company Name
    [description] => Supplier Description Text.
    [categories] => Category Example 1,Main Category,Category Example 2
    [categories_id] => 6,9,5
)

What I need is to ORDER BY is_main DESC the contents of GROUP_CONCAT. In that way, I would know that the first ocurrence is the main category of the supplier.

Supplier Object
(
    [id] => 1
    [name] => Supplier Name
    [company] => SUpplier Company Name
    [description] => Supplier Description Text.
    [categories] => Main Category,Category Example 1,Category Example 2
    [categories_id] => 9,6,5
)

I've tried to place ORDER BY after GROUP BY and some other places but it didn't work. Any help would be appreciated, thank you.

Answer 1

If I understand you, you want the contents of the Group_Concat results ordered, not the rows they are in. To do that, you need to add the order as part of the Group_Concat. Manual here.

So instead of

GROUP_CONCAT(tin1.title) AS categories

you want something like

GROUP_CONCAT(tin1.title ORDER BY tin2.is_main DESC) AS categories

If you want the ids concat to be in the same order as the title one, you will need to put the same sort order on both concats, and make sure they give a guaranteed order, probably by extending the sort order to include the ID, like this

GROUP_CONCAT(tin1.id ORDER BY tin2.is_main DESC, Tin2.ID) AS categories_id,
GROUP_CONCAT(tin1.title ORDER BY tin2.is_main DESC, Tin2.ID) AS categories

You could use title as the second order, as long as you can guarantee the titles will always be different.