We all know the moment when you are roaming through 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 derivative 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:– SugarCRM Cookbook – The School of REST – Part 3
– Sugar 7.8 Developer Guide - Architecture - 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.clients/base/api/FilterApi.php
<?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 used the HTTP client provided by Postman Chrome Extension and the demo dataset for Sugar PRO 7.8.0.0.
My first need: I wanted 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 » constructions 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 augments the query to join additional tables. As highlighted in the FIXME comment, the « many » side of a relationship can produce duplicate entries in the results. Actually, Sugar could remove these duplicates manually, but it will have a cost and it will not prevent huge transfer of result set data from the database. So before executing query, the generated SugarQuery is inspected and modified to include a DISTINCT option in order to reduce data result size to unique values:MySQL
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 will illustrate what Sugar does:data/SugarBean.php
<?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 many rows to compute. If you run into this problem, then you will have to optimize your SugarQuery or simply find another way to get the results you need.
Conclusion
Keep in mind that using this feature can generate heavy load on your database server, especially if you have no idea of what is going on behind your query. Also, since it is not a public method, we can’t guess if SugarCRM is going to make some updates on the verifyField method 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 relationships. So keep into digging some of their great features for more efficient results or just to satisfy your own curiosity.
