Pass Dropdown Inputs In SQL query 'LIKE'

Help me!
1

I am trying to pass values from dropdown to sql queries. I have two dropdowns Currency and language_code while Currency works fine I have to add ‘%/en/%’ this as value in language_code dropdown for it to work i.e. Pic added below.

Till now I have tried ‘%{{language_code.selectedValue}}%’, %{{language_code.selectedValue}}% and {{language_code.selectedValue}}.

Only this {{language_code.selectedValue}} work with %/en/% this as value from dropdown.

Am I doing something wrong in passing value in ‘LIKE’?

select sum(donation_order.amount) as value from donation_order
inner join accounting_transaction
on accounting_transaction.order_id = donation_order.id
WHERE
accounting_transaction.type = 'donation'
AND donation_order.status= 'paid' 
AND accounting_transaction.currency_code = {{Currency.selectedValue}}
AND donation_order.return_url LIKE {{language_code.selectedValue}};

image

2

Hello,
What do you want to do by comparing return_url and language_code?

In SQL, the Like keyword is used to search a substring into a string. In your case, the %/en/% will match all the return_url including /en/ string.

A simple example, I have a customer table with a tag column in my database.
If I want to get all the tags including /en/, you can run this following SQL request:

SELECT *
FROM customer 
where like LIKE '%/en/%';

Tell me if your need more help :pray:

3

So return url contains language code in database like www.xy.com/en/somepage.
I want to know if it is possible to place language code from dropdown in %/{{ language_code }}/%

SELECT *
FROM customer 
where like LIKE '%/{{ language_code }}/%';
4

Yes it is possible.
Can you share your smart action to understand how you build it? :pray:

5

It is not a smart action. It was build from frontend.

So there is dropdown with language codes such as en, fr, es

And I am passing the values from dropdown like dropdown.selectedValue to sql query

For example,
SELECT *
FROM customer 
where like LIKE '%/{{ dropdown.selectedValue }}/%';
6

Where do you write this SQL query? I need to know more about the context to unlock you.

7

Here is an example

image
image526×846 23.8 KB

8

Are you using the workspace ?
Are you using a “dropdown” component with a “chart” component ?

9

Yes we are using the workspace. Yes we are using a “dropdown” component with a “chart” component.

10

Can you replace your syntax by this one: like '%' || {{dropdown.selectedValue}} || '%'?

11

Thanks for your help it seems to be working.

I want to add “/” before and after {{dropdown.selectedValue}}

12

like '%/' || {{dropdown.selectedValue}} || '/%'?

13

“‘%/’ || {{language_code.selectedValue}} || ‘/%’” is returning empty.

14

Do you have ‘en’ or ‘/en/’ in your dropdown value ?

15

en
image

And this is giving wrong result LIKE '%' || '/' || {{language_code.selectedValue}} || '/'|| '%'; On further inspection it seems to be same result.

16

Hello,
Did you find a solution ?
I’m little bit astonished that solution “‘%/’ || {{language_code.selectedValue}} || ‘/%’” does’t work.

17

Well no, this is my whole query and it returns null while in mysql query should be 4007

select sum(donation_order.amount) as value from donation_order
inner join accounting_transaction
on accounting_transaction.order_id = donation_order.id
WHERE
AND donation_order.status= 'paid' 
AND accounting_transaction.currency_code = {{Currency.selectedValue}}
AND donation_order.return_url LIKE '%/' || {{language_code.selectedValue}} || '/%';

Maybe I am writing it wrong. @Alban_Bertolini Any suggestions?

18

Can you remove the two previous AND to check if our like condition is wrong ?
I’m testing with a mysql database and the syntax is correct on my side.

19

I have removed all AND conditions but the results are always the same now

select sum(donation_order.amount) as value from donation_order
inner join accounting_transaction
on accounting_transaction.order_id = donation_order.id
WHERE donation_order.return_url LIKE '%/' || {{language_code.selectedValue}} || '/%';
20

@Alban_Bertolini Maybe I am doing something wrong. Can you link a documentation for this usage so I might check myself instead of disturbing anyone?