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-2011 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.
44 class M2MRelationship extends SugarRelationship
46 var $type = "many-to-many";
48 public function __construct($def)
53 $this->name = $def['name'];
55 $lhsModule = $def['lhs_module'];
56 $this->lhsLinkDef = VardefManager::getLinkFieldForRelationship(
57 $lhsModule, BeanFactory::getBeanName($lhsModule), $this->name
59 $this->lhsLink = $this->lhsLinkDef['name'];
61 $rhsModule = $def['rhs_module'];
62 $this->rhsLinkDef = VardefManager::getLinkFieldForRelationship(
63 $rhsModule, BeanFactory::getBeanName($rhsModule), $this->name
65 $this->rhsLink = $this->rhsLinkDef['name'];
67 $this->self_referencing = $lhsModule == $rhsModule;
71 * @param $lhs SugarBean left side bean to add to the relationship.
72 * @param $rhs SugarBean right side bean to add to the relationship.
73 * @param $additionalFields key=>value pairs of fields to save on the relationship
74 * @return boolean true if successful
76 public function add($lhs, $rhs, $additionalFields = array())
78 $lhsLinkName = $this->lhsLink;
79 $rhsLinkName = $this->rhsLink;
81 if (empty($lhs->$lhsLinkName) && !$lhs->load_relationship($lhsLinkName))
83 $lhsClass = get_class($lhs);
84 $GLOBALS['log']->fatal("could not load LHS $lhsLinkName in $lhsClass");
87 if (empty($rhs->$rhsLinkName) && !$rhs->load_relationship($rhsLinkName))
89 $rhsClass = get_class($rhs);
90 $GLOBALS['log']->fatal("could not load RHS $rhsLinkName in $rhsClass");
94 //Many to many has no additional logic, so just add a new row to the table and notify the beans.
95 $dataToInsert = $this->getRowToInsert($lhs, $rhs);
96 $dataToInsert = array_merge($dataToInsert, $additionalFields);
98 $this->addRow($dataToInsert);
100 if (empty($_SESSION['disable_workflow']) || $_SESSION['disable_workflow'] != "Yes")
102 $lhs->$lhsLinkName->addBean($rhs);
103 $rhs->$rhsLinkName->addBean($lhs);
105 $this->callAfterAdd($lhs, $rhs, $lhsLinkName);
106 $this->callAfterAdd($rhs, $lhs, $rhsLinkName);
110 protected function getRowToInsert($lhs, $rhs)
113 "id" => create_guid(),
114 $this->def['join_key_lhs'] => $lhs->id,
115 $this->def['join_key_rhs'] => $rhs->id,
116 'date_modified' => TimeDate::getInstance()->getNow()->asDb(),
121 if (!empty($this->def['relationship_role_column']) && !empty($this->def['relationship_role_column_value']) && !$this->ignore_role_filter )
123 $row[$this->relationship_role_column] = $this->relationship_role_column_value;
126 foreach($this->def['fields'] as $fieldDef)
128 if (!empty($fieldDef['name']) && !isset($row[$fieldDef['name']]) && !empty($fieldDef['default']))
130 $row[$fieldDef['name']] = $fieldDef['default'];
138 public function remove($lhs, $rhs)
140 $lhsLinkName = $this->lhsLink;
141 $rhsLinkName = $this->rhsLink;
143 if (!($lhs instanceof SugarBean)) {
144 $GLOBALS['log']->fatal("LHS is not a SugarBean object");
147 if (!($rhs instanceof SugarBean)) {
148 $GLOBALS['log']->fatal("RHS is not a SugarBean object");
151 if (empty($lhs->$lhsLinkName) && !$lhs->load_relationship($lhsLinkName))
153 $GLOBALS['log']->fatal("could not load LHS $lhsLinkName");
156 if (empty($rhs->$rhsLinkName) && !$rhs->load_relationship($rhsLinkName))
158 $GLOBALS['log']->fatal("could not load RHS $rhsLinkName");
162 $dataToRemove = array(
163 $this->def['join_key_lhs'] => $lhs->id,
164 $this->def['join_key_rhs'] => $rhs->id
167 $this->removeRow($dataToRemove);
169 if (empty($_SESSION['disable_workflow']) || $_SESSION['disable_workflow'] != "Yes")
171 $lhs->$lhsLinkName->load();
172 $rhs->$rhsLinkName->load();
174 $this->callAfterDelete($lhs, $rhs, $lhsLinkName);
175 $this->callAfterDelete($rhs, $lhs, $rhsLinkName);
180 * @param $link Link2 loads the relationship for this link.
183 public function load($link)
185 $db = DBManagerFactory::getInstance();
186 $query = $this->getQuery($link);
187 $result = $db->query($query);
190 $relatedModule = $link->getSide() == REL_LHS ? $this->def['rhs_module'] : $this->def['lhs_module'];
191 $idField = $link->getSide() == REL_LHS ? $this->def['join_key_rhs'] : $this->def['join_key_lhs'];
192 while ($row = $db->fetchByAssoc($result))
194 $id = $row[$idField];
197 return array("rows" => $rows);
200 public function getQuery($link, $params = array())
202 if ($link->getSide() == REL_LHS) {
203 $knownKey = $this->def['join_key_lhs'];
204 $targetKey = $this->def['join_key_rhs'];
208 $knownKey = $this->def['join_key_rhs'];
209 $targetKey = $this->def['join_key_lhs'];
211 $rel_table = $this->getRelationshipTable();
213 if (!$this->self_referencing)
215 $where = "$rel_table.$knownKey = '{$link->getFocus()->id}'";
219 $where = "($rel_table.{$this->def['join_key_rhs']} = '{$link->getFocus()->id}' OR $rel_table.{$this->def['join_key_lhs']} = '{$link->getFocus()->id}')";
222 if (empty($params['return_as_array'])) {
223 return "SELECT $targetKey FROM $rel_table WHERE $where AND deleted=0";
228 'select' => "SELECT $targetKey id",
229 'from' => "FROM $rel_table",
230 'where' => "WHERE $where AND $rel_table.deleted=0",
235 public function getJoin($link, $params = array(), $return_array = false)
237 $linkIsLHS = $link->getSide() == REL_LHS;
238 $startingTable = $link->getFocus()->table_name;
239 $startingKey = $linkIsLHS ? $this->def['lhs_key'] : $this->def['rhs_key'];
240 $startingJoinKey = $linkIsLHS ? $this->def['join_key_lhs'] : $this->def['join_key_rhs'];
241 $joinTable = $this->getRelationshipTable();
242 $joinTableWithAlias = $joinTable;
243 $joinKey = $linkIsLHS ? $this->def['join_key_rhs'] : $this->def['join_key_lhs'];
244 $targetTable = $linkIsLHS ? $this->def['rhs_table'] : $this->def['lhs_table'];
245 $targetTableWithAlias = $targetTable;
246 $targetKey = $linkIsLHS ? $this->def['rhs_key'] : $this->def['lhs_key'];
247 $join_type= isset($params['join_type']) ? $params['join_type'] : ' INNER JOIN ';
251 //Set up any table aliases required
252 if (!empty($params['join_table_link_alias']))
254 $joinTableWithAlias = $joinTable . " ". $params['join_table_link_alias'];
255 $joinTable = $params['join_table_link_alias'];
257 if ( ! empty($params['join_table_alias']))
259 $targetTableWithAlias = $targetTable . " ". $params['join_table_alias'];
260 $targetTable = $params['join_table_alias'];
263 if (!$this->self_referencing)
265 $join1 = "$startingTable.$startingKey=$joinTable.$startingJoinKey";
266 $join2 = "$targetTable.$targetKey=$joinTable.$joinKey";
271 $join1 = "($startingTable.$startingKey=$joinTable.{$this->def['join_key_rhs']} OR $startingTable.$startingKey=$joinTable.{$this->def['join_key_rhs']})";
272 $join2 = "($targetTable.$targetKey=$joinTable.{$this->def['join_key_rhs']} OR $targetTable.$targetKey=$joinTable.{$this->def['join_key_rhs']})";
273 $where = "($startingTable.$startingKey=$joinTable.{$this->def['join_key_rhs']} AND $joinTable.{$this->def['join_key_lhs']}='{$link->getFocus()->$targetKey}') OR "
274 . "($startingTable.$startingKey=$joinTable.{$this->def['join_key_lhs']} AND $joinTable.{$this->def['join_key_rhs']}='{$link->getFocus()->$targetKey}')";
278 //First join the relationship table
279 $join .= "$join_type $joinTableWithAlias ON $join1 AND $joinTable.deleted=0\n"
280 //Next add any role filters
281 . $this->getRoleFilterForJoin() . "\n"
282 //Then finally join the related module's table
283 . "$join_type $targetTableWithAlias ON $join2 AND $targetTable.deleted=0\n";
288 'type' => $this->type,
289 'rel_key' => $joinKey,
290 'join_tables' => array($joinTable, $targetTable),
292 'select' => "$targetTable.id",
295 return $join . $where;
299 * Similar to getQuery or Get join, except this time we are starting from the related table and
300 * searching for items with id's matching the $link->focus->id
302 * @param array $params
303 * @param bool $return_array
306 public function getSubpanelQuery($link, $params = array(), $return_array = false)
308 $targetIsLHS = $link->getSide() == REL_RHS;
309 $startingTable = $targetIsLHS ? $this->def['lhs_table'] : $this->def['rhs_table'];;
310 $startingKey = $targetIsLHS ? $this->def['lhs_key'] : $this->def['rhs_key'];
311 $startingJoinKey = $targetIsLHS ? $this->def['join_key_lhs'] : $this->def['join_key_rhs'];
312 $joinTable = $this->getRelationshipTable();
313 $joinTableWithAlias = $joinTable;
314 $joinKey = $targetIsLHS ? $this->def['join_key_rhs'] : $this->def['join_key_lhs'];
315 $targetKey = $targetIsLHS ? $this->def['rhs_key'] : $this->def['lhs_key'];
316 $join_type= isset($params['join_type']) ? $params['join_type'] : ' INNER JOIN ';
320 //Set up any table aliases required
321 if (!empty($params['join_table_link_alias']))
323 $joinTableWithAlias = $joinTable . " ". $params['join_table_link_alias'];
324 $joinTable = $params['join_table_link_alias'];
327 if (!$this->self_referencing)
329 $where = "$startingTable.$startingKey=$joinTable.$startingJoinKey AND $joinTable.$joinKey='{$link->getFocus()->$targetKey}'";
333 $where = "($startingTable.$startingKey=$joinTable.{$this->def['join_key_rhs']} AND $joinTable.{$this->def['join_key_lhs']}='{$link->getFocus()->$targetKey}') OR "
334 . "($startingTable.$startingKey=$joinTable.{$this->def['join_key_lhs']} AND $joinTable.{$this->def['join_key_rhs']}='{$link->getFocus()->$targetKey}')";
337 //First join the relationship table
338 $query .= "$join_type $joinTableWithAlias ON $where AND $joinTable.deleted=0\n"
339 //Next add any role filters
340 . $this->getRoleFilterForJoin() . "\n";
342 if (!empty($params['return_as_array'])) {
343 $return_array = true;
348 'type' => $this->type,
349 'rel_key' => $joinKey,
350 'join_tables' => array($joinTable),
359 protected function getRoleFilterForJoin()
362 if (!empty($this->relationship_role_column) && !$this->ignore_role_filter)
364 $ret .= " AND ".$this->getRelationshipTable().'.'.$this->relationship_role_column;
366 if (empty($this->relationship_role_column_value))
370 $ret.= "='".$this->relationship_role_column_value."'";
382 public function relationship_exists($lhs, $rhs)
384 $query = "SELECT * FROM {$this->getRelationshipTable()} WHERE {$this->join_key_lhs} = {$lhs->id} AND {$this->join_key_rhs} = {$rhs->id}";
386 //Roles can allow for multiple links between two records with different roles
387 $query .= $this->getRoleFilterForJoin() . " and deleted = 0";
389 $result = DBManagerFactory::getInstance()->query($query);
390 $row = $this->_db->fetchByAssoc($result);
401 * @return Array - set of fields that uniquely identify an entry in this relationship
403 protected function getAlternateKeyFields()
405 $fields = array($this->join_key_lhs, $this->join_key_rhs);
407 //Roles can allow for multiple links between two records with different roles
408 if (!empty($this->def['relationship_role_column']) && !$this->ignore_role_filter)
410 $fields[] = $this->relationship_role_column;
416 public function getRelationshipTable()
418 if (!empty($this->def['table']))
419 return $this->def['table'];
420 else if(!empty($this->def['join_table']))
421 return $this->def['join_table'];