GET array for use with IN clause of custom query action

[mebeingken]

Hi All,

Having trouble today with getting an array passed into a custom query action IN clause.

For testing I have tried hard-coding the numbers, which yields the correct results:

If I then switch out the values (88,90,123,98,91,92) and replace with parameter :P4, the result in testing the query reduces to a single result (because it is using only the first number from the list.)

I understand that the IN clause requires an array, but I can’t find the right combination.

I have tried:

{{$_GET.filter_tags}} which is an array that receives it’s values from an array variable on the front end.

I have also tried to take the GET variable and split it (because it seems to be a comma separated string, not an array) and convert it into an actual array, but I get an array to string conversion error.

At any rate…does anybody know how I can a) use an array variable on the page to b) pass that array through for use within an IN clause in a custom query?

Thanks!

–Ken

[Teodor]

Hi Ken,
Please check the following topic:

Working with the database query IN filter

This is one of the peculiarities of the Query Builder. You would expect to be able to add the criteria as you would in a normal SQL query, but you need to turn the list into an array: {{"1,2,3".split(",")}} This has been discussed here.

[mebeingken]

Thanks @Teodor,

That is the first thing I was trying, but as I said, I keep getting an array to string conversion error. I assume because I need numbers, not string.

So I have tried:
{{$_GET.filter_tags.split(",").toNumber()}}

{{$_GET.filter_tags.toNumber().split(",")}}

{{$_GET.filter_tags.split(",".toNumber())}}

So perhaps my question is: How do I split and return numbers?

[Teodor]

How are the GET parameter values generated, i.e. what kind of value is passed there and what generates the value?

[mebeingken]

It is an array variable on the front end:

<dmx-array id="filter_tags"></dmx-array>

I add to that array like this:

[Teodor]

In the query builder, this should be working perfectly fine, with a value coming from the array component:

{{$_GET.parameter.split(",")}}

No need of tonumber formatter.

[mebeingken]

[Teodor]

I just tested this and it seems to work perfectly fine. Are you sure you added this in the Filters section of the query builder?

[mebeingken]

Like this?

[Teodor]

Sorry, I missed the part you were referring to a custom query. I am testing this with a normal query, built with Wappler query builder.

@patrick can advise here.

[mebeingken]

Ya, I’ve done this in a regular query plenty of times…always works. In the custom query though, I can’t even get the proper results when using the Execute Query button within the action, but assumed that is because I didn’t format properly as an array.???

[George]

{{$_GET.filter_tags}}

I think you should use this one indeed. No need for splitting here just give the comma separated list as single parameter.

If you are getting a single record on the test query that might be because you have entered a single static test value? Maybe add the test numbers list there.

Any way even if with single row - test for the real results in the browser.

[mebeingken]

Thanks @George,

Unfortunately, using {{$_GET.filter_tags}} doesn’t fix it (in the browser.) Only the first value from the array is used in the search. I’ve confirmed by doing the search directly in mysql.

My understanding (from this thread Query using IN clause) is that IN requires an array, but the GET doesn’t seem to return an array to “value” in the debug:

But it sounds like you are saying the comma sep list, and that doesn’t work either.

Hoping for some clarification on what I’m truly after.

Thanks for the help!

[George]

Yes with the normal queries the IN requires an array but with the custom queries it is a different case.

@patrick should check it out as to see if we support the IN fully.

It should be handled like any other PDO parameter, for example:

https://stackoverflow.com/questions/920353/can-i-bind-an-array-to-an-in-condition

[patrick]

From the stackoverflow posted by @George I read that arrays are not supported by PDO and you have to rewrite the query and have a parameter for each value in the array. In the query builder the query is build dynamically and it creates multiple parameters for each value in the array for the IN statement.

[mebeingken]

@George and @patrick,

Thanks, this was very helpful.

For anybody needing to do this in the future, here is what will work inside a wappler custom query:

I found you can use the mysql find_in_set function in lieu of the IN clause. It should be noted that this shouldn’t be used on large sets as the performance will degrade.

find_in_set(cast(distro_tags.tag as char), :P4)

In my example this adds a WHERE condition requiring the tag field from the distro_tags table to be one of the entries in the P4 parameter, which is a string representation of a numeric array.

The parameter can be passed directly from the $_GET variable into the sql statement since it natively has no white space. If creating your own, the format is value followed by a comma without any white space (or it will break.)

Solved!