What's wrong with my query?

Hi, I have the query below. If I run it in my SQL client, I'm getting results. If I run it in Retool, I am getting no results. Can I construct the SQL query with strings as outputs from Javascript code? Do you see any other problem? Thanks for help :pray:

UPDATE: what I am trying to solve -> I need to filter the table based on its columns. If any of the filters is empty, I need to show all results in my table.

SELECT
  i.product_id AS 'Product ID',
  i.product_name AS 'Product Name',
  COALESCE(i.origin_category_id, 'empty') AS 'Origin Category ID',
  COALESCE(c1.name, 'empty') AS 'Origin Category Name',
  COALESCE(i.target_category_id, 'empty') AS 'Target Category ID',
  COALESCE(c2.name, 'empty') AS 'Target Category Name',
  i.is_matched AS 'Matched'

FROM
	shop_feed_item i
  LEFT JOIN category_library c1 ON i.origin_category_id=c1.id
  LEFT JOIN category_library c2 ON i.target_category_id=c2.id

WHERE
	i.shop_project_id = {{select_source_feed_select.value}}
	AND i.product_id LIKE {{ '%' + filter_product_id.value + '%' }}
	AND i.product_name LIKE {{ '%' + filter_product_name.value + '%' }}
    AND {{filter_matched_only.value ? 'i.is_matched = 1' : 'i.product_id IS NOT NULL'}}
    AND {{filter_products_origin_category_name.value ? 'c1.name = \'' + filter_products_origin_category_name.value + '\'' : 'i.product_id IS NOT NULL'}}
    AND {{filter_products_target_category_name.value ? 'c2.name = \'' + filter_products_target_category_name.value + '\'' : 'i.product_id IS NOT NULL'}}
  {{filter_product_origin_category_id.value === '' ? '' : 'AND i.origin_category_id LIKE %' + filter_product_origin_category_id.value + '%'}}
  {{filter_product_target_category_id.value === '' ? '' : 'AND i.target_category_id LIKE %' + filter_product_target_category_id.value + '%'}}

ORDER BY i.product_id;

After several hours... it seems like I have found a way to make it work. The problem was with the filters using ternary operators. Still don't know the reason. Here is the query that works:

SELECT
  i.product_id AS 'Product ID',
  i.product_name AS 'Product Name',
  COALESCE(i.origin_category_id, 'empty') AS 'Origin Category ID',
  COALESCE(c1.name, 'empty') AS 'Origin Category Name',
  COALESCE(i.target_category_id, 'empty') AS 'Target Category ID',
  COALESCE(c2.name, 'empty') AS 'Target Category Name',
  i.is_matched AS 'Matched'

FROM
	shop_feed_item i
  LEFT JOIN category_library c1 ON i.origin_category_id=c1.id
  LEFT JOIN category_library c2 ON i.target_category_id=c2.id

WHERE
	i.shop_project_id = {{select_source_feed_select.value}}
	AND i.product_id LIKE {{ '%' + filter_product_id.value + '%' }}
	AND i.product_name LIKE {{ '%' + filter_product_name.value + '%' }}
  AND ({{!filter_matched_only.value}} OR i.is_matched = {{filter_matched_only.value}})
  AND ({{!filter_products_origin_category_name.value}} OR c1.name = {{filter_products_origin_category_name.value}})
  AND ({{!filter_products_target_category_name.value}} OR c2.name = {{filter_products_target_category_name.value}})
  AND ({{!filter_product_origin_category_id.value}} OR i.origin_category_id = {{filter_product_origin_category_id.value}})
  AND ({{!filter_product_target_category_id.value}} OR i.target_category_id = {{filter_product_target_category_id.value}})

ORDER BY i.product_id;

The reason is that Retool uses parameterized queries and it does not execute most javascript before passing the value to the parameter. Seems like {{}} can only "return" a boolean or one of Retool's model property values.

So it can understand {{select_source_feed_select.value}} or {{!filter_matched_only.value}} as they can return a simple value which Retool places into the parameter, but filter_matched_only.value ? 'i.is_matched = 1' : 'i.product_id IS NOT NULL' is incomprehensible so you get an error. Rather than get no results, you should have received an error. I get "An expression of non-boolean type specified in a context where a condition is expected" when I try something similar.

As a result, you need to use SQL to handle your logic, rather than Javascript, which is exactly what you did in your final version.

I'm sure one of the support techs can give more complete explanation, but this is my observation of how this all works.

Ah, okay – that is possible. Thanks for the help!