Adding join within sub query breaks query

Wappler Version : 4.9.1
Operating System : win 10
Server Model: PHP
Database Type: mariaDB
Hosting Type: Wamp

Expected behavior

What do you think should happen?

Query should return results but query breaks

I have an invoice table with a sub table of invoice items

I also have customer, shipping address and invoice address tables which i link with joins

I have a query which selects an invoice, items from the sub table and also joins to the customer, shipping and invoice address tables

The query returns data exactly as i would expect

However io need to pull product names from the products table, linked to the product_id field ion teh invoice_items sub table

image

So i open the table query builder

Select the subtable

open it

image

I then add a join to the sub query to link the products table

and add the product name

image

I then save and run the query and get this error

Invoice_id is part of the query as can be seen above

API file content
get_invoice_by_id.zip (2.0 KB)

@Teodor , any feedback?

I see that you have called your sub table invoice_items while being a sub table already gets the invoice_prefix

So you final table name in the database ends up with double invoice_ prefix.

You can just call the sub table “items” when creating it as such. It will get the right prefix of its parent table.

Maybe that is the root cause of your issue.

Thanks George. I did spot that and actually rebuilt the query from scratch but same issue. Am on holiday in Tenerife now so may not get access to check again until next Wednesday.

1 Like

@Hyperbytes Is this still an issue? Or fixed in Wappler 5?

I wouldn’t like to confirm it’s fixed, the results I got seemed erratic, sometimes working, sometimes not. I am not yet convinced that the aliasing get’s it right every time but having said that, i have not had issues recently. PHP platform seemed most prone.

Thanks for the reply. In my 4.9.1 Wappler, with MSSQL, I have found that setting an alias for the sub-table column and for the same table in its own query builder works. But if I don’t deliberately do that, it throws an error.
Will probably create a bug report once I upgrade to 5.x and test it there.

Just done a test with node and Wappler 5.0.2, worked perfectly without any manual changes or aliases added

With PHP, still issues. It works fine with just the sub table
Table Structure
image

Output
image

Add a join in the sub table and it breaks

image

Thanks for the 5.0.2 confirmation with alias.

In 4.9.1, I am able to use join in sub table query with ASP/MSSQL - but I have to set an alias manually. Without the alias it throws an error.

From your screenshots it looks like that one of the where conditions is being prefixed with the table name instead of the alias name. Here an update for the PHP version, please try it out.

SqlBuilder.zip (3.1 KB) Unzip to dmxConnectLib/lib/db.

If it still doesn’t work, could you then check the Debug flag with the query action, it should then output the generated SQL query.

Sorry Patrick, same error

Also no SQL query output when Output checked, same error message, nothing else


It’s just a testbed i set up for this using SQLite so i could zip the entire site for you if that would help

Here is the API action content :

<?php
require('../../dmxConnectLib/dmxConnect.php');


$app = new \lib\App();

$app->define(<<<'JSON'
{
  "meta": {
    "$_GET": [
      {
        "type": "text",
        "name": "sort"
      },
      {
        "type": "text",
        "name": "dir"
      }
    ]
  },
  "exec": {
    "steps": {
      "name": "query",
      "module": "dbconnector",
      "action": "select",
      "options": {
        "connection": "db",
        "sql": {
          "type": "SELECT",
          "columns": [
            {
              "table": "invoices",
              "column": "invoice_number"
            },
            {
              "table": "invoices",
              "column": "client_id"
            }
          ],
          "table": {
            "name": "invoice",
            "alias": "invoices"
          },
          "primary": "invoice_id",
          "joins": [],
          "sub": {
            "items": {
              "type": "SELECT",
              "table": {
                "name": "invoice_items",
                "alias": "items"
              },
              "key": "invoice_id",
              "columns": [
                {
                  "table": "items",
                  "column": "quantity"
                },
                {
                  "table": "items",
                  "column": "product_id"
                }
              ],
              "primary": "item_id",
              "joins": [
                {
                  "table": "products",
                  "column": "*",
                  "alias": "product",
                  "type": "INNER",
                  "clauses": {
                    "condition": "AND",
                    "rules": [
                      {
                        "table": "product",
                        "column": "product_id",
                        "operator": "equal",
                        "value": {
                          "table": "items",
                          "column": "product_id"
                        },
                        "operation": "="
                      }
                    ]
                  },
                  "primary": "product_id"
                }
              ],
              "query": "SELECT items.quantity, items.product_id\nFROM invoice_items AS items\nINNER JOIN products AS product ON (product.product_id = items.product_id)",
              "params": []
            }
          },
          "wheres": {
            "condition": "AND",
            "rules": [
              {
                "id": "invoices.invoice_id",
                "field": "invoices.invoice_id",
                "type": "double",
                "operator": "equal",
                "value": 1,
                "data": {
                  "table": "invoices",
                  "column": "invoice_id",
                  "type": "number",
                  "columnObj": {
                    "type": "increments",
                    "primary": true,
                    "unique": false,
                    "nullable": false,
                    "name": "invoice_id"
                  }
                },
                "operation": "="
              }
            ],
            "conditional": null,
            "valid": true
          },
          "query": "SELECT invoice_number, client_id\nFROM invoice AS invoices\nWHERE invoice_id = 1",
          "params": []
        },
        "test": true
      },
      "output": true,
      "meta": [
        {
          "type": "number",
          "name": "invoice_number"
        },
        {
          "type": "text",
          "name": "client_id"
        },
        {
          "name": "items",
          "type": "array",
          "sub": [
            {
              "type": "text",
              "name": "quantity"
            },
            {
              "type": "number",
              "name": "product_id"
            }
          ]
        }
      ],
      "outputType": "array"
    }
  }
}
JSON
);
?>

Thx, seems that debug code is not being used when the query has sub tables. But thanks to the trace I found an other location that needed an update.

SqlBuilder.zip (3.1 KB)

1 Like

Thanks Patrick, working perfectly now

image

Is this the same issue as here?

The update appears to have also resolved that issue thanks

Fixed in Wappler 5.0.3