How to customise filter search condition to use subquery in sugar 7.x?

Question

hey champions,

As you know, back in sugar 6.x, we could edit the searchFields.php to add "subquery" to customise the search condition. which was quite flexible.

e.g.

'favorites_only' => 
array (
  'query_type' => 'format',
  'operator' => 'subquery',
  'subquery' => 'SELECT sugarfavorites.record_id FROM sugarfavorites 
                       WHERE sugarfavorites.deleted=0 
                           and sugarfavorites.module = \'Cases\' 
                           and sugarfavorites.assigned_user_id = \'{0}\'',

We have used this feature in many place in old sugar. After we move to sugar 7.8. we lost all these customisation.

All we can do right now seem just add custom fields to$viewdefs['Cases']['base']['filter']['default'] to enable particular field as additional filter. 

My questions is how could we continue use the "subquery" operator in sugar 7.x so that we could join any database table to do complex search?

many thanks.

Ron

Ron Lesh · Accepted Answer

Hi Ron Lesh There is no way to achieve that through filter metadata. Instead you have to extends the class FilterApi (clients/base/api/FilterApi.php). Inside that class take a look at the method "addFavoriteFilter" (it refers to $favorite defined at custom/modules/<ModuleName>/clients/base/filters/default/default.php). In the inherited class you have to override the method "addFilters" the way you can inject some joins/raw sql into SugarQuery $q object according to a new filter definition you are going to create. At last you can modify your filter definition in order to specify the new filter definition. Kind regards