1: <?php
  2: /**
  3:  * Object joint handler class.
  4:  *
  5:  * You may not change or alter any portion of this comment or credits
  6:  * of supporting developers from this source code or any supporting source code
  7:  * which is considered copyrighted (c) material of the original comment or credit authors.
  8:  * This program is distributed in the hope that it will be useful,
  9:  * but WITHOUT ANY WARRANTY; without even the implied warranty of
 10:  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
 11:  *
 12:  * @copyright       (c) 2000-2016 XOOPS Project (www.xoops.org)
 13:  * @license             GNU GPL 2 (http://www.gnu.org/licenses/gpl-2.0.html)
 14:  * @package             kernel
 15:  * @subpackage          model
 16:  * @since               2.3.0
 17:  * @author              Taiwen Jiang <phppp@users.sourceforge.net>
 18:  */
 19: defined('XOOPS_ROOT_PATH') || exit('Restricted access');
 20: 
 21: /**
 22:  * Object joint handler class.
 23:  *
 24:  * @author Taiwen Jiang <phppp@users.sourceforge.net>
 25:  *
 26:  * {@link XoopsModelAbstract}
 27:  */
 28: 
 29: /**
 30:  * Usage of methods provided by XoopsModelJoint:
 31:  *
 32:  * Step #1: set linked table and adjoint fields through XoopsPersistableObjectHandler:
 33:  *             $handler->table_link = $handler->db->prefix("the_linked_table"); // full name of the linked table that is used for the query
 34:  *             $handler->field_link = "the_linked_field"; // name of field in linked table that will be used to link the linked table with current table
 35:  *             $handler->field_object = "the_object_field"; // name of field in current table that will be used to link the linked table with current table; linked field name will be used if the field name is not set
 36:  * Step #2: fetch data
 37:  */
 38: class XoopsModelJoint extends XoopsModelAbstract
 39: {
 40:     /**
 41:      * Validate information for the link
 42:      *
 43:      * @access private
 44:      */
 45:     public function validateLinks()
 46:     {
 47:         if (empty($this->handler->table_link) || empty($this->handler->field_link)) {
 48:             trigger_error('The linked table is not set yet.', E_USER_WARNING);
 49: 
 50:             return null;
 51:         }
 52:         if (empty($this->handler->field_object)) {
 53:             $this->handler->field_object = $this->handler->field_link;
 54:         }
 55: 
 56:         return true;
 57:     }
 58: 
 59:     /**
 60:      * get a list of objects matching a condition joint with another related object
 61:      *
 62:      * @param CriteriaElement|CriteriaCompo $criteria
 63:      * @param  array          $fields       variables to fetch
 64:      * @param  bool           $asObject     flag indicating as object, otherwise as array
 65:      * @param  string         $field_link   field of linked object for JOIN; deprecated, for backward compatibility
 66:      * @param  string         $field_object field of current object for JOIN; deprecated, for backward compatibility
 67:      * @return array of objects <a href='psi_element://XoopsObject'>XoopsObject</a>
 68:      * @internal param CriteriaElement $object <a href='psi_element://CriteriaElement'>CriteriaElement</a> to match to match
 69:      */
 70:     public function &getByLink(CriteriaElement $criteria = null, $fields = null, $asObject = true, $field_link = null, $field_object = null)
 71:     {
 72:         if (!empty($field_link)) {
 73:             $this->handler->field_link = $field_link;
 74:         }
 75:         if (!empty($field_object)) {
 76:             $this->handler->field_object = $field_object;
 77:         }
 78:         if (!$this->validateLinks()) {
 79:             return null;
 80:         }
 81: 
 82:         if (is_array($fields) && count($fields)) {
 83:             if (!in_array('o.' . $this->handler->keyName, $fields)) {
 84:                 $fields[] = 'o.' . $this->handler->keyName;
 85:             }
 86:             $select = implode(',', $fields);
 87:         } else {
 88:             $select = 'o.*, l.*';
 89:         }
 90:         $limit = null;
 91:         $start = null;
 92:         // $field_object = empty($field_object) ? $field_link : $field_object;
 93:         $sql = " SELECT {$select}" . " FROM {$this->handler->table} AS o" . " LEFT JOIN {$this->handler->table_link} AS l ON o.{$this->handler->field_object} = l.{$this->handler->field_link}";
 94:         if (isset($criteria) && is_subclass_of($criteria, 'criteriaelement')) {
 95:             $sql .= ' ' . $criteria->renderWhere();
 96:             if ($sort = $criteria->getSort()) {
 97:                 $sql .= " ORDER BY {$sort} " . $criteria->getOrder();
 98:                 $orderSet = true;
 99:             }
100:             $limit = $criteria->getLimit();
101:             $start = $criteria->getStart();
102:         }
103:         if (empty($orderSet)) {
104:             $sql .= " ORDER BY o.{$this->handler->keyName} DESC";
105:         }
106:         $result = $this->handler->db->query($sql, $limit, $start);
107:         $ret    = array();
108:         if ($asObject) {
109:             while ($myrow = $this->handler->db->fetchArray($result)) {
110:                 $object = $this->handler->create(false);
111:                 $object->assignVars($myrow);
112:                 $ret[$myrow[$this->handler->keyName]] = $object;
113:                 unset($object);
114:             }
115:         } else {
116:             $object = $this->handler->create(false);
117:             while ($myrow = $this->handler->db->fetchArray($result)) {
118:                 $object->assignVars($myrow);
119:                 $ret[$myrow[$this->handler->keyName]] = $object->getValues(array_keys($myrow));
120:             }
121:             unset($object);
122:         }
123: 
124:         return $ret;
125:     }
126: 
127:     /**
128:      * Count of objects matching a condition
129:      *
130:      * @param  CriteriaElement|CriteriaCompo $criteria {@link CriteriaElement} to match
131:      * @return int    count of objects
132:      */
133:     public function getCountByLink(CriteriaElement $criteria = null)
134:     {
135:         if (!$this->validateLinks()) {
136:             return null;
137:         }
138: 
139:         $sql = " SELECT COUNT(DISTINCT o.{$this->handler->keyName}) AS count" . " FROM {$this->handler->table} AS o" . " LEFT JOIN {$this->handler->table_link} AS l ON o.{$this->handler->field_object} = l.{$this->handler->field_link}";
140:         if (isset($criteria) && is_subclass_of($criteria, 'criteriaelement')) {
141:             $sql .= ' ' . $criteria->renderWhere();
142:         }
143:         if (!$result = $this->handler->db->query($sql)) {
144:             return false;
145:         }
146:         $myrow = $this->handler->db->fetchArray($result);
147: 
148:         return (int)$myrow['count'];
149:     }
150: 
151:     /**
152:      * array of count of objects matching a condition of, groupby linked object keyname
153:      *
154:      * @param  CriteriaElement|CriteriaCompo $criteria {@link CriteriaElement} to match
155:      * @return int    count of objects
156:      */
157:     public function getCountsByLink(CriteriaElement $criteria = null)
158:     {
159:         if (!$this->validateLinks()) {
160:             return null;
161:         }
162:         $sql = " SELECT l.{$this->handler->field_link}, COUNT(*)" . " FROM {$this->handler->table} AS o" . " LEFT JOIN {$this->handler->table_link} AS l ON o.{$this->handler->field_object} = l.{$this->handler->field_link}";
163:         if (isset($criteria) && is_subclass_of($criteria, 'criteriaelement')) {
164:             $sql .= ' ' . $criteria->renderWhere();
165:         }
166:         $sql .= " GROUP BY l.{$this->handler->field_link}";
167:         if (!$result = $this->handler->db->query($sql)) {
168:             return false;
169:         }
170:         $ret = array();
171:         while (list($id, $count) = $this->handler->db->fetchRow($result)) {
172:             $ret[$id] = $count;
173:         }
174: 
175:         return $ret;
176:     }
177: 
178:     /**
179:      * update objects matching a condition against linked objects
180:      *
181:      * @param  array  $data     array of key => value
182:      * @param  CriteriaElement|CriteriaCompo $criteria {@link CriteriaElement} to match
183:      * @return int    count of objects
184:      */
185:     public function updateByLink($data, CriteriaElement $criteria = null)
186:     {
187:         if (!$this->validateLinks()) {
188:             return null;
189:         }
190:         $set = array();
191:         foreach ($data as $key => $val) {
192:             $set[] = "o.{$key}=" . $this->handler->db->quoteString($val);
193:         }
194:         $sql = " UPDATE {$this->handler->table} AS o" . ' SET ' . implode(', ', $set) . " LEFT JOIN {$this->handler->table_link} AS l ON o.{$this->handler->field_object} = l.{$this->handler->field_link}";
195:         if (isset($criteria) && is_subclass_of($criteria, 'criteriaelement')) {
196:             $sql .= ' ' . $criteria->renderWhere();
197:         }
198: 
199:         return $this->handler->db->query($sql);
200:     }
201: 
202:     /**
203:      * Delete objects matching a condition against linked objects
204:      *
205:      * @param  CriteriaElement|CriteriaCompo $criteria {@link CriteriaElement} to match
206:      * @return int    count of objects
207:      */
208:     public function deleteByLink(CriteriaElement $criteria = null)
209:     {
210:         if (!$this->validateLinks()) {
211:             return null;
212:         }
213:         $sql = "DELETE FROM {$this->handler->table} AS o " . " LEFT JOIN {$this->handler->table_link} AS l ON o.{$this->handler->field_object} = l.{$this->handler->field_link}";
214:         if (isset($criteria) && is_subclass_of($criteria, 'criteriaelement')) {
215:             $sql .= ' ' . $criteria->renderWhere();
216:         }
217: 
218:         return $this->handler->db->query($sql);
219:     }
220: }
221: