Working with the database query IN filter

database

#1

The following query is only returning the first id/record as seen with Open in Browser.

SELECT id, username FROM users WHERE id IN (1,2,3)

Checking to see if this is a bug.

PS: It would be nice to be able to copy the SQL statement that is in the preview window when doing a bug report without having to manually type it.

Wappler 1.8.1


#2

Hi.

Add the "test": true, line in your server action to view the final query in the Open In Browser output.

As for the question:

In my experience, IN does not always work with hard-coded values. Try running this query directly in your MySQL/MSSQL client (phpMyAdmin or SSMS) and compare the outputs.


#3

Via phpMyAdmin, 3 records are returned, but only 1 via Wappler.

I got it to work using 3 OR rules, but I would prefer to use IN if possible.

I just learned that the final query is also available via Open in Editor.


#4

In that case, share the code here or with @patrick and he will sort out the bug, if any.


#5

It is the same SQL statement in Wappler that was executed in phpMyAdmin.


#6

No, not the query. The server action. Based on how you have designed the server action, final query is created in runtime.
You might see this in query builder, but it does not mean this is being executed.
This is why I suggested to set "test": true, as it shows the final query being executed with parameter values.


#7
{
  "query1": {
    "options": {
      "test": true,
      "connection": "connection1",
      "sql": {
        "type": "select",
        "columns": [
          {
            "table": "teddytalk_users",
            "column": "id"
          },
          {
            "table": "teddytalk_users",
            "column": "realname"
          },
          {
            "table": "teddytalk_users",
            "column": "biz_name"
          },
          {
            "table": "teddytalk_users",
            "column": "country"
          }
        ],
        "table": {
          "name": "teddytalk_users"
        },
        "joins": [],
        "query": "SELECT id, realname, biz_name, country FROM teddytalk_users WHERE id IN (2537,4737,827)",
        "params": [],
        "wheres": {
          "condition": "OR",
          "rules": [
            {
              "id": "teddytalk_users.id",
              "field": "teddytalk_users.id",
              "type": "double",
              "operator": "in",
              "value": "2537,4737,827",
              "data": {
                "table": "teddytalk_users",
                "column": "id",
                "type": "number"
              },
              "operation": "IN"
            }
          ],
          "conditional": null,
          "valid": true
        },
        "orders": [],
        "sort": null,
        "dir": null
      }
    },
    "query": "SELECT `id`, `realname`, `biz_name`, `country` FROM `teddytalk_users` WHERE (`id` IN (?))",
    "params": [
      {
        "id": "teddytalk_users.id",
        "field": "teddytalk_users.id",
        "type": "double",
        "operator": "in",
        "value": "2537,4737,827",
        "data": {
          "table": "teddytalk_users",
          "column": "id",
          "type": "number"
        },
        "operation": "IN"
      }
    ]
  }
}

IN operator working with multivalues
#8

This is one of the peculiarities of the Query Builder. You would expect to be able to add the criteria as you would in a normal SQL query, but you need to turn the list into an array:
{{"1,2,3".split(",")}}
This has been discussed here.


#9

Yes, IN expects an array, not a comma separated list so split() is needed to create the array


#10

Ok, How can I create an array ? Value in array 0,1,2,3
Tank you @Hyperbytes


#11

Both answers above explains you how to make an array - just use the split formatter, to split on comma.
Please read the responses more carefully.


#12

Excuse me @George
but I need to enter these values in html and not in query builder
how can i do it

written so it does not work well


#13

What exactly do you need to achieve? Where do you need these values exactly?
Please be a little more detailed when explaining this.


#14

I have this:

I want to pass the values 0,1,2,3 in the case of option “All documents”
as if the query were WHERE IN (0,1,2,3)


without touching the other options

option 0, 1, 2 and option 3 work good

I hope I explained myself


#15

Do you just want to show all records if nothing is selected?


#16

I want to show all records if the first option “All documents” is selected


#17

Well just add a new group in the query conditions, use the select there and use the select value as a condition for it!