ORDER BY whether a GROUP_CONCAT contains a certain value?

Question

I have properties and I need to list the ones mapped as "Founding Members" first, and then alphabetically while the rest that aren't need to come afterwards and be listed alphabetically.

properties table:
------------------------
id  name  
1   Gaga Hotel
2   Foo Bar Resort


properties_features
------------------------

feature_id  property_id
1           1
2           1
2           2

features
------------------------
id  name
1   Founding Member
2   Glamping

Currently I am doing:

SELECT
p.name,
GROUP_CONCAT( pf.feature_id ) AS features

FROM properties AS p

LEFT JOIN properties_features AS pf
    ON p.id = pf.property_id

WHERE 1=1

GROUP BY p.id

But this obviously won't order them based on whether features aka GROUP_CONCAT ( pf.feature_id ) contains 1 which is "Founding Member".

How could I do this? Oh and, I can't really change the schema as I'd have to adjust pretty much the entire website as I'm making edits to an existing site.

Answer 1

Perhaps:

SELECT
p.name,
GROUP_CONCAT( pf.feature_id ) AS features

FROM properties AS p

LEFT JOIN properties_features AS pf
    ON p.id = pf.property_id

WHERE 1=1

GROUP BY p.id

ORDER BY 
    CASE WHEN LOCATE(',1,', CONCAT(',', features, ',')) <> 0 THEN 1 ELSE 2 END,
    p.name

Answer 2

If you need to sort features in the list, you can set ORDER BY inside GROUP_CONCAT:

 SELECT
    p.name, GROUP_CONCAT(pf.feature_id ORDER BY 
    CASE
     WHEN ft.name = `Founding Member` THEN ''
     ELSE ft.name
    END ) AS features
    FROM properties p 
    LEFT JOIN properties_features AS pf
    ON p.id = pf.property_id
    LEFT JOIN features ft ON (ft.id = pf.features)
    GROUP BY p.id

If you need to sort the output based on whether feature.name is Founding Member, you can do

 SELECT
p.name, GROUP_CONCAT(pf.feature_id) AS features, MIN(
CASE
     WHEN ft.name = `Founding Member` THEN 0
     ELSE 1
    END
) AS order_field
FROM properties p 
LEFT JOIN properties_features AS pf
ON p.id = pf.property_id
LEFT JOIN features ft ON (ft.id = pf.features)
GROUP BY p.id
ORDER BY order_field