Pass a custom component's variable value to the query

Hi all,
I am struggling with passing the value from the component to the data query. I have created a custom component that is used to filter a list of elements fetched by data query (just as the Subcategories filter on this page: https://www.trustpilot.com/categories/animals_pets ) . I have defined an isSelected state variable in the component to define whether my subcategory filter is selected. I guess that I need to set up a condition in the query with Postgres IN operator to pull all companies that match any of the subcategories selected. But I don’t know how to pass an array of selected subcategories to the query. I would be very grateful for help. Below I have added the link to the video where I describe my problem.

Thank you for your help in advance. Cheers.

https://streamable.com/dx3lce?src=player-page-share

Hello @maciek_ziolkowski ! You can select the option Custom read query and, instead of using the IN operator which does not allow safe value substitution in postgres, use the ANY operator.

You query would look like this:

SELECT *
FROM table
WHERE column = ANY( {{ array }}::type[] )

in which {{ array }} is the dynamic value corresponding to your isSelected state.
LMK if this solves the issue for you!

1 Like

Hi @icaro_guerra . Thanks for the tip, it led me to the right direction. However, I haven’t solved my issue yet.

So right now I am able to pass the list of subcategories that are only selected. Each of the badges has two state variables defined with external ready only access - is selected (type: boolean) and value (type:string)

In my page data query called fetchCompanies I check which of the badges are currently selected ( item.isSelected == true ) and retrieve only the name of value of the badge (subcategory name).

Now, the issue is that I want to show all companies if no subcategory badge is selected. And I don’t know how to do this :slight_smile:

Ok, now I am a step further and know how to write a condition to either return all companies or those that match the subcategory filter selection. Here’s the code snippet I wrote for that.

$state.categoriesBadge.filter((item) => item.isSelected == true).length > 0 ?
`subcategory IN (
    ${
        $state.categoriesBadge.filter(
            (item) => item.isSelected == true
            ).map(
                (item) => `'` + item.value + `'`
            )
    }
)` :
`true`

But even though it shows the correct response in code editor, when I save it the response of the whole query is as follows:

Does Plasmic pass a string instead of a query part and this is why it throws an error?

Sorry for the delay. We do pass a string instead of a query part for security measures. The only place we allow dynamic values is for values only, not for identifiers such as column names and table names.

Could you try this query instead?

SELECT *
FROM table
WHERE table.a = ANY ({{ list }}) 
OR {{ list.length === 0 }}
1 Like

Hi @icaro_guerra , this works perfectly. Thanks.