How to Use MySQL Database Query "in" Condition With a Variable Value

I have a Server Side database query which returns a list of data from a table of invoices. One of those values returned is the id of an associated activity.

I want to use the list of activity id values to do a query on the activities table.

So I’ve defined a Set Value variable activity_list to contain that list of activity id values, given the value:

{{invoices.join(",", "activity")}}

This works perfectly and returns a list:

activity_list: "213,188,157,152,152"

However this does not work as the value supplied to the activities query. This is because Wappler is passing in the double quotes instead of just the list of numbers itself.

I’ve verified this by testing out the select statement in raw MySQL.

This statement returns the 4 rows I require:

select id, title_internal from activities where id in (213,188,157,152,152);

While this statement only returns one row (for value 213), which is what happens in the Server Action Wappler creates:

select id, title_internal from activities where id in ("213,188,157,152,152");

How can I fix this issue in Wappler?

Is this a Wappler bug?

Best wishes,
Antony.

This might be of some help:

Thank you Ken, that is wonderful.

The short answer is that the variable needs to be an array rather than a comma separated string.

So the syntax I needed was:

{{invoices.join(",", "activity").split(',')}}

Where the split() function takes the string and splits it into an array of elements, with each element being the text between the commas.

Also note that while it is tempting to use the .unique() function, if you use it directly it creates an array with missing values which gives a MySQL “Offset” error:

offset

So to make your values unique you need to join, split, make the array unique and then join and split it again!

invoices.join(",", "activity").split(',').unique().join(',').split(',')

(If you are using IN in a custom query, you need to read all of Ken’s linked document!)

1 Like