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

postgresql, ruby-on-rails-3.2

Solution

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).

Problem

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.

Original source