Using AppGini Filters + $options->QueryWhere = causes SQL syntax error?

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
peebee
AppGini Super Hero
AppGini Super Hero
Posts: 330
Joined: 2013-03-21 04:37

Using AppGini Filters + $options->QueryWhere = causes SQL syntax error?

Post by peebee » 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:

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;
	}
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.

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 1546
Joined: 2018-04-01 10:12

Re: Using AppGini Filters + $options->QueryWhere = causes SQL syntax error?

Post by pböttcher » 2019-12-05 22:29

Hi,

it can be that you need to check if the QueryWhere is empty. If so you can apply the WHERE Clause if not you need to append you filter by adding it as " AND" Clause.

Did not try that but could be.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

peebee
AppGini Super Hero
AppGini Super Hero
Posts: 330
Joined: 2013-03-21 04:37

Re: Using AppGini Filters + $options->QueryWhere = causes SQL syntax error?

Post by peebee » 2019-12-09 04:31

Thanks for the reply pböttcher. Unfortunately, I still can't get it to work?

In debug, I can see that

[QueryWhere] =>

is empty until I add my WHERE query in the function jobs_init hook. Then I get:

[QueryWhere] => WHERE `jobs`.`referred` > DATE_SUB(NOW(),INTERVAL 7 YEAR)

That query works just as it should and returns the correct rows. MySQL query is OK but filters do not work?

I tried to substitute the WHERE with an AND clause but it failed to return correct data (no error - just all rows returned).

For testing purposes, I actually loaded two preconfigured Appgini apps from the Appgini download page (Invoicing & Online Clinic Management System). I tested them both - same result.

In the Invoicing app for example, I applied a very simple WHERE clause to the function invoices_init hook (eg: $options->QueryWhere = 'WHERE `invoices`.`status` != "Unpaid"';). The WHERE clause worked just fine and returned the correct data - but applying any Appgini filters again caused an SQL syntax error?

If I apply a simple Appgini filter like "Invoice ID equal to 2" and a MySQL error occurs:
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


This is the actual query that is being reported in the error:
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` <=> '2' ) )

ANY filter causes the same error result? Is this a bug or am I doing something wrong?

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1497
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

NARROW DOWN: Using AppGini Filters + $options->QueryWhere = causes SQL syntax error?

Post by jsetzer » 2019-12-09 07:33

Hi peebee,

I agree, it should work the way you did it. Looks perfect.

For narrowing down, I recommend doing the following checks:
  • Double check the letter-casing of jobs-table (lowercase)
  • and of referred-column (lowercase) and
  • and also of $options->QueryWhere (PascalCase) in jobs_init()-function
  • Check if the user can see any rows of that table at all (permissions)
  • Also you can briefly check if there are any fiter_... arguments in the url
  • ... and if there is any SearchTerm (from quick searchinput)
  • Then you should have a look at the file /jobs_view.php (in root directory) near the line where it says "// hook: jobs_init". The Query will be defined in the lines above that comment, starting at the line where it says "// mm: build the query based on current member's permissions"
  • Next you should have a look at ./datalist.php (root-directory), roundabout line 570 where if says "// get count of matching records". You may include some debugging and check the SQL conditions.

    Code: Select all

    var_dump($TempQuery);
    exit;
    
I hope one of these ideas for narrowing it down will give you the final hint.

Kind regards,
Jan
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

peebee
AppGini Super Hero
AppGini Super Hero
Posts: 330
Joined: 2013-03-21 04:37

Re: Using AppGini Filters + $options->QueryWhere = causes SQL syntax error?

Post by peebee » 2019-12-10 05:45

Thanks for the time in replying Jan.

The WHERE clause in the jobs_init() itself is not an issue in that it is working fine.

The problem arises when I try to apply any filter from the standard built in filter function.

So, the WHERE query in jobs_init() = working fine on it's own
Filters = working fine (prior to adding the WHERE clause)
Once I add the WHERE clause and combine it with any filter = MySQL syntax error?
  • Letter-casing is all fine
  • Permissions - I'm logged in as Admin with full permissions
  • No filter applied in the url - just jobs_view.php (and no pre-applied filters in effect).
  • No quick search term applied
  • No additional queiries in jobs_view.php (snippet below)

Code: Select all

	}elseif($perm[2]==0){ // view none
		$x->QueryFields = array("Not enough permissions" => "NEP");
		$x->QueryFrom = '`jobs`';
		$x->QueryWhere = '';
		$x->DefaultSortField = '';
	}
	// hook: jobs_init
	$render=TRUE;
	if(function_exists('jobs_init')){
		$args=array();
		$render=jobs_init($x, getMemberInfo(), $args);
	}

	if($render) $x->Render();


Haven't tried your datalist bebug yet but I will as soon as I have a chance. Thanks for your time. I'll keep persevering......

The strange thing is, I tried the exact same method in an unedited Appgini demo app from the Appgini downloads page and I experienced the same error?

peebee
AppGini Super Hero
AppGini Super Hero
Posts: 330
Joined: 2013-03-21 04:37

Re: Using AppGini Filters + $options->QueryWhere = causes SQL syntax error?

Post by peebee » 2019-12-16 01:10

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
1807462c-c465-4c27-8216-b7b86d185ec5.jpg (39.13 KiB) Viewed 16708 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?

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1497
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Using AppGini Filters + $options->QueryWhere = causes SQL syntax error?

Post by jsetzer » 2019-12-16 07:44

Good morning,

this is a wonderful complete bug-report! By setting up a new app you have removed any possible influence coming from other sources, well done!

I cannot see any error in your code, that looks fine.

Just to be 100% sure, I would put single quotes ' instead of double quotes " in your QueryWhere:

Code: Select all

replace 
$options->QueryWhere = 'WHERE `invoices`.`status` != "Unpaid"';

by
$options->QueryWhere = "WHERE `invoices`.`status` != 'Unpaid'";
Technical Background: Single quotes and double quotes have a different meaning in MySQL/MariaDB. I do not know your database-setup, but by default (and most common from my experience), strings have to be surrounded by 'single quotes', not "double quotes". It may be configured differently in your environment. For most of us single quotes should work fine.

As far as I can see, AppGini adds the additional bracket in Render()-method of DataList-class. This is inside datalist.php in your app's root directory. There is a variable $WhereNeedsClosing in line 475 which controls adding a closing bracket in line 540.

That whole PHP-code for concatenating SQL-where conditions is quite complex, as different filters from permissions, from init-hook, URL-parameters, search string and Filter-Page have to be merged into one big SQL-where-clause.

I guess line 536 ($WhereNeedsClosing = 1;) causes the additional bracket and I suggest debugging that Render()-method in your environment to find out why there is the closing-bracket but no opening bracket. I think the opening bracket should have been added in lines 488 or 490. Maybe you can insert var_dump($this->QueryWhere); in between the relevant code lines temporarily to see what's happening.

Kind regards,
Jan
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

peebee
AppGini Super Hero
AppGini Super Hero
Posts: 330
Joined: 2013-03-21 04:37

Re: Using AppGini Filters + $options->QueryWhere = causes SQL syntax error?

Post by peebee » 2019-12-17 02:29

Thanks again Jan.

Yes, I'd already tried single quotes and double quotes - same outcome.

I'd also tried injecting the WHERE clause straight into the invoices_view.php core file ($x->QueryWhere = ' ';) thinking that might get around the issue - same SQL error outcome with the superfluous closing bracket?

Obviously, my preference would be to get this to work without having to edit core files. I'm also trying to ensure that all other filters on tables without the WHERE query work as they should.

I'll have a bit more of a fiddle and report back with what I find.

peebee
AppGini Super Hero
AppGini Super Hero
Posts: 330
Joined: 2013-03-21 04:37

Re: Using AppGini Filters + $options->QueryWhere = causes SQL syntax error?

Post by peebee » 2019-12-17 02:49

Seems to me like this is an actual bug in Appgini rather than something I'm messing up?

I can easily resolve the issue when the WHERE clause is added to the invoices_init hook AND a filter is applied by simply adding an opening bracket to complete the query, like so:

$options->QueryWhere = "WHERE ((`invoices`.`status` != 'Unpaid')";

but of course that naturally causes a syntax error when NO filter is applied as there is no closing bracket?

I took a look into the "set query filters" functions in the datalist.php. Hmmmm, yes very complex as you say. Not something I really want to be fiddling with.

Might drop this in as a bug report and see if Ahmad might like to take a look/see if he can replicate?

peebee
AppGini Super Hero
AppGini Super Hero
Posts: 330
Joined: 2013-03-21 04:37

Re: Using AppGini Filters + $options->QueryWhere = causes SQL syntax error?

Post by peebee » 2019-12-17 06:51

The issue certainly relates to the code snippet below in datalist.php as you suggested Jan.

With a tiny bit of editing I can easily change the behaviour - add or remove the opening/closing parentheses depending on filters being applied or not and if $options->QueryWhere exists.

Sadly, I can only manage to get it working for one option or the other - not all options on all tables where no $options->QueryWhere applies.

There are just too many options for me to work out? I've submitted a support ticket. Hopefully Ahmad might be able to assist. :)

Code: Select all

	// set query filters
		$QueryHasWhere = 0;
		if(strpos($this->QueryWhere, 'where ')!==FALSE)
			$QueryHasWhere = 1;

		$WhereNeedsClosing = 0;
		for($i = 1; $i <= (datalist_filters_count * $FiltersPerGroup); $i+=$FiltersPerGroup){ // Number of filters allowed
			// test current filter group
			$GroupHasFilters = 0;
			for($j = 0; $j < $FiltersPerGroup; $j++){
				if($FilterField[$i+$j] != '' && $this->QueryFieldsIndexed[($FilterField[$i+$j])] != '' && $FilterOperator[$i+$j] != '' && ($FilterValue[$i+$j] != '' || strpos($FilterOperator[$i+$j], 'empty'))){
					$GroupHasFilters = 1;
					break;
				}
			}

			if($GroupHasFilters){
				if(!stristr($this->QueryWhere, "where "))
					$this->QueryWhere = "where (";
				elseif($QueryHasWhere){
					$this->QueryWhere .= " and (";
					$QueryHasWhere = 0;
				}

				$this->QueryWhere .= " <FilterGroup> " . $FilterAnd[$i] . " (";

				for($j = 0; $j < $FiltersPerGroup; $j++){
					if($FilterField[$i+$j] != '' && $this->QueryFieldsIndexed[($FilterField[$i+$j])] != '' && $FilterOperator[$i+$j] != '' && ($FilterValue[$i+$j] != '' || strpos($FilterOperator[$i+$j], 'empty'))){
						if($FilterAnd[$i+$j]==''){
							$FilterAnd[$i+$j]='and';
						}
						// test for date/time fields
						$tries = 0; $isDateTime = $isDate = false;
						$fieldName=str_replace('`', '', $this->QueryFieldsIndexed[($FilterField[$i+$j])]);
						list($tn, $fn)=explode('.', $fieldName);
						while(!($res = sql("show columns from `{$tn}` like '{$fn}'", $eo)) && $tries < 2){
							$tn=substr($tn, 0, -1);
							$tries++;
						}
						if($row = @db_fetch_array($res)){
							$isDateTime = in_array($row['Type'], array('date', 'time', 'datetime'));
							$isDate = in_array($row['Type'], array('date', 'datetime'));
						}
						// end of test
						if($FilterOperator[$i+$j]=='is-empty' && !$isDateTime){
							$this->QueryWhere .= " <FilterItem> " . $FilterAnd[$i+$j] . " (" . $this->QueryFieldsIndexed[($FilterField[$i+$j])] . "='' or " . $this->QueryFieldsIndexed[($FilterField[$i+$j])] . " is NULL) </FilterItem>";
						}elseif($FilterOperator[$i+$j]=='is-not-empty' && !$isDateTime){
							$this->QueryWhere .= " <FilterItem> " . $FilterAnd[$i+$j] . " " . $this->QueryFieldsIndexed[($FilterField[$i+$j])] . "!='' </FilterItem>";
						}elseif($FilterOperator[$i+$j]=='is-empty' && $isDateTime){
							$this->QueryWhere .= " <FilterItem> " . $FilterAnd[$i+$j] . " (" . $this->QueryFieldsIndexed[($FilterField[$i+$j])] . "=0 or " . $this->QueryFieldsIndexed[($FilterField[$i+$j])] . " is NULL) </FilterItem>";
						}elseif($FilterOperator[$i+$j]=='is-not-empty' && $isDateTime){
							$this->QueryWhere .= " <FilterItem> " . $FilterAnd[$i+$j] . " " . $this->QueryFieldsIndexed[($FilterField[$i+$j])] . "!=0 </FilterItem>";
						}elseif($FilterOperator[$i+$j]=='like' && !strstr($FilterValue[$i+$j], "%") && !strstr($FilterValue[$i+$j], "_")){
							$this->QueryWhere .= " <FilterItem> " . $FilterAnd[$i+$j] . " " . $this->QueryFieldsIndexed[($FilterField[$i+$j])] . " like '%" . makeSafe($FilterValue[$i+$j]) . "%' </FilterItem>";
						}elseif($FilterOperator[$i+$j]=='not-like' && !strstr($FilterValue[$i+$j], "%") && !strstr($FilterValue[$i+$j], "_")){
							$this->QueryWhere .= " <FilterItem> " . $FilterAnd[$i+$j] . " " . $this->QueryFieldsIndexed[($FilterField[$i+$j])] . " not like '%" . makeSafe($FilterValue[$i+$j]) . "%' </FilterItem>";
						}elseif($isDate){
							$dateValue = mysql_datetime($FilterValue[$i + $j]);
							$this->QueryWhere .= " <FilterItem> " . $FilterAnd[$i+$j] . " " . $this->QueryFieldsIndexed[($FilterField[$i+$j])] . " " . $GLOBALS['filter_operators'][$FilterOperator[$i+$j]] . " '$dateValue' </FilterItem>";
						}else{
							$this->QueryWhere .= " <FilterItem> " . $FilterAnd[$i+$j] . " " . $this->QueryFieldsIndexed[($FilterField[$i+$j])] . " " . $GLOBALS['filter_operators'][$FilterOperator[$i+$j]] . " '" . makeSafe($FilterValue[$i+$j]) . "' </FilterItem>";
						}
					}
				}

				$this->QueryWhere .= ") </FilterGroup>";
				$WhereNeedsClosing = 1;
			}
		}

		if($WhereNeedsClosing)
			$this->QueryWhere .= ")";

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1497
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Using AppGini Filters + $options->QueryWhere = causes SQL syntax error?

Post by jsetzer » 2019-12-17 07:01

Good morning @peebee,

I totally agree with you. The way you did it and your code looks fine. After narrowing it down to this point, this is something for Ahmed to look into.

I would appreciate it if you could post the results here.

With kind regards,
Jan
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

User avatar
a.gneady
Site Admin
Posts: 1199
Joined: 2012-09-27 14:46
Contact:

Re: Using AppGini Filters + $options->QueryWhere = causes SQL syntax error?

Post by a.gneady » 2019-12-17 16:48

Thanks for the deep investigation of this issue @peebee and for your looking into it @Jan.

I was able to reproduce the issue indeed and here is what I attempted for fixing it: In datalist.php, search for this line:

Code: Select all

// apply lookup filterers to the query
And add this code before it:

Code: Select all

$QueryHasCustomWhere = (strlen($this->QueryWhere) > 0);
Then, find this line:

Code: Select all

if($WhereNeedsClosing)
And change it to:

Code: Select all

if($WhereNeedsClosing && !$QueryHasCustomWhere)
I tested this when applying a simple, and a composite (multiple group) filter, with and without a quick search ... it seems to work fine in all these cases ... but please feel free to perform further tests and if all looks fine, please let me know to include this fix in the next release.
:idea: AppGini plugins to add more power to your apps:
  • Messages plugin: Make it easy for your app users to communicate securely without leaving the app or having to exchange emails/contact info.
  • Calendar plugin: Organize your data into beautiful calendars.
  • Mass Update plugin: Update multiple records at once and improve your workflow efficiency.
  • Summary Reports plugin: Build powerful reports and charts for your AppGini apps.
  • Search Page Maker: Compose user-friendly yet powerful search pages for your AppGini apps.
:arrow: Psst! Do you know that you can get a discount of up to 20% if you order 2 or more of our products?

peebee
AppGini Super Hero
AppGini Super Hero
Posts: 330
Joined: 2013-03-21 04:37

Re: Using AppGini Filters + $options->QueryWhere = causes SQL syntax error?

Post by peebee » 2019-12-17 22:10

Thanks for the speedy reply and fix Ahmed. Much appreciated, as always.

I'll have a test and report back.

Also good to see that I'm not actually going completely insane...... :)

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1056
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: Using AppGini Filters + $options->QueryWhere = causes SQL syntax error?

Post by onoehring » 2020-03-16 13:06

Hi Ahmed,

thank you very much for the fix.
I stumbled upon the same problem and your code worked for me as well.

I have installed AG 5.82 now but this fix is not applied in 5.82 yet ( :-( ... sad because the file is regenerated).

Olaf

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1056
Joined: 2019-05-21 22:42
Location: Germany
Contact:

5.93 still exists: Using AppGini Filters + $options->QueryWhere = causes SQL syntax error?

Post by onoehring » 2021-01-21 11:11

Hi,

this problem still exists in the latest version 5.93 and in the datatype.php file. :-(
Please fix it. Right now we must do the changes you described in the quickfix above ( viewtopic.php?f=2&t=3347&p=12525&hilit= ... ere#p11759) every time the application is regenerated.

Thank you
Olaf

User avatar
a.gneady
Site Admin
Posts: 1199
Joined: 2012-09-27 14:46
Contact:

Re: Using AppGini Filters + $options->QueryWhere = causes SQL syntax error?

Post by a.gneady » 2021-01-30 17:48

Hello Olaf,

I guess that thread got buried and I didn't see it until now. I'll apply the fix into the next release of AppGini.
:idea: AppGini plugins to add more power to your apps:
  • Messages plugin: Make it easy for your app users to communicate securely without leaving the app or having to exchange emails/contact info.
  • Calendar plugin: Organize your data into beautiful calendars.
  • Mass Update plugin: Update multiple records at once and improve your workflow efficiency.
  • Summary Reports plugin: Build powerful reports and charts for your AppGini apps.
  • Search Page Maker: Compose user-friendly yet powerful search pages for your AppGini apps.
:arrow: Psst! Do you know that you can get a discount of up to 20% if you order 2 or more of our products?

hernan
Posts: 26
Joined: 2020-06-22 18:56

Re: Using AppGini Filters + $options->QueryWhere = causes SQL syntax error?

Post by hernan » 2021-04-28 14:06

I had a similar problem.
AppGini version 5.95

My Tables with Custom Where and/ord Order showed an SQL Query error when filtering (with regular filter but also when using the SPM).
After reading this thread I checked the datalist.php file and making a small modification it works.

Original code:

Code: Select all

 // clean up query
	$this->QueryWhere = str_replace('( <FilterGroup> and ', '(', $this->QueryWhere);

Modified code:

Code: Select all

 // clean up query
	if($QueryHasCustomWhere){
		$this->QueryWhere = str_replace('( <FilterGroup> and ', '', $this->QueryWhere);
	} else {
		$this->QueryWhere = str_replace('( <FilterGroup> and ', '(', $this->QueryWhere);
	}
        //$this->QueryWhere = str_replace('( <FilterGroup> and ', '(', $this->QueryWhere); --> I just commented the replaced line.
I just check if there is a CustomWhere, using the $QueryHasCustomWhere variable.

It may be related to something else in my code... but doing this solved my problem and now filters work with tables with and without custom Where/Order in tablename_init() functions in hooks.

But I hope it could help someone else.
Bye!


pkumar
Posts: 4
Joined: 2021-05-01 22:52

Re: Using AppGini Filters + $options->QueryWhere = causes SQL syntax error?

Post by pkumar » 2021-05-11 13:15

Dear Sir

This code worked, thanks

// clean up query
if($QueryHasCustomWhere){
$this->QueryWhere = str_replace('( <FilterGroup> and ', '', $this->QueryWhere);
} else {
$this->QueryWhere = str_replace('( <FilterGroup> and ', '(', $this->QueryWhere);
}
//$this->QueryWhere = str_replace('( <FilterGroup> and ', '(', $this->QueryWhere); --> I just commented the replaced line.

prem

Post Reply