2 if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');
3 /*********************************************************************************
4 * SugarCRM Community Edition is a customer relationship management program developed by
5 * SugarCRM, Inc. Copyright (C) 2004-2013 SugarCRM Inc.
7 * This program is free software; you can redistribute it and/or modify it under
8 * the terms of the GNU Affero General Public License version 3 as published by the
9 * Free Software Foundation with the addition of the following permission added
10 * to Section 15 as permitted in Section 7(a): FOR ANY PART OF THE COVERED WORK
11 * IN WHICH THE COPYRIGHT IS OWNED BY SUGARCRM, SUGARCRM DISCLAIMS THE WARRANTY
12 * OF NON INFRINGEMENT OF THIRD PARTY RIGHTS.
14 * This program is distributed in the hope that it will be useful, but WITHOUT
15 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
16 * FOR A PARTICULAR PURPOSE. See the GNU Affero General Public License for more
19 * You should have received a copy of the GNU Affero General Public License along with
20 * this program; if not, see http://www.gnu.org/licenses or write to the Free
21 * Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
24 * You can contact SugarCRM, Inc. headquarters at 10050 North Wolfe Road,
25 * SW2-130, Cupertino, CA 95014, USA. or at email address contact@sugarcrm.com.
27 * The interactive user interfaces in modified source and object code versions
28 * of this program must display Appropriate Legal Notices, as required under
29 * Section 5 of the GNU Affero General Public License version 3.
31 * In accordance with Section 7(b) of the GNU Affero General Public License version 3,
32 * these Appropriate Legal Notices must retain the display of the "Powered by
33 * SugarCRM" logo. If the display of the logo is not reasonably feasible for
34 * technical reasons, the Appropriate Legal Notices must display the words
35 * "Powered by SugarCRM".
36 ********************************************************************************/
39 require_once("data/Relationships/SugarRelationship.php");
42 * Represents a many to many relationship that is table based.
45 class M2MRelationship extends SugarRelationship
47 var $type = "many-to-many";
49 public function __construct($def)
52 $this->name = $def['name'];
54 $lhsModule = $def['lhs_module'];
55 $this->lhsLinkDef = $this->getLinkedDefForModuleByRelationship($lhsModule);
56 $this->lhsLink = $this->lhsLinkDef['name'];
58 $rhsModule = $def['rhs_module'];
59 $this->rhsLinkDef = $this->getLinkedDefForModuleByRelationship($rhsModule);
60 $this->rhsLink = $this->rhsLinkDef['name'];
62 $this->self_referencing = $lhsModule == $rhsModule;
66 * Find the link entry for a particular relationship and module.
71 public function getLinkedDefForModuleByRelationship($module)
73 $results = VardefManager::getLinkFieldForRelationship( $module, BeanFactory::getObjectName($module), $this->name);
74 //Only a single link was found
75 if( isset($results['name']) )
79 //Multiple links with same relationship name
80 else if( is_array($results) )
82 $GLOBALS['log']->error("Warning: Multiple links found for relationship {$this->name} within module {$module}");
83 return $this->getMostAppropriateLinkedDefinition($results);
92 * Find the most 'appropriate' link entry for a relationship/module in which there are multiple link entries with the
93 * same relationship name.
98 protected function getMostAppropriateLinkedDefinition($links)
100 //First priority is to find a link name that matches the relationship name
101 foreach($links as $link)
103 if( isset($link['name']) && $link['name'] == $this->name )
108 //Next would be a relationship that has a side defined
109 foreach($links as $link)
111 if( isset($link['id_name']))
116 //Unable to find an appropriate link, guess and use the first one
117 $GLOBALS['log']->error("Unable to determine best appropriate link for relationship {$this->name}");
121 * @param $lhs SugarBean left side bean to add to the relationship.
122 * @param $rhs SugarBean right side bean to add to the relationship.
123 * @param $additionalFields key=>value pairs of fields to save on the relationship
124 * @return boolean true if successful
126 public function add($lhs, $rhs, $additionalFields = array())
128 $lhsLinkName = $this->lhsLink;
129 $rhsLinkName = $this->rhsLink;
131 if (empty($lhs->$lhsLinkName) && !$lhs->load_relationship($lhsLinkName))
133 $lhsClass = get_class($lhs);
134 $GLOBALS['log']->fatal("could not load LHS $lhsLinkName in $lhsClass");
137 if (empty($rhs->$rhsLinkName) && !$rhs->load_relationship($rhsLinkName))
139 $rhsClass = get_class($rhs);
140 $GLOBALS['log']->fatal("could not load RHS $rhsLinkName in $rhsClass");
144 $lhs->$lhsLinkName->addBean($rhs);
145 $rhs->$rhsLinkName->addBean($lhs);
147 $this->callBeforeAdd($lhs, $rhs, $lhsLinkName);
148 $this->callBeforeAdd($rhs, $lhs, $rhsLinkName);
150 //Many to many has no additional logic, so just add a new row to the table and notify the beans.
151 $dataToInsert = $this->getRowToInsert($lhs, $rhs, $additionalFields);
153 $this->addRow($dataToInsert);
155 if ($this->self_referencing)
156 $this->addSelfReferencing($lhs, $rhs, $additionalFields);
158 $lhs->$lhsLinkName->addBean($rhs);
159 $rhs->$rhsLinkName->addBean($lhs);
161 $this->callAfterAdd($lhs, $rhs, $lhsLinkName);
162 $this->callAfterAdd($rhs, $lhs, $rhsLinkName);
167 protected function getRowToInsert($lhs, $rhs, $additionalFields = array())
170 "id" => create_guid(),
171 $this->def['join_key_lhs'] => $lhs->id,
172 $this->def['join_key_rhs'] => $rhs->id,
173 'date_modified' => TimeDate::getInstance()->nowDb(),
178 if (!empty($this->def['relationship_role_column']) && !empty($this->def['relationship_role_column_value']) && !$this->ignore_role_filter )
180 $row[$this->relationship_role_column] = $this->relationship_role_column_value;
183 if (!empty($this->def['fields']))
185 foreach($this->def['fields'] as $fieldDef)
187 if (!empty($fieldDef['name']) && !isset($row[$fieldDef['name']]) && !empty($fieldDef['default']))
189 $row[$fieldDef['name']] = $fieldDef['default'];
193 if (!empty($additionalFields))
195 $row = array_merge($row, $additionalFields);
202 * Adds the reversed version of this relationship to the table so that it can be accessed from either side equally
205 * @param array $additionalFields
208 protected function addSelfReferencing($lhs, $rhs, $additionalFields = array())
210 if ($rhs->id != $lhs->id)
212 $dataToInsert = $this->getRowToInsert($rhs, $lhs, $additionalFields);
213 $this->addRow($dataToInsert);
217 public function remove($lhs, $rhs)
219 if(!($lhs instanceof SugarBean) || !($rhs instanceof SugarBean)) {
220 $GLOBALS['log']->fatal("LHS and RHS must be beans");
223 $lhsLinkName = $this->lhsLink;
224 $rhsLinkName = $this->rhsLink;
226 if (!($lhs instanceof SugarBean)) {
227 $GLOBALS['log']->fatal("LHS is not a SugarBean object");
230 if (!($rhs instanceof SugarBean)) {
231 $GLOBALS['log']->fatal("RHS is not a SugarBean object");
234 if (empty($lhs->$lhsLinkName) && !$lhs->load_relationship($lhsLinkName))
236 $GLOBALS['log']->fatal("could not load LHS $lhsLinkName");
239 if (empty($rhs->$rhsLinkName) && !$rhs->load_relationship($rhsLinkName))
241 $GLOBALS['log']->fatal("could not load RHS $rhsLinkName");
245 if (empty($_SESSION['disable_workflow']) || $_SESSION['disable_workflow'] != "Yes")
247 if ($lhs->$lhsLinkName instanceof Link2)
249 $lhs->$lhsLinkName->load();
250 $this->callBeforeDelete($lhs, $rhs, $lhsLinkName);
253 if ($rhs->$rhsLinkName instanceof Link2)
255 $rhs->$rhsLinkName->load();
256 $this->callBeforeDelete($rhs, $lhs, $rhsLinkName);
260 $dataToRemove = array(
261 $this->def['join_key_lhs'] => $lhs->id,
262 $this->def['join_key_rhs'] => $rhs->id
265 $this->removeRow($dataToRemove);
267 if ($this->self_referencing)
268 $this->removeSelfReferencing($lhs, $rhs);
270 if (empty($_SESSION['disable_workflow']) || $_SESSION['disable_workflow'] != "Yes")
272 if ($lhs->$lhsLinkName instanceof Link2)
274 $lhs->$lhsLinkName->load();
275 $this->callAfterDelete($lhs, $rhs, $lhsLinkName);
278 if ($rhs->$rhsLinkName instanceof Link2)
280 $rhs->$rhsLinkName->load();
281 $this->callAfterDelete($rhs, $lhs, $rhsLinkName);
289 * Removes the reversed version of this relationship
292 * @param array $additionalFields
295 protected function removeSelfReferencing($lhs, $rhs, $additionalFields = array())
297 if ($rhs->id != $lhs->id)
299 $dataToRemove = array(
300 $this->def['join_key_lhs'] => $rhs->id,
301 $this->def['join_key_rhs'] => $lhs->id
303 $this->removeRow($dataToRemove);
308 * @param $link Link2 loads the relationship for this link.
311 public function load($link, $params = array())
313 $db = DBManagerFactory::getInstance();
314 $query = $this->getQuery($link, $params);
315 $result = $db->query($query);
317 $idField = $link->getSide() == REL_LHS ? $this->def['join_key_rhs'] : $this->def['join_key_lhs'];
318 while ($row = $db->fetchByAssoc($result, FALSE))
320 if (empty($row['id']) && empty($row[$idField]))
322 $id = empty($row['id']) ? $row[$idField] : $row['id'];
325 return array("rows" => $rows);
328 protected function linkIsLHS($link) {
329 return $link->getSide() == REL_LHS;
332 public function getQuery($link, $params = array())
334 if ($this->linkIsLHS($link)) {
335 $knownKey = $this->def['join_key_lhs'];
336 $targetKey = $this->def['join_key_rhs'];
337 $relatedSeed = BeanFactory::getBean($this->getRHSModule());
338 $relatedSeedKey = $this->def['rhs_key'];
339 if (!empty($params['where']))
340 $whereTable = (empty($params['right_join_table_alias']) ? $relatedSeed->table_name : $params['right_join_table_alias']);
344 $knownKey = $this->def['join_key_rhs'];
345 $targetKey = $this->def['join_key_lhs'];
346 $relatedSeed = BeanFactory::getBean($this->getLHSModule());
347 $relatedSeedKey = $this->def['lhs_key'];
348 if (!empty($params['where']))
349 $whereTable = (empty($params['left_join_table_alias']) ? $relatedSeed->table_name : $params['left_join_table_alias']);
351 $rel_table = $this->getRelationshipTable();
353 $where = "$rel_table.$knownKey = '{$link->getFocus()->id}'" . $this->getRoleWhere();
355 //Add any optional where clause
356 if (!empty($params['where'])){
357 $add_where = is_string($params['where']) ? $params['where'] : "$whereTable." . $this->getOptionalWhereClause($params['where']);
358 if (!empty($add_where))
359 $where .= " AND $rel_table.$targetKey=$whereTable.id AND $add_where";
362 $deleted = !empty($params['deleted']) ? 1 : 0;
363 $from = $rel_table . " ";
364 if (!empty($params['where'])) {
365 $from .= ", $whereTable";
366 if (isset($relatedSeed->custom_fields)) {
367 $customJoin = $relatedSeed->custom_fields->getJOIN();
368 $from .= $customJoin ? $customJoin['join'] : '';
372 if (empty($params['return_as_array'])) {
373 $query = "SELECT $targetKey id FROM $from WHERE $where AND $rel_table.deleted=$deleted";
374 //Limit is not compatible with return_as_array
375 if (!empty($params['limit']) && $params['limit'] > 0) {
376 $offset = isset($params['offset']) ? $params['offset'] : 0;
377 $query = DBManagerFactory::getInstance()->limitQuery($query, $offset, $params['limit'], false, "", false);
384 'select' => "SELECT $targetKey id",
385 'from' => "FROM $from",
386 'where' => "WHERE $where AND $rel_table.deleted=$deleted",
391 public function getJoin($link, $params = array(), $return_array = false)
393 $linkIsLHS = $link->getSide() == REL_LHS;
395 $startingTable = (empty($params['left_join_table_alias']) ? $link->getFocus()->table_name : $params['left_join_table_alias']);
397 $startingTable = (empty($params['right_join_table_alias']) ? $link->getFocus()->table_name : $params['right_join_table_alias']);
400 $startingKey = $linkIsLHS ? $this->def['lhs_key'] : $this->def['rhs_key'];
401 $startingJoinKey = $linkIsLHS ? $this->def['join_key_lhs'] : $this->def['join_key_rhs'];
402 $joinTable = $this->getRelationshipTable();
403 $joinTableWithAlias = $joinTable;
404 $joinKey = $linkIsLHS ? $this->def['join_key_rhs'] : $this->def['join_key_lhs'];
405 $targetTable = $linkIsLHS ? $this->def['rhs_table'] : $this->def['lhs_table'];
406 $targetTableWithAlias = $targetTable;
407 $targetKey = $linkIsLHS ? $this->def['rhs_key'] : $this->def['lhs_key'];
408 $join_type= isset($params['join_type']) ? $params['join_type'] : ' INNER JOIN ';
412 //Set up any table aliases required
413 if (!empty($params['join_table_link_alias']))
415 $joinTableWithAlias = $joinTable . " ". $params['join_table_link_alias'];
416 $joinTable = $params['join_table_link_alias'];
418 if ( ! empty($params['join_table_alias']))
420 $targetTableWithAlias = $targetTable . " ". $params['join_table_alias'];
421 $targetTable = $params['join_table_alias'];
424 $join1 = "$startingTable.$startingKey=$joinTable.$startingJoinKey";
425 $join2 = "$targetTable.$targetKey=$joinTable.$joinKey";
429 //First join the relationship table
430 $join .= "$join_type $joinTableWithAlias ON $join1 AND $joinTable.deleted=0\n"
431 //Next add any role filters
432 . $this->getRoleWhere($joinTable) . "\n"
433 //Then finally join the related module's table
434 . "$join_type $targetTableWithAlias ON $join2 AND $targetTable.deleted=0\n";
439 'type' => $this->type,
440 'rel_key' => $joinKey,
441 'join_tables' => array($joinTable, $targetTable),
443 'select' => "$targetTable.id",
446 return $join . $where;
450 * Similar to getQuery or Get join, except this time we are starting from the related table and
451 * searching for items with id's matching the $link->focus->id
453 * @param array $params
454 * @param bool $return_array
455 * @return String|Array
457 public function getSubpanelQuery($link, $params = array(), $return_array = false)
459 $targetIsLHS = $link->getSide() == REL_RHS;
460 $startingTable = $targetIsLHS ? $this->def['lhs_table'] : $this->def['rhs_table'];;
461 $startingKey = $targetIsLHS ? $this->def['lhs_key'] : $this->def['rhs_key'];
462 $startingJoinKey = $targetIsLHS ? $this->def['join_key_lhs'] : $this->def['join_key_rhs'];
463 $joinTable = $this->getRelationshipTable();
464 $joinTableWithAlias = $joinTable;
465 $joinKey = $targetIsLHS ? $this->def['join_key_rhs'] : $this->def['join_key_lhs'];
466 $targetKey = $targetIsLHS ? $this->def['rhs_key'] : $this->def['lhs_key'];
467 $join_type= isset($params['join_type']) ? $params['join_type'] : ' INNER JOIN ';
471 //Set up any table aliases required
472 if (!empty($params['join_table_link_alias']))
474 $joinTableWithAlias = $joinTable . " ". $params['join_table_link_alias'];
475 $joinTable = $params['join_table_link_alias'];
478 $where = "$startingTable.$startingKey=$joinTable.$startingJoinKey AND $joinTable.$joinKey='{$link->getFocus()->$targetKey}'";
480 //Check if we should ignore the role filter.
481 $ignoreRole = !empty($params['ignore_role']);
483 //First join the relationship table
484 $query .= "$join_type $joinTableWithAlias ON $where AND $joinTable.deleted=0\n"
485 //Next add any role filters
486 . $this->getRoleWhere($joinTable, $ignoreRole) . "\n";
488 if (!empty($params['return_as_array'])) {
489 $return_array = true;
494 'type' => $this->type,
495 'rel_key' => $joinKey,
496 'join_tables' => array($joinTable),
505 protected function getRoleFilterForJoin()
508 if (!empty($this->relationship_role_column) && !$this->ignore_role_filter)
510 $ret .= " AND ".$this->getRelationshipTable().'.'.$this->relationship_role_column;
512 if (empty($this->relationship_role_column_value))
516 $ret.= "='".$this->relationship_role_column_value."'";
528 public function relationship_exists($lhs, $rhs)
530 $query = "SELECT id FROM {$this->getRelationshipTable()} WHERE {$this->join_key_lhs} = '{$lhs->id}' AND {$this->join_key_rhs} = '{$rhs->id}'";
532 //Roles can allow for multiple links between two records with different roles
533 $query .= $this->getRoleWhere() . " and deleted = 0";
535 return $GLOBALS['db']->getOne($query);
539 * @return Array - set of fields that uniquely identify an entry in this relationship
541 protected function getAlternateKeyFields()
543 $fields = array($this->join_key_lhs, $this->join_key_rhs);
545 //Roles can allow for multiple links between two records with different roles
546 if (!empty($this->def['relationship_role_column']) && !$this->ignore_role_filter)
548 $fields[] = $this->relationship_role_column;
554 public function getRelationshipTable()
556 if (!empty($this->def['table']))
557 return $this->def['table'];
558 else if(!empty($this->def['join_table']))
559 return $this->def['join_table'];
564 public function getFields()
566 if (!empty($this->def['fields']))
567 return $this->def['fields'];
569 "id" => array('name' => 'id'),
570 'date_modified' => array('name' => 'date_modified'),
571 'modified_user_id' => array('name' => 'modified_user_id'),
572 'created_by' => array('name' => 'created_by'),
573 $this->def['join_key_lhs'] => array('name' => $this->def['join_key_lhs']),
574 $this->def['join_key_rhs'] => array('name' => $this->def['join_key_rhs'])
576 if (!empty($this->def['relationship_role_column']))
578 $fields[$this->def['relationship_role_column']] = array("name" => $this->def['relationship_role_column']);
580 $fields['deleted'] = array('name' => 'deleted');