Create this Query in Wappler

[Hyperbytes]

What we seem to need is an effective way of converting a data query into an array in suitable format to use with IN/ NOT IN

[TomD]

we seem to need is an effective way of converting a data query into an array in suitable format to use with IN/ NOT IN

I think we have that. Patrick has provided an alternative to the non-working values function. Unless I misunderstood the original question, I think this is the simplest solution.

[Hyperbytes]

Actually, initial tests seem to indicate that all is needed is to add

{{ queryname.groupBy(‘id’).keys() }}

Into the query designer, no json hacks needed

[Hyperbytes]

Server action

Result (as seen by table generator)

where query2 is defined as above

[nshkrsh]

Getting an errror: Can not convert Array to String.
I set the value of {{queryname.groupBy(‘id’).keys()}} to a variable using SET VALUE to check, and this is the value returned:
{"aa" : ["11","13","43"]}

These IDs are correct, but NOT IN condition still doesn’t work and throws the above error.

[George]

See also:

Custom/manual SQL query

After seeing so many requests about custom queries, we had more in depth investigation in supporting custom queries in Wappler and we can to do conclusion that it can be done! We will be making a new ‘Custom Query’ action for server connect that will allows you to enter any SQL query code. On its dialog you will also be able to define custom parameters in a grid and directly test/see the output! Because you will be able to run the query directly, we can use its output to constru…

[TomD]

I imagine you’re using the curly single quotes just to display here (if you were actually using them, that would cause an error).

I’m sure your expression is correct. I was getting the same error when using the values function. It might be worth checking the code to make sure it has updated correctly. I’ve done a number of experiments with this now , and haven’t had any problems using expressions like this.

[nshkrsh]

Yeah the quotes were an issue the first time I tried. But I fixed that to {{queryname.groupBy('id').keys()}} and I am getting a response in the variable as shared above.
But the query gives the conversion error.

Are you sure you are getting multiple IDs from your test exclusion query?

[TomD]

Yes - definitely getting the results I expect.

Here are the steps:

Turning on output only for the Set Value line, this is the raw data returned by running the query:

{"excl_ids":[5670,5631,5613,5431,5155,5047,4919,4910,4734,3997,5812,5814]}

[nshkrsh]

Response for me is still:
{"aa":["30","16","11"]}

code_value_id is numeric in DB. Don’t know why its coming as string. I see output for you is numbers.
Are you using .NET or PHP? I am using .NET with MySQL 8.0 odbc driver.

[TomD]

I’m using PHP.

[nshkrsh]

Must be IIS related then.
@patrick can you please check this?

[TomD]

It’s strange that the results are being returned as text. Is that definitely an issue with the array conversion or is that how the results are returned by the first query? Also, I think the query should work even if the numbers are in quotes, so perhaps there is another issue.

[Hyperbytes]

Amazing @George

Exactly what i asked for many months ago

Ability to apply a SQL function to a field within the query builder

Perhaps we are approaching this from the wrong direction.
To implement all the available functions for all the different data server types is a big ask, maybe an alternative would be an “Query Pass through” where a user could pick “Custom Query” and type in a custom query which is simply passed through the the database server as pretty much a a raw query with only globals processed

So the user could type something like

“SELECT DATE(datetimefieldname), Sin(angle),ASCII(symbol) WHERE filterval = {{$_GET.filterval}}”

and the $_GET.filterval would be processed but the remaining query would be passed Raw

I appreciate it is not quite “visual Wappler” but it would be immensely powerful

[Hyperbytes]

NishKarsh, did you try NOT editing the file and just adding the {{queryname.groupBy(‘id’).keys()} into the query editor conditions as above, worked perfectly for me

[nshkrsh]

try NOT editing the file

I deleted the edited server action. Made this from scratch and added the groupby in condition and set value.

I think the query should work even if the numbers are in quotes, so perhaps there is another issue

Yeah… quotes is probably not the issue.

[Hyperbytes]

Shot in the dark, try {{query1.groupBy(“id”).keys().toNumber()}}?

[nshkrsh]

Error: Can not convert Array to Double.

[Hyperbytes]

which stage is generating the error, i am not having the same issues as you,

query1 (get exceptions)
query2 get results condition NOT IN {{query1.groupBy(“id”).keys()}} and {{query1.groupBy(“id”).keys().toNumber()}} both work via the visual builder for me?

[nshkrsh]

The condition in query is generating error.
The group by itself is working, but when used in condition parameter for NOT IN, it says Cannot convert array to string.
I am able to print the value of group by using set value, so I know that part is working.

Are you testing this on IIS with MySQL too?