@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
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
Output
Add a join in the sub table and it breaks
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
Is this the same issue as here?
The update appears to have also resolved that issue thanks
Fixed in Wappler 5.0.3
This topic was automatically closed after 32 hours. New replies are no longer allowed.