What is the syntax for filtering a query in Transformer

💬 Queries and Resources
,
1

New in JS + Transformer in Retool

  1. I need to filter my query.data by the values of three components on my canvas.
  2. Instead of using the WHERE clause in SQL, I would like to do the filtering in the Transformer
  3. What should the JS syntax in the Transformer be for the following criteria

SELECT * FROM table WHERE
customer_name like {{'%' + customer_input.value + '%'}}
AND booking_id LIKE {{booking_input.value + '%'}}
AND booking_status IN UNNEST({{status_check_group.value}})

I am thinking something like
const data = {{query.data}}
return data.filter(some syntax that data.customer_name like {{'%' + customer_input.value + '%'}}...etc)

Thanks for helping.

2

I think something like this would work, it might need a little tweaking for your specific syntax!

let data = {{formatDataAsArray(query.data)}}
let customer = {{customer_input.value}}.toLowerCase()
let booking = {{booking_input.value}}.toLowerCase()
let statuses = {{status_check_group.value}}

let filtered = data.filter(row=>
    row.customer_name.toLowerCase().includes(customer) 
    && row.booking_id.toLowerCase().startsWith(booking)
    && statuses.includes(row.booking_status)
)

return filtered

A big PSA on this is that loading all of your data into your browser and filtering it down locally with JS in your browser will at a certain size cause some severe performance issues. For small to moderately sized data sets it will be an improvement overall, but definitely something to keep in mind as your database keeps growing. Most web apps will only ever load a single page (i.e. one month of transactions on your banking app). More helpful performance tips here: https://docs.retool.com/docs/building-performant-retool-apps

1 Like