Lecture 4 minutes
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)
- 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
<?php ... protected static function verifyField(SugarQuery $q, $field) { $ret = array(); if (strpos($field, '.')) { // It looks like it's a related field that it's searching by list($linkName, $field) = explode('.', $field); $q->from->load_relationship($linkName); if(empty($q->from->$linkName)) { throw new SugarApiExceptionInvalidParameter("Invalid link $linkName for field $field"); } if($q->from->$linkName->getType() == "many") { // FIXME TY-1192: we have a problem here: we should allow 'many' links for related to match against // parent object but allowing 'many' in other links may lead to duplicates. So for now we allow 'many' // but we should figure out how to find if 'many' is permittable or not. // throw new SugarApiExceptionInvalidParameter("Cannot use condition against multi-link $linkName"); } $join = $q->join($linkName, array('joinType' => 'LEFT')); $table = $join->joinName(); $ret['field'] = "$table.$field"; $bean = $q->getTableBean($table); if (empty($bean)) $bean = $q->getTableBean($linkName); if (empty($bean) && $q->getFromBean() && $q->getFromBean()->$linkName) $bean = BeanFactory::getBean($q->getFromBean()->$linkName->getRelatedModuleName()); if(empty($bean)) { throw new SugarApiExceptionInvalidParameter("Cannot use condition against $linkName - unknown module"); } } else { $bean = $q->from; } ... } ... |
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.
The second test is with the "many" side of a relationship: I want all Accounts filtered on their related Contacts with "Title" value "President"
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT DISTINCT accounts.name name, accounts.id id, accounts.date_modified date_modified, accounts.assigned_user_id assigned_user_id, accounts.created_by created_by FROM accounts INNER JOIN( SELECT tst.team_set_id FROM team_sets_teams tst INNER JOIN team_memberships team_memberships ON tst.team_id = team_memberships.team_id AND team_memberships.user_id = 'seed_will_id' AND team_memberships.deleted = 0 GROUP BY tst.team_set_id ) accounts_tf ON accounts_tf.team_set_id = accounts.team_set_id LEFT JOIN accounts_contacts jt1_accounts_contacts ON(accounts.id = jt1_accounts_contacts.account_id AND jt1_accounts_contacts.deleted = 0) LEFT JOIN contacts jt0_contacts ON(jt0_contacts.id = jt1_accounts_contacts.contact_id AND jt0_contacts.deleted = 0 AND(jt0_contacts.team_set_id IN( SELECT tst.team_set_id FROM team_sets_teams tst INNER JOIN team_memberships team_membershipsjt0_contacts ON tst.team_id = team_membershipsjt0_contacts.team_id AND team_membershipsjt0_contacts.user_id = 'seed_will_id' AND team_membershipsjt0_contacts.deleted = 0))) WHERE accounts.deleted = 0 AND jt0_contacts.title = 'President' ORDER BY accounts.date_modified DESC, accounts.id DESC LIMIT 0, 21 |
Let me highlight some source code that can maybe illustrate what Sugar does:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
<?php ... function fetchFromQuery(SugarQuery $query, array $fields = array(), array $options = array()) { ... if ($this->queryProducesDuplicates($query)) { $this->fixQuery($query); } ... } ... protected function queryProducesDuplicates(SugarQuery $query) { foreach ($query->join as $join) { if ($join->linkName) { $seed = $query->from; $linkName = $join->linkName; if ($seed->load_relationship($linkName)) { /** @var Link2 $link */ $link = $seed->$linkName; if ($link->getType() === REL_TYPE_MANY) { $relationship = $link->getRelationshipObject(); if (empty($relationship->primaryOnly)) { return true; } } } } } return false; } ... protected function fixQuery(SugarQuery $query) { foreach ($query->select->select as $field) { if ($field->table) { $bean = $query->getTableBean($field->table); if (!$bean) { $bean = $query->from; } $def = $bean->getFieldDefinition($field->field); $type = $this->db->getFieldType($def); if ($this->db->isTextType($type)) { $GLOBALS['log']->warn('Unable to fix the query containing text field'); return; } } } $query->distinct(true); } ... |
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.
