Using AppGini Filters + $options->QueryWhere = causes SQL syntax error?
Posted: 2019-12-05 05:41
I have need to hide all records older than 7 years from all Users.
Easy solution is to add WHERE query to $options in hooks/tablename.php like so:
That works perfectly. Filters out all jobs referred over 7 years old.
A problem arises however if you use the built in Appgini filters while that WHERE query is applied. Any other filter applied using AppGini's built in filters returns an SQL syntax error?
With debug, I can see that:
[QueryWhere] => WHERE `jobs`.`referred` > DATE_SUB(NOW(),INTERVAL 7 YEAR)
That is as expected and as it should be. This far all good.
Apply ANY filter like say a simple "Job Number equal to 3" and this is an example of the SQL syntax error displayed (the red Appgini error display for Admins) when a filter is applied:
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
I can see the end of the filter query in the error report which shows (using the example of Job Number equal to 3):
.... WHERE `jobs`.`referred` > DATE_SUB(NOW(),INTERVAL 7 YEAR) and ( `jobs`.`ourref` <=> '3' ) )
Any ideas what is causing this is and how I might be able to fix it? Thanks in advance.
Easy solution is to add WHERE query to $options in hooks/tablename.php like so:
Code: Select all
function jobs_init(&$options, $memberInfo, &$args){
// Only display jobs referred less than 7 years ago
$options->QueryWhere = 'WHERE `jobs`.`referred` > DATE_SUB(NOW(),INTERVAL 7 YEAR)';
return TRUE;
}
A problem arises however if you use the built in Appgini filters while that WHERE query is applied. Any other filter applied using AppGini's built in filters returns an SQL syntax error?
With debug, I can see that:
[QueryWhere] => WHERE `jobs`.`referred` > DATE_SUB(NOW(),INTERVAL 7 YEAR)
That is as expected and as it should be. This far all good.
Apply ANY filter like say a simple "Job Number equal to 3" and this is an example of the SQL syntax error displayed (the red Appgini error display for Admins) when a filter is applied:
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
I can see the end of the filter query in the error report which shows (using the example of Job Number equal to 3):
.... WHERE `jobs`.`referred` > DATE_SUB(NOW(),INTERVAL 7 YEAR) and ( `jobs`.`ourref` <=> '3' ) )
Any ideas what is causing this is and how I might be able to fix it? Thanks in advance.