Bug in retool when using js conditional in where statement

[technopath]

I believe I found a bug in your system.

At the end of the query I have a conditional statement to check the value of the control manufacturer.value

The issue is even if the control has a value the results ignore it in retool

Here is the query

---

select distinct
o.increment_id as Order_ID,
o.created_at as Order_Date,
w.reseller_id as Member,
w.company_name as Vend_Name,
z.city,
z.region,
eaov.value as MFG,
b.sup_name as Distributor,
s.company_supplier_code as Dist_Acct_num,
i.sku AS pop_sku,
n.value AS Product_Description,
sw.value as Size,
get_deal_name(get_deal_id(product_options)) AS program,
v.qty AS pop_qty,
v.Ship_Date AS Ship_Date,
i.am_earn_reward_points As Rebate
FROM sales_order o
JOIN sales_order_item i ON i.order_id = o.entity_id
JOIN products_vertical v ON i.item_id = v.item_id
JOIN catalog_product_entity_varchar n ON n.entity_id = i.product_id AND n.attribute_id = 73
LEFT JOIN catalog_product_entity_varchar sw ON sw.entity_id = i.product_id AND sw.attribute_id = 154
LEFT JOIN catalog_product_entity_int ma ON ma.entity_id = i.product_id AND ma.attribute_id = 83
LEFT JOIN eav_attribute_option eao ON ma.value = eao.option_id
LEFT JOIN eav_attribute_option_value eaov ON eao.option_id = eaov.option_id
left join technopath_company_address_supplier s on s.customer_id = o.customer_id and s.supplier_id = v.supplier_id and s.shipping_address_id = v.location_id
left join bms_supplier b on b.sup_id = s.supplier_id
left join amasty_company_account_company w on w.company_id = s.company_id
left join sales_order_address z on z.entity_id = v.Location_id
left join catalog_product_link l on l.linked_product_id = i.product_id and l.link_type_id = 3
where
o.created_at between {{dateRange1.value.start}} and {{dateRange1.value.end}}
{{ manufacturer.value ? AND eao.option_id IN (${manufacturer.value}) : '' }}

---

**Here is the debug from running the query, it returns all results ignoring the value in the IN statement

see the results returned by retool and notice how the mfg column has all MFGs where it should only have General mills which is = 16

When i take the query generated in the debug and run 'as is' in mysql workbench i get 139 results, only General Mills

**When i change the code in retool query without the if condition in the where I get just general mills. Notice how the query is the same in the debug

[ScottR]

You have the between statement but then after it there is no AND
SO, if I am reading what you are trying to do, you could try....

AND ({{ manufacturer.value}} OR eao.option_id IN ({{manufacturer.value}))

[technopath]

@technopath

{{ manufacturer.value ? AND eao.option_id IN (${manufacturer.value}) : '' }}

Thanks, Scott for your reply the AND is added in the JS condition

{{ manufacturer.value ? AND eao.option_id IN (${manufacturer.value}) : '' }}

If you look closer at the resulting query the AND is there

[ScottR]

The problem is that you probably cannot do what you are trying to do.... try using a CASE statement.... or what I suggested...

[technopath]

@ScottR

AND ({{ manufacturer.value}} OR eao.option_id IN ({{manufacturer.value}))

The issue with that is if the user does not filter by Manufacteur hence returning no value we get a SQL error

It works well if there is a value

Initially tried the case statement and it had its own issue.

What is confusing me is that what I am doing is resulting in the correct query but retool for some reason does not process it properly hence this a bug in retool

[ScottR]

Do the following - add the ! in that so if there is NO value it will complete
({{ !manufacturer.value}} OR eao.option_id IN ({{manufacturer.value}))

[technopath]

I found a solution

I created a transformer

if ({{manufacturer.value}} == '') {

return(0);

} else {

return({{manufacturer.value}});
}

then called in my query using case else

AND
CASE
WHEN {{Array.isArray(man_transformer.value) ? "1" : "0"}} = 0 THEN eao.option_id > 0
ELSE eao.option_id IN ({{man_transformer.value}})
END;

Thanks for your help