Create this Query in Wappler


#41

I will be {{$_GET.somevar}} i would expect


#42

Yes, replace the {{30}} with the expression needed for that variable. The execute action is not available in Wappler, but by hand coding you can use it to run your custom queries. Only problem is that Wappler doesn’t have any meta data, so you don’t have the results in the pickers of Wappler.


#43

So not being shy @patrick, would it be feasible to have a server action on the basis of
set value = queryresult.keyfield => array to move query results to an array so they can be used with IN/NOT IN ?


#44

I haven’t tested, but you could try {{ query1.data.groupBy('id').keys() }}.

change query1 with the name of your subquery action and the 'id' with the column of the result set


#45

“query”: “SELECT * FROM tbl_code_value WHERE is_active = 1 AND (code_id = :$_GET_oid OR code_id = :$_GET_rid) and id not in (SELECT code_value_id FROM tbl_code_value_exclusion where tbl_code_value_exclusion.campaign_id = :$_GET_cid)”,
“params”: [
{
“operator”: “equal”,
“type”: “expression”,
“name”: “$_GET_oid”,
“value”: “{{$_GET.oid}}”
},
{
“operator”: “equal”,
“type”: “expression”,
“name”: “$_GET_rid”,
“value”: “{{$_GET.rid}}”
},
{
“operator”: “equal”,
“type”: “expression”,
“name”: “$_GET_cid”,
“value”: “{{$_GET.cid}}”
}
]

Is this ok?
Because its not working. The sub-query is not affecting the results.
Without sub-query I am getting 19 rows. It should be 16, when sub-query exclusion works.


#46

The parameters in the query should be replaced with ?. The params array should only contain the values.

"query": "SELECT * FROM tbl_code_value WHERE is_active = 1 AND (code_id = ? OR code_id = ?) and id not in (SELECT code_value_id FROM tbl_code_value_exclusion where tbl_code_value_exclusion.campaign_id = ?)",
"params": ["{{$_GET.oid}}", "{{$_GET.rid}}", "{{$_GET.cid}}"]


#47

For my test at least, this is a solution - with a minor change: removing ‘data’ from the expression and using:
{{ query1.groupBy('id').keys() }}

Great - this is something I will be needing too.

Is the ‘values’ function meant to do this? If so, will it be implemented? Or perhaps it’s not needed, unless it would be faster.


#48

Still the same response. 19 rows.

Do I need to get rid of or add something in rest of the file?


#49

The values function should do indeed the same, seems that it was possible to get the same result with the groupBy and keys functions combined.


#50

You really should try to avoid sub queries and just make sure you have the available data in the main table. Or try to convert them to joins, see:

I really won’t recommend the code hacking solution Patrick proposed because even if you get the query to run, the return data structure will be unknown and all the data pickers later on won’t give you any fields to pick from. So you have to write everything by hand …


#51

Kind of gone around in a circle on that one @George, that was my initial thought but due to the one to many nature of the join with filters at each ends of the join then this doesn’t seem to be possible with a join.


#52

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


#53

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.


#54

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


#55

Server action

image

Result (as seen by table generator)

where query2 is defined as above


#56

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.


#57

See also:


#58

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.


#59

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?


#60

Yes - definitely getting the results I expect.

Here are the steps:

image

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]}