Using condition with conditional fields in custom queries

Wappler General How To
#1

If I use a normal query in Wappler, I can set conditions with conditions, like this

Screenshot 2020-08-31 at 15.20.52
Screenshot 2020-08-31 at 15.20.521024×603 78.9 KB

When using a custom query can i achieve the same thing.
I have attempted adding them in just like the normal query but it does not seem to do the same thing

Screenshot 2020-08-31 at 15.23.44
Screenshot 2020-08-31 at 15.23.441552×989 283 KB

Make custom query parameter literal vs string
#2

How about using CASE statements in your where clause?

1 Like
#3

Thanks @mebeingken, I tried that, but I must be doing something wrong, still not getting it quite right, lol
Will send through more details when i have unconfused myself a little more.

#4

Try something like this in where clause:
CASE WHEN :P1 = ‘’ THEN table1.column = :P1 ELSE 1 = 1 END

and set :P1 = {{$_GET.var}}.

We have this setup at numerous places and it works well for us.

You can also use IFNULL but that is a bit trickier.

1 Like
#5

You could create the query in a variable and use a stored procedure to execute it. This could be as flexible as you wanted.

1 Like
#6

Hey @sid

I do something similar, but I’m trying to clean up my code a bit and I’ve not been able to get it to work as you have suggested.

It seems when I try to have the CASE statement do a WHEN check for the parameter having NO VALUE (i.e. from an input field before a user as entered anything) it always creates an error… I’ve tried WHEN :P1 IS NULL or WHEN :P1 = '' or WHEN :P1 = "" but they all fail.

It only seems to work when i pass an actual value through in the query parameter, such as -1

Do you still get it work like you have said in this example?

#7

Did you try returning the said GET or POST variable via set value to checl what value server action is reading for it when its not sent from client side?
Also, you can try to do Select :P1 to check.

#8

THanks for the response.

When the input field is empty in the front end, using SetValue in the server connect file the response is:

{"testParam":""}

typing something into the input responds with (as expected)

{"testParam":"5"}

It just seems that when compiling the SQL code, it does not detect NULL as a value in that can be checked against in the CASE statement…

#9

Can you also try setting a custom query as select :P1 as 'test' and set :P1 as the testparam?
If you get "", it means you have to use :P1 = ''. If null, then use :P1 is null.

Also, if you can share your exact condition, that might help debugging this too. Because we are still using this with latest versions and there are no issues.

#10

Thanks again @sid

Here is a test query setting a value and then using a basic query like you suggested:

image

SELECT :P1 AS testParam

Response when run in browser and the browser is sending an empty query parameter:

{"testParam":"",
"custom":[{"testparam":""}]}

So based on what you’re saying, it does appear to work? Perhaps more digging needs to be done.

#11

This all looks good… What is the final case when custom query that is not working for you?

#12

I’ve given up for now and gone using a slightly different method as i couldn’t get it to work and need to keep moving on :slight_smile:

As per above, it seemed to work when using it for simple things like setting a value or in a SELECT statement…but when used as a condition in a CASE statment it always errored out.

Here is a sample statement that DOES NOT work:

WHERE 
  CASE WHEN :P2 = '' THEN  TRUE ELSE week_num = :P2 END

That throws an error that seems to be:
invalid input syntax for integer: ""

Or in more detail:

    at Parser.parseErrorMessage (/opt/node_app/node_modules/pg-protocol/dist/parser.js:278:15)
    at Parser.handlePacket (/opt/node_app/node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (/opt/node_app/node_modules/pg-protocol/dist/parser.js:39:38)
    at Socket.<anonymous> (/opt/node_app/node_modules/pg-protocol/dist/index.js:10:42)
    at Socket.emit (events.js:315:20)
    at addChunk (_stream_readable.js:295:12)
    at readableAddChunk (_stream_readable.js:271:9)
    at Socket.Readable.push (_stream_readable.js:212:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:186:23)
#13

This kind of makes sense. The value is expected to be a number. So when its not there, it fine comparing it with a string ''. But, when you so send a value, it becomes a string vs int comparison.
You can make use of CAST function to always convert :P2 to string in the case when part. And use it normally in the else part.
https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html