1: <?php
  2: /**
  3:  * Represent a single query or a tree of many query elements uniformly to
  4:  * clients.
  5:  *
  6:  * @category Horde
  7:  * @package  Rdo
  8:  */
  9: 
 10: /**
 11:  * @category Horde
 12:  * @package  Rdo
 13:  */
 14: class Horde_Rdo_Query
 15: {
 16:     /**
 17:      * @var Horde_Rdo_Mapper
 18:      */
 19:     public $mapper;
 20: 
 21:     /**
 22:      * @var string
 23:      */
 24:     public $conjunction = 'AND';
 25: 
 26:     /**
 27:      * @var array
 28:      */
 29:     public $fields = array('*');
 30: 
 31:     /**
 32:      * @var boolean
 33:      */
 34:     public $distinct = false;
 35: 
 36:     /**
 37:      * @var array
 38:      */
 39:     public $tests = array();
 40: 
 41:     /**
 42:      * @var array
 43:      */
 44:     public $relationships = array();
 45: 
 46:     /**
 47:      * @var integer
 48:      */
 49:     public $limit;
 50: 
 51:     /**
 52:      * @var integer
 53:      */
 54:     public $limitOffset = null;
 55: 
 56:     /**
 57:      * @var array
 58:      */
 59:     protected $_sortby = array();
 60: 
 61:     /**
 62:      * @var integer
 63:      */
 64:     protected $_aliasCount = 0;
 65: 
 66:     /**
 67:      * @var array
 68:      */
 69:     protected $_aliases = array();
 70: 
 71:     /**
 72:      * Turn any of the acceptable query shorthands into a full
 73:      * Horde_Rdo_Query object. If you pass an existing Horde_Rdo_Query
 74:      * object in, it will be cloned before it's returned so that it
 75:      * can be safely modified.
 76:      *
 77:      * @param mixed $query The query to convert to an object.
 78:      * @param Horde_Rdo_Mapper $mapper The Mapper object governing this query.
 79:      *
 80:      * @return Horde_Rdo_Query The full Horde_Rdo_Query object.
 81:      */
 82:     public static function create($query, $mapper = null)
 83:     {
 84:         if ($query instanceof Horde_Rdo_Query ||
 85:             $query instanceof Horde_Rdo_Query_Literal) {
 86:             $query = clone $query;
 87:             if (!is_null($mapper)) {
 88:                 $query->setMapper($mapper);
 89:             }
 90:             return $query;
 91:         }
 92: 
 93:         $q = new Horde_Rdo_Query($mapper);
 94: 
 95:         if (is_scalar($query)) {
 96:             $q->addTest($mapper->tableDefinition->getPrimaryKey(), '=', $query);
 97:         } elseif ($query) {
 98:             $q->combineWith('AND');
 99:             foreach ($query as $key => $value) {
100:                 $q->addTest($key, '=', $value);
101:             }
102:         }
103: 
104:         return $q;
105:     }
106: 
107:     /**
108:      * @param  Horde_Rdo_Mapper  $mapper  Rdo mapper base class
109:      */
110:     public function __construct($mapper = null)
111:     {
112:         $this->setMapper($mapper);
113:     }
114: 
115:     /**
116:      * @param Horde_Rdo_Mapper $mapper Rdo mapper base class
117:      *
118:      * @return Horde_Rdo_Query Return the query object for fluent chaining.
119:      */
120:     public function setMapper($mapper)
121:     {
122:         if ($mapper === $this->mapper) {
123:             return $this;
124:         }
125: 
126:         $this->mapper = $mapper;
127: 
128:         // Fetch all non-lazy-loaded fields for the mapper.
129:         $this->setFields($mapper->fields, $mapper->table . '.');
130: 
131:         // Add all non-lazy relationships.
132:         foreach ($mapper->relationships as $relationship => $rel) {
133:             if (isset($rel['mapper'])) {
134:                 // @TODO - should be getting this instance from somewhere
135:                 // else external, and not passing the adapter along
136:                 // automatically.
137:                 $m = new $rel['mapper']($this->mapper->adapter);
138:             } else {
139:                 $m = $this->mapper->tableToMapper($relationship);
140:                 if (is_null($m)) {
141:                     throw new Horde_Rdo_Exception('Unable to find a Mapper class for eager-loading relationship ' . $relationship);
142:                 }
143:             }
144: 
145:             // Add the fields for this relationship to the query.
146:             $m->tableAlias = $this->_alias($m->table);
147:             $this->addFields($m->fields, $m->tableAlias . '.@');
148: 
149:             switch ($rel['type']) {
150:             case Horde_Rdo::ONE_TO_ONE:
151:             case Horde_Rdo::MANY_TO_ONE:
152:                 if (isset($rel['query'])) {
153:                     $query = $this->_fillJoinPlaceholders($m, $mapper, $rel['query']);
154:                 } else {
155:                     $query = array($mapper->table . '.' . $rel['foreignKey'] => new Horde_Rdo_Query_Literal($m->table . '.' . $m->tableDefinition->getPrimaryKey()));
156:                 }
157:                 $this->addRelationship($relationship, array('mapper' => $m,
158:                                                             'type' => $rel['type'],
159:                                                             'query' => $query));
160:                 break;
161: 
162:             case Horde_Rdo::ONE_TO_MANY:
163:             case Horde_Rdo::MANY_TO_MANY:
164:                 //@TODO
165:             }
166:         }
167: 
168:         return $this;
169:     }
170: 
171:     /**
172:      * Makes the query return only distinct (different) values.
173:      *
174:      * @since Horde_Rdo 1.1.0
175:      *
176:      * @param boolean $distinct  Whether to enable a distinct query.
177:      *
178:      * @return Horde_Rdo_Query Returns self for fluent method chaining.
179:      */
180:     public function distinct($distinct)
181:     {
182:         $this->distinct = $distinct;
183:         return $this;
184:     }
185: 
186:     /**
187:      * @param array $fields The fields to load with this query.
188:      *
189:      * @return Horde_Rdo_Query Returns self for fluent method chaining.
190:      */
191:     public function setFields($fields, $fieldPrefix = null)
192:     {
193:         if (!is_array($fields)) {
194:             $fields = array($fields);
195:         }
196:         if (!is_null($fieldPrefix)) {
197:             array_walk($fields, array($this, '_prefix'), $fieldPrefix);
198:         }
199:         $this->fields = $fields;
200:         return $this;
201:     }
202: 
203:     /**
204:      * @param array $fields Additional Fields to load with this query.
205:      *
206:      * @return Horde_Rdo_Query Returns self for fluent method chaining.
207:      */
208:     public function addFields($fields, $fieldPrefix = null)
209:     {
210:         if (!is_null($fieldPrefix)) {
211:             array_walk($fields, array($this, '_prefix'), $fieldPrefix);
212:         }
213:         $this->fields = array_merge($this->fields, $fields);
214:     }
215: 
216:     /**
217:      * @param string $conjunction SQL conjunction such as "AND", "OR".
218:      */
219:     public function combineWith($conjunction)
220:     {
221:         $this->conjunction = $conjunction;
222:         return $this;
223:     }
224: 
225:     /**
226:      */
227:     public function addTest($field, $test, $value)
228:     {
229:         $this->tests[] = array('field' => $field,
230:                                'test'  => $test,
231:                                'value' => $value);
232:         return $this;
233:     }
234: 
235:     /**
236:      */
237:     public function addRelationship($relationship, $args)
238:     {
239:         if (!isset($args['mapper'])) {
240:             throw new InvalidArgumentException('Relationships must contain a Horde_Rdo_Mapper object.');
241:         }
242:         if (!isset($args['table'])) {
243:             $args['table'] = $args['mapper']->table;
244:         }
245:         if (!isset($args['tableAlias'])) {
246:             if (isset($args['mapper']->tableAlias)) {
247:                 $args['tableAlias'] = $args['mapper']->tableAlias;
248:             } else {
249:                 $args['tableAlias'] = $this->_alias($args['table']);
250:             }
251:         }
252:         if (!isset($args['type'])) {
253:             $args['type'] = Horde_Rdo::MANY_TO_MANY;
254:         }
255:         if (!isset($args['join_type'])) {
256:             switch ($args['type']) {
257:             case Horde_Rdo::ONE_TO_ONE:
258:             case Horde_Rdo::MANY_TO_ONE:
259:                 $args['join_type'] = 'INNER JOIN';
260:                 break;
261: 
262:             default:
263:                 $args['join_type'] = 'LEFT JOIN';
264:             }
265:         }
266: 
267:         $this->relationships[$relationship] = $args;
268:         return $this;
269:     }
270: 
271:     /**
272:      * Add a sorting rule.
273:      *
274:      * @param string $sort SQL sort fragment, such as 'updated DESC'
275:      */
276:     public function sortBy($sort)
277:     {
278:         $this->_sortby[] = $sort;
279:         return $this;
280:     }
281: 
282:     /**
283:      */
284:     public function clearSort()
285:     {
286:         $this->_sortby = array();
287:         return $this;
288:     }
289: 
290:     /**
291:      * Restrict the query to a subset of the results.
292:      *
293:      * @param integer $limit Number of items to fetch.
294:      * @param integer $offset Offset to start fetching at.
295:      */
296:     public function limit($limit, $offset = null)
297:     {
298:         $this->limit = $limit;
299:         $this->limitOffset = $offset;
300:         return $this;
301:     }
302: 
303:     /**
304:      * Accessor for any fields that we want some logic around.
305:      *
306:      * @param string $key
307:      */
308:     public function __get($key)
309:     {
310:         switch ($key) {
311:         case 'sortby':
312:             if (!$this->_sortby && $this->mapper->defaultSort) {
313:                 // Add in any default sort values, if none are already
314:                 // set.
315:                 $this->sortBy($this->mapper->defaultSort);
316:             }
317:             return $this->_sortby;
318:         }
319: 
320:         throw new InvalidArgumentException('Undefined property ' . $key);
321:     }
322: 
323:     /**
324:      * Query generator.
325:      *
326:      * @return array A two-element array of the SQL query and an array
327:      * of bind parameters.
328:      */
329:     public function getQuery()
330:     {
331:         $bindParams = array();
332:         $sql = '';
333: 
334:         $this->_select($sql, $bindParams);
335:         $this->_from($sql, $bindParams);
336:         $this->_join($sql, $bindParams);
337:         $this->_where($sql, $bindParams);
338:         $this->_orderBy($sql, $bindParams);
339:         $this->_limit($sql, $bindParams);
340: 
341:         return array($sql, $bindParams);
342:     }
343: 
344:     /**
345:      */
346:     protected function _select(&$sql, &$bindParams)
347:     {
348:         $fields = array();
349:         foreach ($this->fields as $field) {
350:             $parts = explode('.@', $field, 2);
351:             if (count($parts) == 1) {
352:                 $fields[] = $field;
353:             } else {
354:                 list($tableName, $columnName) = $parts;
355:                 if (isset($this->_aliases[$tableName])) {
356:                     $tableName = $this->_aliases[$tableName];
357:                 }
358:                 $fields[] = str_replace('.@', '.', $field) . ' AS ' . $this->mapper->adapter->quoteColumnName($tableName . '@' . $columnName);
359:             }
360:         }
361: 
362:         if ($this->distinct) {
363:             $sql = 'SELECT ' . $this->mapper->adapter->distinct(implode(', ', $fields), implode(', ', $this->sortby));
364:         } else {
365:             $sql = 'SELECT ' . implode(', ', $fields);
366:         }
367:     }
368: 
369:     /**
370:      */
371:     protected function _from(&$sql, &$bindParams)
372:     {
373:         $sql .= ' FROM ' . $this->mapper->table;
374:     }
375: 
376:     /**
377:      */
378:     protected function _join(&$sql, &$bindParams)
379:     {
380:         foreach ($this->relationships as $relationship) {
381:             $relsql = array();
382:             $table = $relationship['table'];
383:             $tableAlias = $relationship['tableAlias'];
384:             foreach ($relationship['query'] as $key => $value) {
385:                 if ($value instanceof Horde_Rdo_Query_Literal) {
386:                     $relsql[] = $key . ' = ' . str_replace("{$table}.", "{$tableAlias}.", (string)$value);
387:                 } else {
388:                     $relsql[] = $key . ' = ?';
389:                     $bindParams[] = $value;
390:                 }
391:             }
392: 
393:             $sql .= ' ' . $relationship['join_type'] . ' ' . $relationship['table'] . ' ' . $tableAlias . ' ON ' . implode(' AND ', $relsql);
394:         }
395:     }
396: 
397:     /**
398:      */
399:     protected function _where(&$sql, &$bindParams)
400:     {
401:         $clauses = array();
402:         foreach ($this->tests as $test) {
403:             if (strpos($test['field'], '@') !== false) {
404:                 list($rel, $field) = explode('@', $test['field']);
405:                 if (!isset($this->relationships[$rel])) {
406:                     continue;
407:                 }
408:                 $clause = $this->relationships[$rel]['tableAlias'] . '.' . $field . ' ' . $test['test'];
409:             } else {
410:                 $clause = $this->mapper->table . '.' . $this->mapper->adapter->quoteColumnName($test['field']) . ' ' . $test['test'];
411:             }
412: 
413:             if ($test['value'] instanceof Horde_Rdo_Query_Literal) {
414:                 $clauses[] = $clause . ' ' . (string)$test['value'];
415:             } else {
416:                 if (($test['test'] == 'IN' || $test['test'] == 'NOT IN') && is_array($test['value'])) {
417:                     $clauses[] = $clause . '(?' . str_repeat(',?', count($test['value']) - 1) . ')';
418:                     $bindParams = array_merge($bindParams, array_values($test['value']));
419:                 } else {
420:                     $clauses[] = $clause . ' ?';
421:                     $bindParams[] = $test['value'];
422:                 }
423:             }
424:         }
425: 
426:         if ($clauses) {
427:             $sql .= ' WHERE ' . implode(' ' . $this->conjunction . ' ', $clauses);
428:         }
429:     }
430: 
431:     /**
432:      */
433:     protected function _orderBy(&$sql, &$bindParams)
434:     {
435:         if ($this->sortby) {
436:             $sql .= ' ORDER BY';
437:             foreach ($this->sortby as $sort) {
438:                 if (strpos($sort, '@') !== false) {
439:                     list($rel, $field) = explode('@', $sort);
440:                     if (!isset($this->relationships[$rel])) {
441:                         continue;
442:                     }
443:                     $sql .= ' ' . $this->relationships[$rel]['tableAlias'] . '.' . $field . ',';
444:                 } else {
445:                     $sql .= " $sort,";
446:                 }
447:             }
448: 
449:             $sql = substr($sql, 0, -1);
450:         }
451:     }
452: 
453:     /**
454:      */
455:     protected function _limit(&$sql, &$bindParams)
456:     {
457:         if ($this->limit) {
458:             $opts = array('limit' => $this->limit, 'offset' => $this->limitOffset);
459:             $sql = $this->mapper->adapter->addLimitOffset($sql, $opts);
460:         }
461:     }
462: 
463:     /**
464:      * Callback for array_walk to prefix all elements of an array with
465:      * a given prefix.
466:      */
467:     protected function _prefix(&$fieldName, $key, $prefix)
468:     {
469:         $fieldName = $prefix . $fieldName;
470:     }
471: 
472:     /**
473:      * Get a unique table alias
474:      */
475:     protected function _alias($tableName)
476:     {
477:         $alias = 't' . ++$this->_aliasCount;
478:         $this->_aliases[$alias] = $tableName;
479:         return $alias;
480:     }
481: 
482:     /**
483:      * Take a query array and replace @field@ placeholders with values
484:      * that will match in the load query.
485:      *
486:      * @param Horde_Rdo_Mapper $m1 Left-hand mapper
487:      * @param Horde_Rdo_Mapper $m2 Right-hand mapper
488:      * @param array $query The query to process placeholders on.
489:      *
490:      * @return array The query with placeholders filled in.
491:      */
492:     protected function _fillJoinPlaceholders($m1, $m2, $query)
493:     {
494:         $q = array();
495:         foreach (array_keys($query) as $field) {
496:             $value = $query[$field];
497:             if (preg_match('/^@(.*)@$/', $value, $matches)) {
498:                 $q[$m1->tableAlias . '.' . $field] = new Horde_Rdo_Query_Literal($m2->table . '.' . $matches[1]);
499:             } else {
500:                 $q[$m1->tableAlias . '.' . $field] = $value;
501:             }
502:         }
503: 
504:         return $q;
505:     }
506: 
507: }
508: