Using SQL query to select multiple select field

Technical Help
1

We use a self hosted Baserow and we query data directly to the postgreSQL. One problem we are facing is selecting Multiple-Select fields. I think it would be much easier if the multiple-select field is available in the main table as an array of select options ids (just like the single select field but as array of ids)
This would make things much easier and straight forward
Please correct me if am missing something

2

Hi @mahmouds12 , unfortunately I don’t think we’ll ever make this change as storing the multiple select field using a many-to-many intermediate table between the table and the select_option table is the much more correct/simple way to store this data in a relational database. It should be straightforward however to construct a SQL query to get the multiple select values/ids in a array like so:

select dt.id, jsonb_agg(ms.multipleselectfield1553selectoption_id)
from database_table_199 dt
         left join database_multipleselect_1553 ms on ms.table199model_id = dt.id
group by dt.id

Make sure to replace the 1553/199 with the id of the multiple select field and table id respectively.

Hope this helps!

1 Like
3

Alternatively you could also use in subquery to remove the need to group by in the outer expression:

select dt.id,
(select jsonb_agg(ms.multipleselectfield1553selectoption_id)
from database_table_199 inner_dt
         left join database_multipleselect_1553 ms
                   on ms.table199model_id = inner_dt.id
    where inner_dt.id = dt.id
         
) as select_ids
from database_table_199 dt