-1

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.

Corcunda
  • 35
  • 1
  • 6
  • Hi. It is not clear what you want. In particular "ORDER BY is_main DESC, so in the concatenated fields I would know that the first one will be the main category of the supplier". Please use enough words & sentences & references to parts of examples to be clear. Anyway that would be part the [mcve] you should be giving. – philipxy Nov 26 '18 at 05:56
  • Thanks. Unfortunately you are still not clear. If you know you can ORDER BY in a GROUP_CONCAT why don't you? Otherwise, explain what you mean--without misusing keywords that *don't do what you want*, though it can do something that reminds you of what you want--*that you haven't explained*. Also you have no example input & desired output & you don't refer to it. DId you read the link re [mcve]--*cut & paste & runnable with input*? You haven't given one. PS Please don't add EDITs/UPDATEs or cross out text. Just make your post the best possible. For old versions click on the "edited" link. – philipxy Nov 26 '18 at 10:28
  • This is a faq. Please always google error messages & many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & 'site:stackoverflow.com' & tags & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on [mcve]. – philipxy Nov 26 '18 at 10:32
  • @philipxy - I didn't know we could ORDER BY in a GROUP_CONCAT, that's the whole point of the question. Thanks to DancingFool, now I know. I will try to make a more reasonable question as you wish. – Corcunda Nov 26 '18 at 10:42
  • What is the point of your comment? (Rhetorical.) You seem to be trying to say that I should know that you "didn't know we could ORDER BY in a GROUP_CONCAT, that's the whole point of the question" but I *don't* know because it happens that the way you "misused keywords that *don't do what you want*" (instead of clearly saying what you mean) is consistent with you trying but failing to "ORDER BY in a GROUP_CONCAT". – philipxy Nov 27 '18 at 10:38

1 Answers1

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.

DancingFool
  • 1,247
  • 1
  • 8
  • 10