Problem with multiselect fields and get data from DB

[A11]

Hello everyone,

I am currently using a multiselect field to filter my data, and I'm facing an issue with the SQL query. Here's how my query looks:

SELECT
  cian_id,
  warehouse_id,
  AVG(percentage_covers) AS percentage_covers,
  SUM(searches_count) AS searches_count,
  SUM(shows_count) AS shows_count,
  SUM(total_views) AS total_views,
  SUM(phone_shows) AS phone_shows,
  d.deal_type
FROM
  hz_pg_cian_analytics a
  JOIN hz_pg_deal_type d ON a.deal_type_id = d.id
WHERE
  review_date BETWEEN {{cianDateRange.value.start}} AND {{cianDateRange.value.end}}
  AND a.deal_type_id IN ({{ cianDealTypeMultiSelect.value }})
GROUP BY
  cian_id,
  warehouse_id,
  d.deal_type;

The challenge I'm facing is that {{cianDealTypeMultiSelect.value}} returns a list, for example, [1, 2], which is causing a syntax issue in my query. Specifically, the syntax becomes incorrect like this: AND a.deal_type_id IN ([1, 2]).

Could someone kindly guide me on how to address this problem? Your assistance would be greatly appreciated.

[ScottR]

@A11

Specifically, the syntax becomes incorrect like

Try:
a.deal_type_id = ANY([1, 2])

[A11]

I tried this AND a.deal_type_id = ANY(Array {{cianDealTypeMultiSelect.values}}) and it didn't work, however AND a.deal_type_id = ANY(Array[1,2]) works fine.
{{cianDealTypeMultiSelect.values}} returns an array with [ 1,2 ]

[ScottR]

cianDealTypeMultiSelect.value NOT cianDealTypeMultiSelect.values. extra s at the end as far as I can tell

[Tess]

@A11 let us know if the s was the issue! Otherwise, it might be worth checking to see if prepared statements were disabled on the resource which could also cause syntax errors for otherwise correct syntax

[A11]

"No, it didn't help. I have tried with and without 's'. I have also tried using prepared statements and still got the same error."

[A11]

This works for me
AND a.deal_type_id IN ( {{parseInt(cianDealTypeMultiSelect.value.join(","))}} )

[Tess]

Interesting Thanks for sharing your solution!