Forums - Thread Details
  • State Verified Answer
  • Replies 3 replies
  • Subscribers 320 subscribers
  • Views 1921 views
  • Users 0 members are here
Forum Actions
Related

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

Ron Lesh

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
  • 0

    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

    André Lopes
    Lampada Global
    Skype: andre.lampada
  • 0 in reply to André Lopes

    Hey André,

    Thanks again for your help. Seems not easy. but I definitely will find a time and try it out.

    This sounds like a solution to "add a custom filter which does join/saw sql". However, what if i want to customise the search field, which could be created / added as one of the condition?

    e.g "Assigned To".

    currently, it only has the option of "is any of" and "is not any of". how could i customise it to have the option of "is not empty"? 

    e.g. "Open Only"

    'open_only' =>
    array (
    'dbFields' =>
    array (
    0 => 'status',
    ),
    'type' => 'bool',
    'vname' => 'LBL_OPEN_ITEMS',
    ),

    the option is "is true or false". i understand it should be like this. however, because back in years ago, we completely changed the value of "case_status_dom". i am guessing by default, when it is selected as true, sugar would try to look for status which is one of/ is not one of those stock status value. In our case, i would need to change it to target our custom status.

    thank you very much.

    regards,

    Ron

  • 0 in reply to Ron Lesh

    Hello

    Did you find any solution to this?

    Regards, 

    Angel M.