1: <?php
  2: /*
  3:  You may not change or alter any portion of this comment or credits
  4:  of supporting developers from this source code or any supporting source code
  5:  which is considered copyrighted (c) material of the original comment or credit authors.
  6: 
  7:  This program is distributed in the hope that it will be useful,
  8:  but WITHOUT ANY WARRANTY; without even the implied warranty of
  9:  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
 10: */
 11: 
 12: namespace Xoops\Core\Kernel\Model;
 13: 
 14: use Xoops\Core\Kernel\CriteriaElement;
 15: use Xoops\Core\Kernel\XoopsModelAbstract;
 16: 
 17: /**
 18:  * Object joint handler class.
 19:  *
 20:  * @category  Xoops\Core\Kernel\Model\Joint
 21:  * @package   Xoops\Core\Kernel
 22:  * @author    Taiwen Jiang <phppp@users.sourceforge.net>
 23:  * @copyright 2000-2015 XOOPS Project (http://xoops.org)
 24:  * @license   GNU GPL 2 or later (http://www.gnu.org/licenses/gpl-2.0.html)
 25:  * @link      http://xoops.org
 26:  * @since     2.3.0
 27:  *
 28:  * Usage of methods provided by XoopsModelJoint:
 29:  *
 30:  * Step #1: set linked table and joint fields through XoopsPersistableObjectHandler:
 31:  *      $handler->table_link = $handler->db2->prefix("the_linked_table");
 32:  *          full name of the linked table that is used for the query
 33:  *      $handler->field_link = "the_linked_field";
 34:  *          name of field in linked table that will be used to link the linked table
 35:  *          with current table
 36:  *      $handler->field_object = "the_object_field";
 37:  *          name of field in current table that will be used to link the linked table
 38:  *          with current table; linked field name will be used if the field name is
 39:  *          not set
 40:  * Step #2: fetch data
 41:  */
 42: class Joint extends XoopsModelAbstract
 43: {
 44:     /**
 45:      * Validate information for the linkage
 46:      *
 47:      * @return bool
 48:      */
 49:     private function validateLinks()
 50:     {
 51:         if (empty($this->handler->table_link) || empty($this->handler->field_link)) {
 52:             trigger_error("The linked table is not set yet.", E_USER_WARNING);
 53:             return false;
 54:         }
 55:         if (empty($this->handler->field_object)) {
 56:             $this->handler->field_object = $this->handler->field_link;
 57:         }
 58:         return true;
 59:     }
 60: 
 61:     /**
 62:      * get a list of objects matching a condition joint with another related object
 63:      *
 64:      * @param CriteriaElement|null $criteria     criteria to match
 65:      * @param array                $fields       variables to fetch
 66:      * @param bool                 $asObject     flag indicating as object, otherwise as array
 67:      * @param string               $field_link   field of linked object for JOIN;
 68:      *                                            deprecated, for backward compatibility only
 69:      * @param string               $field_object field of current object for JOIN;
 70:      *                                            deprecated, for backward compatibility only
 71:      *
 72:      * @return false|array array as requested by $asObject
 73:      */
 74:     public function getByLink(
 75:         CriteriaElement $criteria = null,
 76:         $fields = null,
 77:         $asObject = true,
 78:         $field_link = null,
 79:         $field_object = null
 80:     ) {
 81:         if (!empty($field_link)) {
 82:             $this->handler->field_link = $field_link;
 83:         }
 84:         if (!empty($field_object)) {
 85:             $this->handler->field_object = $field_object;
 86:         }
 87:         if (!$this->validateLinks()) {
 88:             return false;
 89:         }
 90: 
 91:         $qb = $this->handler->db2->createXoopsQueryBuilder();
 92:         if (is_array($fields) && count($fields)) {
 93:             if (!in_array("o." . $this->handler->keyName, $fields)) {
 94:                 $fields[] = "o." . $this->handler->keyName;
 95:             }
 96:             $first = true;
 97:             foreach ($fields as $field) {
 98:                 if ($first) {
 99:                     $first = false;
100:                     $qb->select($field);
101:                 } else {
102:                     $qb->addSelect($field);
103:                 }
104:             }
105:         } else {
106:             $qb ->select('o.*')
107:                 ->addSelect('l.*');
108:         }
109:         $qb ->from($this->handler->table, 'o')
110:             ->leftJoin(
111:                 'o',
112:                 $this->handler->table_link,
113:                 'l',
114:                 "o.{$this->handler->field_object} = l.{$this->handler->field_link}"
115:             );
116:         if (isset($criteria) && ($criteria instanceof CriteriaElement)) {
117:             $qb = $criteria->renderQb($qb);
118:         }
119:         $result = $qb->execute();
120:         $ret = array();
121:         if ($asObject) {
122:             while ($myrow = $result->fetch(\PDO::FETCH_ASSOC)) {
123:                 $object = $this->handler->create(false);
124:                 $object->assignVars($myrow);
125:                 $ret[$myrow[$this->handler->keyName]] = $object;
126:                 unset($object);
127:             }
128:         } else {
129:             $object = $this->handler->create(false);
130:             while ($myrow = $result->fetch(\PDO::FETCH_ASSOC)) {
131:                 $object->assignVars($myrow);
132:                 $ret[$myrow[$this->handler->keyName]] = $object->getValues();
133:             }
134:             unset($object);
135:         }
136:         return $ret;
137:     }
138: 
139:     /**
140:      * Count of objects matching a condition
141:      *
142:      * @param CriteriaElement|null $criteria criteria to match
143:      *
144:      * @return false|int count of objects
145:      */
146:     public function getCountByLink(CriteriaElement $criteria = null)
147:     {
148:         if (!$this->validateLinks()) {
149:             return false;
150:         }
151: 
152:         $qb = $this->handler->db2->createXoopsQueryBuilder();
153: 
154:         $qb ->select("COUNT(DISTINCT o.{$this->handler->keyName})")
155:             ->from($this->handler->table, 'o')
156:             ->leftJoin(
157:                 'o',
158:                 $this->handler->table_link,
159:                 'l',
160:                 "o.{$this->handler->field_object} = l.{$this->handler->field_link}"
161:             );
162: 
163:         if (isset($criteria) && ($criteria instanceof CriteriaElement)) {
164:             $criteria->renderQb($qb);
165:         }
166: 
167:         $result = $qb->execute();
168:         return $result->fetchColumn(0);
169:     }
170: 
171:     /**
172:      * array of count of objects matching a condition of, groupby linked object keyname
173:      *
174:      * @param CriteriaElement $criteria criteria to match
175:      *
176:      * @return false|int count of objects
177:      */
178:     public function getCountsByLink(CriteriaElement $criteria = null)
179:     {
180:         if (!$this->validateLinks()) {
181:             return false;
182:         }
183: 
184:         $qb = $this->handler->db2->createXoopsQueryBuilder();
185: 
186:         $qb ->select("l.{$this->handler->keyName_link}")
187:             ->addSelect('COUNT(*)')
188:             ->from($this->handler->table, 'o')
189:             ->leftJoin(
190:                 'o',
191:                 $this->handler->table_link,
192:                 'l',
193:                 "o.{$this->handler->field_object} = l.{$this->handler->field_link}"
194:             );
195: 
196:         if (isset($criteria) && ($criteria instanceof CriteriaElement)) {
197:             $criteria->renderQb($qb);
198:         }
199: 
200:         $qb ->groupBy("l.{$this->handler->keyName_link}");
201: 
202:         $result = $qb->execute();
203: 
204:         $ret = array();
205:         while (list($id, $count) = $result->fetch(\PDO::FETCH_NUM)) {
206:             $ret[$id] = $count;
207:         }
208:         return $ret;
209:     }
210: 
211:     /**
212:      * update objects matching a condition against linked objects
213:      *
214:      * @param array                $data     array of key => value
215:      * @param CriteriaElement|null $criteria criteria to match
216:      *
217:      * @return false|int count of objects
218:      *
219:      * @todo UPDATE ... LEFT JOIN is not portable
220:      * Note Alain91 : multi tables update is not allowed in Doctrine
221:      */
222:     public function updateByLink(array $data, CriteriaElement $criteria = null)
223:     {
224:         if (!$this->validateLinks()) {
225:             return false;
226:         }
227:         if (empty($data) || empty($criteria)) { // avoid update all records
228:             return false;
229:         }
230: 
231:         $set = array();
232:         foreach ($data as $key => $val) {
233:             $set[] = "o.{$key}=" . $this->handler->db2->quote($val);
234:         }
235:         $sql = " UPDATE {$this->handler->table} AS o" . " SET " . implode(", ", $set)
236:             . " LEFT JOIN {$this->handler->table_link} AS l "
237:             . "ON o.{$this->handler->field_object} = l.{$this->handler->field_link}";
238:         if (isset($criteria) && ($criteria instanceof CriteriaElement)) {
239:             $sql .= " " . $criteria->renderWhere();
240:         }
241: 
242:         return $this->handler->db2->executeUpdate($sql);
243:     }
244: 
245:     /**
246:      * Delete objects matching a condition against linked objects
247:      *
248:      * @param CriteriaElement|null $criteria criteria to match
249:      *
250:      * @return false|int count of objects
251:      *
252:      * @todo DELETE ... LEFT JOIN is not portable
253:      * Note Alain91 : multi tables delete is not allowed in Doctrine
254:      */
255:     public function deleteByLink(CriteriaElement $criteria = null)
256:     {
257:         if (!$this->validateLinks()) {
258:             return false;
259:         }
260:         if (empty($criteria)) { //avoid delete all records
261:             return false;
262:         }
263: 
264:         $sql = "DELETE FROM {$this->handler->table} AS o "
265:             . "LEFT JOIN {$this->handler->table_link} AS l "
266:             . "ON o.{$this->handler->field_object} = l.{$this->handler->field_link}";
267:         if (isset($criteria) && ($criteria instanceof CriteriaElement)) {
268:             $sql .= " " . $criteria->renderWhere();
269:         }
270: 
271:         return $this->handler->db2->executeUpdate($sql);
272:     }
273: }
274: