Create this Query in Wappler

[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?

[Hyperbytes]

Was testing on Apache/Mysql, will re-test on my local IIS (Server 2012 R2) and report back

[Hyperbytes]

No errors on IIS/PHP/ MySQL at my end

[nshkrsh]

MySQL driver version?

[Hyperbytes]

PM sent with config info link

[patrick]

Some drivers don’t return the results correctly and numbers can be returned as string. Using them in the query should be no problem, most databases don’t matter if you pass the variable as number or string, they will convert it to the correct type. So id = 1 and id = '1' both will work the same.

@nshkrsh What is the query2 you now have, did you use the aa value for the NOT IN condition?

[nshkrsh]

did you use the aa value for the NOT IN condition?

Yes. Tried it with aa and writing the query with formatter directly too.

[patrick]

Do you get an error now, what is the result? Also when the result is incorrect, enable the Debug in the select action, this will output the query and parameters used.

[nshkrsh]

The error is:

Getting an errror: Can not convert Array to String.

Messaging you a link to check the debug output.