Populate multiselect component with unique values from database

App Building
1

I'd like to pre-populate the Multiselct component with values already in the database, but unique values only, in alphabetical order.

Example query is:

SELECT "NewGenre" FROM "MAIN" WHERE "NewGenre" != ''

Which results in:

image

How can I get that mapped into the Multiselect like so:

image

Thanks in advance!

2

Hi, welcome to the forum

It would be good to see what you've already tried here and what wouldn't work for you as there's several ways to solve this particular issue.
One option would be to select only the distinct genres in the query and also sort it in the query.
Another option is to manipulate the array of genres once you've selected them in your query. ie something like

// flatten the array of arrays into a single list of all genres
genreListArray.flat();
// order them in alpha sequence
genreListArray.sort();
// use the lodash _.sortedUniq function to remove duplicates
_.sortedUniq(genreListArray);

Or in a neat one liner:
_.sortedUniq(genreListArray.flat().sort())

3

Hi Dave,

Your second point there is what I was trying to achieve and after much Postgresql googling I finally have a query that does what I need:

SELECT ARRAY(
  SELECT DISTINCT UNNEST("Genre"::text[])
  AS Genre 
  FROM "MAIN" ORDER BY Genre
)

and then to populate the Multiselect with the results I used this for the mapped value:

{{ getGenres.rawData.array[0] }}

All seems to be working well so I'll leave this here for the next person that stumbles across it!

1 Like