Accounts: Where is $where defined for search query, defaulting to 'Like%' type

[wrightee]

Hi: I'm having trouble digging my way through code to discover the point at which the variable $where is populated in Account.php,v 1.163.6.3. The issue I'm trying to fix is an error in the final search query that's created from advanced search.

If you search for an account_type only, the resulting query looks like this:

SELECT users.user_name assigned_user_name, accounts.* , accounts_cstm.* FROM accounts LEFT JOIN users ON accounts.assigned_user_id=users.id LEFT JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c where (accounts.account_type like '1%') AND accounts.deleted=0 ORDER BY name asc

We use numerical keys for our account type list, which means of course a 'Like '1%'' query gives us 1, 10,11,12... etc.

Defaulting to 'Like' makes sense for free text fields, but surely not for select boxes, returning bad data or at the very least slowing queries up a bit.

Anyway, if someone can tell me how to unlike my $where var, or at least which file to look in, I'd be very happy!

Thanks! Chris

[wagnerbl]

You can try modules/Accounts/ListView.php
Probably you have something like this on the code;
if(isset($account_type) && $account_type != "") array_push($where_clauses, "accounts.account_type Like '".PearDatabase::quote($account_type)."%'");
Change to
if(isset($account_type) && $account_type != "") array_push($where_clauses, "accounts.account_type = '".PearDatabase::quote($account_type)."'");

[wrightee]

Hi Wagnerbl - thanks for the tip. It didn't do it for me, I guess my version's different, but it did lead me eventually to function generate_search_where() in /include/utils.php that's causing the problem:

foreach ($field_list[$module] as $field=>$parms) 
	{
	if(isset($values[$field]) && $values[$field] != "") {
	$operator='like';
	if (!empty($parms['operator'])) 
	{$operator=$parms['operator'];}
...

.. so now if anyone can tell me where those custom override operators it's looking for in $parms are defined for the accounts table, I think we'll be rocking.

Thanks again for your help.

[wrightee]

After a long Sherlock through the code, I fixed it:

/modules/metadata/SearchFields.php

$searchFields['Accounts'] = ...
'account_type'=>array('query_type'=>'default','operator'=>'='), ...

Adding operators into the array changes the way each field is queried in utils.php