Problem querying nested sub-tables

[htatd]

Hello!

Am I correct with that querying for a sub-table within a sub-table is not supported? All fine with adding the sub table fields as in the example. Not working with an additional “layer” in the sub-table:

{
  "status": "500",
  "message": "Undefined binding(s) detected when compiling SELECT. Undefined column(s): [step_id] query: select \"key\", \"value\", \"step_id\" from \"step_conditions\" as \"step_conditions\" where \"step_id\" in (?)",
  "stack": "Error: Undefined binding(s) detected when compiling SELECT. Undefined column(s): [step_id] query: select \"key\", \"value\", \"step_id\" from \"step_conditions\" as \"step_conditions\" where \"step_id\" in (?)\n    at QueryCompiler_PG.toSQL (/opt/node_app/node_modules/knex/lib/query/querycompiler.js:110:13)\n    at Builder.toSQL (/opt/node_app/node_modules/knex/lib/query/querybuilder.js:83:44)\n    at ensureConnectionCallback (/opt/node_app/node_modules/knex/lib/execution/internal/ensure-connection-callback.js:4:30)\n    at Runner.ensureConnection (/opt/node_app/node_modules/knex/lib/execution/runner.js:272:20)\n    at processTicksAndRejections (node:internal/process/task_queues:96:5)\n    at async Runner.run (/opt/node_app/node_modules/knex/lib/execution/runner.js:30:19)\n    at async App._processSubQueries (/opt/node_app/lib/modules/dbconnector.js:261:28)\n    at async App._processSubQueries (/opt/node_app/lib/modules/dbconnector.js:264:13)\n    at async App.select (/opt/node_app/lib/modules/dbconnector.js:65:17)\n    at async App._exec (/opt/node_app/lib/core/app.js:487:30)"
}

[patrick]

With the latest update nested sub-tables is supported, can you share a bit more information about your database structure and what you are trying to select.

[htatd]

Hi Patrick.

The database structure:

The database query with only the sub-table under the main table works:

query_commission_groups": [
    {
      "title": "M Sales Commission",
      "description": null,
      "commission_type": "fixed_steps",
      "steps": [
        {
          "title": "21-30",
          "description": "20 Eur"
        },
        {
          "title": "11-20",
          "description": "16 Eur"
        },
        {
          "title": "0-10",
          "description": "12 Eur"
        }
      ]
    }
  ]

When I go 1 sub-table deeper inside the sub-table I get an error:

Screen Recording 2022-03-28 at 10.49.33

{
  "status": "500",
  "message": "Undefined binding(s) detected when compiling SELECT. Undefined column(s): [step_id] query: select \"key\", \"value\", \"step_id\" from \"step_conditions\" as \"step_conditions\" where \"step_id\" in (?)",
  "stack": "Error: Undefined binding(s) detected when compiling SELECT. Undefined column(s): [step_id] query: select \"key\", \"value\", \"step_id\" from \"step_conditions\" as \"step_conditions\" where \"step_id\" in (?)\n    at QueryCompiler_PG.toSQL (/opt/node_app/node_modules/knex/lib/query/querycompiler.js:110:13)\n    at Builder.toSQL (/opt/node_app/node_modules/knex/lib/query/querybuilder.js:83:44)\n    at ensureConnectionCallback (/opt/node_app/node_modules/knex/lib/execution/internal/ensure-connection-callback.js:4:30)\n    at Runner.ensureConnection (/opt/node_app/node_modules/knex/lib/execution/runner.js:272:20)\n    at processTicksAndRejections (node:internal/process/task_queues:96:5)\n    at async Runner.run (/opt/node_app/node_modules/knex/lib/execution/runner.js:30:19)\n    at async App._processSubQueries (/opt/node_app/lib/modules/dbconnector.js:261:28)\n    at async App._processSubQueries (/opt/node_app/lib/modules/dbconnector.js:264:13)\n    at async App.select (/opt/node_app/lib/modules/dbconnector.js:65:17)\n    at async App._exec (/opt/node_app/lib/core/app.js:487:30)"
}

[patrick]

Could you test the following update. dbconnector.zip (1.9 KB)

[htatd]

Unfortunately didn’t work. Same error

[patrick]

Could you place the generated json from the server action, in the context menu click Open in Code View to get the code.

[htatd]

{
  "meta": {
    "$_GET": [
      {
        "type": "text",
        "name": "sort"
      },
      {
        "type": "text",
        "name": "dir"
      }
    ]
  },
  "exec": {
    "steps": [
      "lib/get_same_company_id",
      {
        "name": "query_commission_groups",
        "module": "dbconnector",
        "action": "select",
        "options": {
          "sql": {
            "type": "SELECT",
            "columns": [
              {
                "table": "commission_group",
                "column": "title"
              },
              {
                "table": "commission_group",
                "column": "description"
              },
              {
                "table": "commission_group",
                "column": "commission_type"
              }
            ],
            "table": {
              "name": "commission_group",
              "alias": "commission_group"
            },
            "primary": "id",
            "joins": [],
            "sub": {
              "steps": {
                "type": "SELECT",
                "table": {
                  "name": "commission_group_steps",
                  "alias": "commission_group_steps"
                },
                "key": "commission_group_id",
                "columns": [
                  {
                    "table": "commission_group_steps",
                    "column": "title"
                  },
                  {
                    "table": "commission_group_steps",
                    "column": "description"
                  }
                ],
                "primary": "id",
                "joins": [],
                "query": "SELECT title, description\nFROM commission_group_steps AS commission_group_steps",
                "params": [],
                "sub": {
                  "step_conditions": {
                    "type": "SELECT",
                    "table": {
                      "name": "step_conditions",
                      "alias": "step_conditions"
                    },
                    "key": "step_id",
                    "columns": [
                      {
                        "table": "step_conditions",
                        "column": "key"
                      },
                      {
                        "table": "step_conditions",
                        "column": "value"
                      }
                    ],
                    "primary": "id",
                    "joins": [],
                    "query": "SELECT key, value\nFROM step_conditions AS step_conditions",
                    "params": []
                  }
                }
              }
            },
            "query": "SELECT title, description, commission_type\nFROM commission_group AS commission_group",
            "params": []
          },
          "connection": "db"
        },
        "output": true,
        "meta": [
          {
            "type": "text",
            "name": "title"
          },
          {
            "type": "text",
            "name": "description"
          },
          {
            "type": "text",
            "name": "commission_type"
          }
        ],
        "outputType": "array"
      }
    ]
  }
}

[htatd]

Hi @patrick, any idea regarding this issue?

[patrick]

I think it will work when you add the id to the columns list of each table, I’m working on an update to fix it.

[patrick]

The update I’ve send should fix the issue, I’ve done several tests with it now. When you edit the query in Wappler it will replace the file again with the old version, so you need to overwrite it again with the new file each time you edit the server action.

[htatd]

Ahh yes that worked thank you! I was saving the server connect before viewing every time because of habit

[Teodor]

This has been fixed in Wappler 4.7.2