Using an array from an input component to filter SQL Query

Queries and Resources
,
1

Hello community! I am stuck with a filter in a query...

I have the following tables:
rates: a list of all rates applicable to all clients
clients: list of clients
rules: a list of rules to change specific rates for specific clients
tags: a list of possible tags for the rates
tag_rate: a table that relates tags to rates.

I am showing the rates for a specific client, with a pick variable. This works.

What I am trying to add is a filter so that IF I add tags, only the rates with those tags will show. I am doing this in the last WHERE. ChatGPT suggested this code for me and it seems to work If I replace the component variable for just hard coded input. The logic seems to work, but the input isnt recognized by the SQL query as the correct format. I have tried to transform it to text, but still it isn't working.

SELECT
  r.name,
  COALESCE(r.amount + sum(ru.rate_add_amount), r.amount) AS amount,
  COALESCE(r.min + sum(ru.min_add_amount), r.min) AS min,
  r.notes,
  string_agg(s.name, ', ') AS tag,
  array_agg(s.id) AS tag_ids
FROM
  rates AS r
LEFT JOIN (
  SELECT *
  FROM rules
  WHERE client_id = {{ clientSelect.value }}
  ) AS ru
ON
  r.id = ru.rate_id
LEFT JOIN (
  SELECT t.name, tr.rate_id, t.id
  FROM tag_rate AS tr
  LEFT JOIN tags AS t ON t.id = tr.tag_id
) AS s ON r.id = s.rate_id
GROUP BY r.id
HAVING ARRAY {{filters.value}} <@ ARRAY_AGG(s.id);
2

Hey @ggallese!

What kind of SQL resource are you using? The type of SQL can change how arrays are handled pretty significantly (see the SQL cheatsheet for some examples).

3

Hello Kabirdas,

Thanks for the answer. That looks like a useful resource. I am using the Retool database, which is in PostrgreSQL, right?

I recently changed the query to the following:

SELECT
  r.id,
  r.name,
  MAX(COALESCE(r.amount + ru.rate_add_amount, r.amount)) AS amount,
  MAX(COALESCE(r.min + ru.min_add_amount, r.min)) AS min,
  MAX(r.max) AS max,
  r.unit,
  r.notes,
  r.tags AS tags
FROM
  rates AS r
LEFT JOIN (
  SELECT *
  FROM rules
  WHERE client_id = {{ clientSelect.value }}
) AS ru ON r.id = ru.rate_id
WHERE (ARRAY_LENGTH(ARRAY[{{filters.value}}]::VARCHAR[], 1) IS NULL) OR {{"'"+JSON.stringify(filters.value.map(item => item.replace(/'/g, '')))+"'"}}::jsonb @> tags::jsonb
GROUP BY r.id
ORDER BY r.id

But as you see, I need to make some very strange things with the filters.value to use it in an array inside the query. Is there any better practice?

4

Hey @ggallese, that's right! RetoolDB is a PostgreSQL database.

Can you try something like the following?

WHERE
  {{ _.isEmpty(filters.value}} 
OR
  {{ JSON.stringify(filters.value) }} @> tags::jsonb