Merge Subquery results column into a single column then use in ORDER BY

Question

I would like to execute some query like this:

SELECT 
    folders.*, 
    (SELECT 
        files.filename 
     FROM 
        files 
     WHERE 
        files.folder_id = folders.id 
    ) 
    as files 
FROM 
        folders 
ORDER BY 
    files[column name of filename]

The sub-query may give more than 1 result, so I would like to join the results into a single column filename.

I am using Postgres. I added Rails as a tag because you might suggest some Rails magic that can handle this.

Answer 1

This will create a comma separated list of all files in the folder:

select folders.*,
       (SELECT string_agg(files.filename, ',')
        FROM files 
        WHERE files.folder_id = folders.id) as files
from folders

If you need the filenames to be sorted in the comma separated list, you can use an order by in newer Postgres versions:

select folders.*,
       (SELECT string_agg(files.filename, ',' order by files.filename)
        FROM files 
        WHERE files.folder_id = folders.id) as files
from folders

I'm not sure I understand the "and use it in ORDER BY" requirement though. If the column contains several files, how do you expect a proper order of the total result? You will need to show us some sample output (based on some sample data).