Took a few days off and have just revisted this, to see if I can work out what's going wrong. Here is how you can easily replicate the issue.
(1) I loaded this project (without any edits) from the BigProf (Appgini) github repository:
https://github.com/bigprof-software/onl ... ing-system
(2) I entered a few records into the Invoices table, including some with a status of "Unpaid" and some with "Paid".
Everything works fine to this point, as expected.
(3) I then added this simple WHERE clause to hooks/invoices.php, as described in the posts above.
$options->QueryWhere = 'WHERE `invoices`.`status` != "Unpaid"';
That works just fine - returns all records where status is not equal to Unpaid, as it should.
(4) I then apply a simple filter using the Appgini built in filters: ID equal to 1
Result is SQL syntax error:
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1
Query:
select FORMAT(sum(`invoices`.`total`), 2) from `invoices` LEFT JOIN `clients` as clients1 ON `clients1`.`id`=`invoices`.`client` WHERE `invoices`.`status` != "Unpaid" and ( `invoices`.`id` <=> '1' ) )
- 1807462c-c465-4c27-8216-b7b86d185ec5.jpg (39.13 KiB) Viewed 31132 times
(5) The syntax error is caused by the final parentesis which is not required.
With debug, I can see the following:
(a) The full MySQL query when both the WHERE clause AND the filter is applied (error):
"SELECT count(1) from `invoices` LEFT JOIN `clients` as clients1 ON `clients1`.`id`=`invoices`.`client` WHERE `invoices`.`status` != "Unpaid" and ( `invoices`.`id` <=> '1' ) )"
(b) When just the WHERE clause and NO filter (no error):
"SELECT count(1) from `invoices` LEFT JOIN `clients` as clients1 ON `clients1`.`id`=`invoices`.`client` WHERE `invoices`.`status` != "Unpaid""
(c) When no WHERE clause and no filter applied (no error):
"SELECT count(1) from `invoices` LEFT JOIN `clients` as clients1 ON `clients1`.`id`=`invoices`.`client` "
(d) With no WHERE clause but with a filter applied (no error):
"SELECT count(1) from `invoices` LEFT JOIN `clients` as clients1 ON `clients1`.`id`=`invoices`.`client` where ( ( `invoices`.`id` <=> '1' ) )"
In the final filtered query example (d) above, the closing parenthesis is correct as it is obviously required.
In example (a) above you will see that the closing parenthesis is NOT required (or should have a preceding opening parenthesis).
The problem arises when applying a filter to a query that already has a "$options->QueryWhere =" WHERE clause applied. It breaks the parentheses?
Any ideas how I might go about preventing this from happening or am I missing something?