Clever Geek Handbook

Combine subheading result columns into one column, then ORDER BY

I would like to execute a 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 subquery can give more than 1 result, so I would like to concatenate the results into a single column file name.

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

+3


source to share


1 answer


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 filenames to be sorted in a comma separated list, you can use the order in newer versions of Postgres:



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 if I understand the requirement "and use it in the ORDER BY". If the column contains multiple files, how do you expect the overall result to be in the correct order? You will need to show us some rough output (based on some sample data).

+11


source






More articles:


All Articles