SugarCRM - Synolab

SugarCRM – Curiosity on FilterApi and relate filter

Par Yann B. le 9 février 2017

We all know this moment when you are roaming on source code to find something for a particular purpose and you come across that feature you didn’t expect but you absolutely want to test. This is how I came into the related link filter feature. What do I mean by related link filter? It is a derived way to filter related data on a One-to-Many relationship by specifying a link name and a target field:

I want all Contacts filtered on their related Account with « Industry » value « Electronics » (use « one » side of the relationship)
I want all Accounts filtered on their related Contacts with « Title » value « President » (use « many » side of the relationship)

This is an advanced use of Sugar 7 Filter API, if you never used it before, have a look at this documentation for detailed information about how filters work:
– https://developer.sugarcrm.com/2014/02/28/sugarcrm-cookbook3/
– http://support.sugarcrm.com/Documentation/Sugar_Developer/Sugar_Developer_Guide_7.6/UI_Model/Views/Filters/
Examples and tests have been made with a Sugar instance PRO 7.8.0.0

How does it work?

First of all, we have to look at the source code that manages this capability to understand how we will build our filter.

The method verifyField has a specific behavior for field names having a dot character inside. The field name passed is parsed has « link ». »field » to match an existing link on the current module requested. There are 2 things to remember here: using any related field of a link is allowed (so not only « name » or « id » as vardef definition have for « relate » type fields, e.g. « account_id » and « account_name ») and using « many » side of a link is tolerated.

We will come back later on the FIXME, let’s practice now how to fill these conditions for filtering results.

How to use relate filter with FilterApi ?

For my examples, I use a HTTP client provided by Postman Chrome Extension and demo dataset of Sugar PRO 7.8.0.0.

My first need was: I want all Contacts filtered on their related Account with « Industry » value « Electronics ». Let’s have a look on how to implement it with a relate filter.

sugar_filter_2 - synolab

 

The second test is with the « many » side of a relationship: I want all Accounts filtered on their related Contacts with « Title » value « President »

sugar_filter_1 - synolab

 

Do not forget that such filter can be combined with other ones with « $and » and « $or » imbrication or you can use more complex logic with operators provided for targeted field types (« $in », « $starts », …).

So what happens when using a relate filter on the « many » side ?

Adding related data include augmenting query with some joined tables. As highlighted in the FIXME comment, the « many » side of a relationship can produce duplicate entries in the results. Actually, Sugar can prevent this case by managing an applicative « distinct compensation », but it will have a cost and it will not prevent huge transfer of data from the database. So before executing query, the SugarQuery generated is inspected and fixed to include DISTINCT option in order to reduce data result size:

Let me highlight some source code that can maybe illustrate what Sugar does:

Using DISTINCT is not always a good way to prevent duplicate results, especially when there is not enough memory allocated to the database server or too much line to compute. You will have to optimize your SugarQuery or simply find another way to filter.

Conclusion

Keep in mind that what you want to achieve by using this trick can generate much overload on your database server, especially if you have no idea of what is going on backward your query. Also, we can’t guess if SugarCRM is going to make some updates on the verifyField function to handle the « many » side differently (there is explicitly the commented line throw new SugarApiExceptionInvalidParameter("Cannot use condition against multi-link $linkName"); ).

Sugar 7 provides many tools to deal with problematic relationship. So keep digging some of their great features for more efficient results or just for your own curiosity.

LinkedIn Google+ Email
Yann B.

Développeur backend dans l'âme, je m’intéresse à ce qui touche au PHP, les technologies JS et les frameworks du moment qui façonnent le Web.

GIF