Hi All,
I need to use the IN clause in a CUSTOM query and am using PostgreSQL. I've gone through the forum and found some old threads from 2019
But these show that it wasn't possible and Ken had to resort to using a mySQL function find_in_set.
I don't think this is available in PG, so i'm asking if anyone has been able to use the IN clause using a PG setup?
If so, how did you get it workin?
Cheers,
Phil
Perhaps this is more related to the fact that its a NodeJS server application and i believe that Wappler uses Knex to compile the SQL for the Node server…and Knex has issues with arrays in WHERE / IN clauses???
That is not the case because we have seen the same in PHP server apps.
It could be that there is an issue with Knex, but being unable to set IN via params in custom query looks like a global Wappler issue.
Maybe try these links, if you haven't yet.
Thanks @sid… delayed response but thought I’d share my own solutions here for future reference.
As i was using PostgreSQL, FIND_IN_SET isn’t an available option, and like your first link suggested, Postgres has the ANY function/syntax that can be used in a WHERE clause really easily.
The only issue, is that for me to make it work i needed the data that I was filtering on to be in the database somewhere so i could return it using a SELECT statement.
It ends up looking something like this:
SELECT
*
FROM
table.name
WHERE
filter_id = ANY (SELECT filter_ID from filter.table WHERE field = filter)
That’s just some random made up query, but the general idea is that you need to use a SELECT statement after the ANY clause and it needs to return a single column.
You can also use any standard operator before the ANY clause, like =, <>, < etc etc…
Hope this helps anyone looking.
Possibly helpful?
Summary : in this tutorial, you will learn how to use the PostgreSQL IN operator in the WHERE clause to check if a value matches any value in a list.
Thanks for the link, but the issue was that the IN clause doesn’t work properly in Wappler when using custom queries.
@Teodor. Should this be raised as a bug? As far as I can tell from previous posts, Ken just found a work around and never raised one. Not sure if it’s changed or not, but still seems you can’t use the IN clause in a custom query.
I dont think its Wappler bug, but more to do with how parameters get replaced in the libraries being used for custom parameters. @patrick?
Perhaps not a bug as such, but as far as I can tell, there isn’t a way to use the IN clause in a Wappler custom query when passing a dynamic array/set of data for the IN Clause.
I assume this query all by itself produces results.
Can you post the actual custom query you are describing and explain it specifically from the values in your table?
What does the Custom Query literally look like before you try to duplicate it in Wappler?
How about using Stored Procedures in your database server and then referring to them in your Custom Query?
https://www.tutorialspoint.com/postgresql/postgresql_functions.htm
Yes, the SELECT needs to return results with just one column of data. the example was a bit poor…here is an actual working query from Wappler:
WHERE
work_groups."id" = :P1
AND date_reporting.week_sort = ANY ( SELECT week_sort FROM forecasts_historical WHERE forecasts_historical.forecast_id = :P2 AND work_group_id = :P1 )
Not sure if there is any confusion here, the above works fine and I was just sharing an alternative method to using the IN clause in a custom query.
AS far as I’m aware, you can’t use the IN query in a custom query in Wappler (at least not with a NodeJS / PostgreSQL project)
I’ve tried and others have too. I’ve yet to see someone comment saying they have it working (using dynamic data…it works fine if you hard code the array into the SQL statement.)
Cheers
After looking at this page that affirms that IN works well in a PostgreSQL Select I’m curious if digging into your actual Wappler Custom Query file written in JSON will show you what is wrong with asking Wappler to save and store it.
Maybe there you can confirm if this a Wappler Connection bug.
And if you find a “corruption” in the query using IN you can Edit the IN Custom Query in the JSON line directly.
Save & Test it, making sure that Wappler is using the newly edited query.
https://www.postgresqltutorial.com/postgresql-in/
Additionally, is it possible that Wappler is not using this particular NPM module?
https://node-postgres.com/features/queries
https://node-postgres.com/features/queries
PostgreSQL does not support parameters for identifiers. If you need to have dynamic database, schema, table, or column names (e.g. in DDL statements) use pg-format package for handling escaping these values to ensure you do not have SQL injection!
