MySQL - Using Order By result created by a subquery group_concat or join issue

This is a query I've been puzzling over for quite some time, I've never been able to get it to work quite right and after about 40 hours of pondering I've gotten to this point.

Setup

For the example issue we have 2 tables, one being...

field_site_id             field_sitename           field_admins
1                         Some Site                1,
2                         Other Site               1,2,

And the other is admins like...

field_user_id             field_firstname          field_lastname
1                         Joe                      Bloggs
2                         Barry                    Wills

Now all this query is designed to do is the following:

• List all sites in the database
• Usi开发者_StackOverflowng a JOIN and FIND_IN_SET to pull each admin
  • And GROUP_CONCAT(field_firstname, ' ', field_lastname) with a GROUP BY to build a field with the real user names.
• Also allow HAVING to filter on the custom result to narrow the results down further.

All this part works perfectly fine.

What I can't work out how to achieve is to sort the results by the GROUP_CONCAT result, I imagine this is being the ORDER BY works before the concat function therefore the data doesn't exist to order by it, so what would the alternative be?

Code examples:

SELECT *

GROUP_CONCAT(DISTINCT field_firstname, ' ', field_lastname ORDER BY field_lastname SEPARATOR ', ') AS field_admins_fullname,

FROM `table_sites`
LEFT JOIN `table_admins` ON FIND_IN_SET( `table_admins`.`field_user_id`, `table_sites`.`field_site_id` ) > 0
GROUP BY field_site_id

I also tried a query that used a subquery to gather the group_concat result as below...

( SELECT GROUP_CONCAT(field_firstname, ' ', field_lastname ORDER BY field_lastname ASC SEPARATOR ', ') FROM table_admins 
WHERE FIND_IN_SET( `table_admins`.`field_user_id`, `table_sites`.`field_admins` ) > 0
) AS field_admins_fullname

Conclusion

Either way attempting to ORDER BY field_admins_fullname will not create the correct results, it won't error out but assume that's because the given ORDER BY is blank so it just does whatever it wants.

Any suggestions would be welcome, if this is just not possible, what would be another recommend index methodology?

Two things I see wrong:

1st, is the JOIN. It should be using s.field_admins and not field_site_id :

    ON FIND_IN_SET( a.field_user_id, s.field_admins ) > 0

2nd, you should use the CONCAT() function (to conactenate fields from the same row) inside the GROUP_CONCAT().

Try this:

SELECT s.field_site_id
     , s.field_sitename
     , GROUP_CONCAT( CONCAT(a.field_firstname, ' ', a.field_lastname)
                     ORDER BY a.field_lastname ASC
                     SEPARATOR ', '
                   )
       AS field_admins_fullname
FROM table_sites s
  LEFT JOIN table_admins a
    ON FIND_IN_SET( a.field_user_id, s.field_admins ) > 0
GROUP BY s.field_site_id

Friendly advice:

Don't use         Do use
------------      --------
table_sites       site
table_admins      admin

field_site_id     site_id 
field_sitename    sitename
field_admins      admins

But what should really be stressed, is your setup. Having fields that have comma separated values lead to this kind of horrible queries that use FIND_IN_SET() for joins and GROUP_CONCAT() for showing results. Horrible to see, difficult to maintain and most important, very, very slow as no index can be used.

You should have something like this instead:

Setup suggestion

Table:  site

site_id      sitename       
1            Some Site      
2            Other Site     


Table:  site_admin

site_id      admin_id      
1            1
2            1 
2            2   


Table:  admin

user_id      firstname      lastname
1            Joe            Bloggs
2            Barry          Wills

I think you need to repeat the complex CONCAT statement you are selecting within the ORDER BY.

So your order by would be more like...

ORDER BY (GROUP_CONCAT(DISTINCT field_firstname, ' ',
    field_lastname ORDER BY field_lastname SEPARATOR ', ')) ASC

I have not tried this but I had a similar issue which this seemed to solve but it was much simpler without the DISTINCT etc.

wrong group by, try this ?

GROUP BY field_site_id