Subtable Query - won't accept conditions

Wappler General Bugs
#1

This may well be user error but could also be a bug.
(W5 B7, PHP, MySQL)

I have a table called departments and a subtable (foreign key linked) called departmentbudget
In Database manager they show correctly linked as well as in Query Builder. The issue comes when I want a list of departments but restrict the budgets that show to a certain financial year.

This is the main query (no conditions)

image
image1536×898 59.8 KB

Double-click the departments_budget entry and go to the sub-query’s conditions

image
image1298×462 25.7 KB

(I have tried with a static value of 1 which I know there are records for - i.e. budgetFY equal 1)

I thought I could run the query for departments and add the nested query for the budgets with its own conditions but as soon as I do I get an error:

code: 0
file: "/var/www/html/dmxConnectLib/lib/db/SqlBuilder.php"
line: 150
message: "Cannot use object of type stdClass as array"
trace: "#0 /var/www/html/dmxConnectLib/modules/dbconnector.php(371): lib\\db\\SqlBuilder->where('budgetDep', 'in', Array)\n#1 /var/www/html/dmxConnectLib/modules/dbconnector.php(64): modules\\dbconnector->processSubQueries(Object(lib\\db\\Connection), Array, Object(stdClass))\n#2 /var/www/html/dmxConnectLib/lib/App.php(197): modules\\dbconnector->select(Object(stdClass), 'q_departments', Array)\n#3 /var/www/html/dmxConnectLib/lib/App.php(169): lib\\App->execSteps(Object(stdClass))\n#4 /var/www/html/dmxConnectLib/lib/App.php(137): lib\\App->execSteps(Array)\n#5 /var/www/html/dmxConnectLib/lib/App.php(126): lib\\App->exec(Object(stdClass), false)\n#6 /var/www/html/dmxConnectLib/lib/App.php(104): lib\\App->exec(Object(stdClass))\n#7 /var/www/html/dmxConnect/api/purchasing/departments/departments_get.php(8): lib\\App->define(Object(stdClass))\n#8 {main}"

I have to remove the subtable condition for it to work at all

I think the issue is when I have a department that doesn’t yet have a budget set but it should be able to cope with that, just returning an empty array for the subtable query for that department (like it does with no conditions)

What am I missing?

Wappler 5.0.0 Beta 8 Released
#2
#3

Hey @patrick. Let me know if you need any more info to help resolve this.

#4

Didn’t have time to test, but please try the following update.

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

#5

Sorry @patrick,
Now get the following:

code: 0
file: "/var/www/html/dmxConnectLib/lib/db/SqlBuilder.php"
line: 369
message: "Undefined property: stdClass::$operation"
trace: "#0 /var/www/html/dmxConnectLib/lib/db/SqlBuilder.php(369): exception_error_handler(8, 'Undefined prope...', '/var/www/html/d...', 369, Array)\n#1 [internal function]: lib\\db\\SqlBuilder->compileWhereExpression(Object(stdClass))\n#2 /var/www/html/dmxConnectLib/lib/db/SqlBuilder.php(385): array_map(Array, Array)\n#3 /var/www/html/dmxConnectLib/lib/db/SqlBuilder.php(346): lib\\db\\SqlBuilder->compileWhereExpression(Object(stdClass))\n#4 /var/www/html/dmxConnectLib/lib/db/SqlBuilder.php(273): lib\\db\\SqlBuilder->compileWheres(Object(stdClass))\n#5 [internal function]: lib\\db\\SqlBuilder->compileComponent('wheres')\n#6 /var/www/html/dmxConnectLib/lib/db/SqlBuilder.php(246): array_map(Array, Array)\n#7 /var/www/html/dmxConnectLib/lib/db/SqlBuilder.php(235): lib\\db\\SqlBuilder->compileSelect()\n#8 /var/www/html/dmxConnectLib/modules/dbconnector.php(372): lib\\db\\SqlBuilder->compile()\n#9 /var/www/html/dmxConnectLib/modules/dbconnector.php(64): modules\\dbconnector->processSubQueries(Object(lib\\db\\Connection), Array, Object(stdClass))\n#10 /var/www/html/dmxConnectLib/lib/App.php(197): modules\\dbconnector->select(Object(stdClass), 'query', Array)\n#11 /var/www/html/dmxConnectLib/lib/App.php(169): lib\\App->execSteps(Object(stdClass))\n#12 /var/www/html/dmxConnectLib/lib/App.php(137): lib\\App->execSteps(Array)\n#13 /var/www/html/dmxConnectLib/lib/App.php(126): lib\\App->exec(Object(stdClass), false)\n#14 /var/www/html/dmxConnectLib/lib/App.php(104): lib\\App->exec(Object(stdClass))\n#15 /var/www/html/dmxConnect/api/test.php(8): lib\\App->define(Object(stdClass))\n#16 {main}"
#6

Could you post the json of the action file for me.

#7

New update: SqlBuilder.zip (3.1 KB)

#8

That seems to have fixed it. I’ll keep testing but all looks good so far

#9