How can I change a group_concat column that’s left joined with another table to empty string instead of null when there are no matching records?

I’ve been trying to get a MySQL query that uses a left join combined with a group_concat to get the Amazon S3 paths of all of the profile photos for a given user. What I want is for the photo_keys aggregate column that’s the result of the group_concat() function to become an empty string instead of null when the user has no profile pics.

There are lots of similar questions in which a column’s value is dependent on some condition (i.e. with case/when statements or if statements), but they don’t have the complicating factor of having a group_concat in combination with the left join.

What I’m starting with (working code, but not desired behavior => photo_keys is null instead of empty string when user has no profile pics)

SELECT u.*, GROUP_CONCAT(pps.photo_key ORDER BY pps.is_primary DESC) as photo_keys
        FROM users u 
        LEFT JOIN profile_images pps ON u.id=pps.user_id
        WHERE u.id = "555555555"
        GROUP BY u.id`

What I’ve tried (this is more like pseudocode)

SELECT u.*, GROUP_CONCAT(pps.photo_key ORDER BY pps.is_primary DESC) as photo_keys, COUNT(*) as numImages
        CASE 
              WHEN numImages = 0 THEN photo_keys = ""
        END
        FROM users u 
        LEFT JOIN profile_images pps ON u.id=pps.user_id
        WHERE u.id = "5555555555"
        GROUP BY u.id`

Thanks so much for your help. -Jay

Answer

Wrap the result with IFNULL will do.

SELECT u.*, IFNULL(GROUP_CONCAT(pps.photo_key ORDER BY pps.is_primary DESC), '') as photo_keys
FROM users u 
LEFT JOIN profile_images pps ON u.id=pps.user_id
WHERE u.id = "555555555"
GROUP BY u.id`

Advertisement

Answer