Only first id/record returned with IN function


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

#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