1: <?php
2: /**
3: * CDbCriteria class file.
4: *
5: * @author Qiang Xue <qiang.xue@gmail.com>
6: * @link http://www.yiiframework.com/
7: * @copyright 2008-2013 Yii Software LLC
8: * @license http://www.yiiframework.com/license/
9: */
10:
11: /**
12: * CDbCriteria represents a query criteria, such as conditions, ordering by, limit/offset.
13: *
14: * It can be used in AR query methods such as CActiveRecord::find and CActiveRecord::findAll.
15: *
16: * $criteria=new CDbCriteria();
17: * $criteria->compare('status',Post::STATUS_ACTIVE);
18: * $criteria->addInCondition('id',array(1,2,3,4,5,6));
19: *
20: * $posts = Post::model()->findAll($criteria);
21: *
22: * @author Qiang Xue <qiang.xue@gmail.com>
23: * @package system.db.schema
24: * @since 1.0
25: */
26: class CDbCriteria extends CComponent
27: {
28: const PARAM_PREFIX=':ycp';
29: /**
30: * @var integer the global counter for anonymous binding parameters.
31: * This counter is used for generating the name for the anonymous parameters.
32: */
33: public static $paramCount=0;
34: /**
35: * @var mixed the columns being selected. This refers to the SELECT clause in an SQL
36: * statement. The property can be either a string (column names separated by commas)
37: * or an array of column names. Defaults to '*', meaning all columns.
38: */
39: public $select='*';
40: /**
41: * @var boolean whether to select distinct rows of data only. If this is set true,
42: * the SELECT clause would be changed to SELECT DISTINCT.
43: */
44: public $distinct=false;
45: /**
46: * @var string query condition. This refers to the WHERE clause in an SQL statement.
47: * For example, <code>age>31 AND team=1</code>.
48: */
49: public $condition='';
50: /**
51: * @var array list of query parameter values indexed by parameter placeholders.
52: * For example, <code>array(':name'=>'Dan', ':age'=>31)</code>.
53: */
54: public $params=array();
55: /**
56: * @var integer maximum number of records to be returned. If less than 0, it means no limit.
57: */
58: public $limit=-1;
59: /**
60: * @var integer zero-based offset from where the records are to be returned. If less than 0, it means starting from the beginning.
61: */
62: public $offset=-1;
63: /**
64: * @var string how to sort the query results. This refers to the ORDER BY clause in an SQL statement.
65: */
66: public $order='';
67: /**
68: * @var string how to group the query results. This refers to the GROUP BY clause in an SQL statement.
69: * For example, <code>'projectID, teamID'</code>.
70: */
71: public $group='';
72: /**
73: * @var string how to join with other tables. This refers to the JOIN clause in an SQL statement.
74: * For example, <code>'LEFT JOIN users ON users.id=authorID'</code>.
75: */
76: public $join='';
77: /**
78: * @var string the condition to be applied with GROUP-BY clause.
79: * For example, <code>'SUM(revenue)<50000'</code>.
80: */
81: public $having='';
82: /**
83: * @var mixed the relational query criteria. This is used for fetching related objects in eager loading fashion.
84: * This property is effective only when the criteria is passed as a parameter to the following methods of CActiveRecord:
85: * <ul>
86: * <li>{@link CActiveRecord::find()}</li>
87: * <li>{@link CActiveRecord::findAll()}</li>
88: * <li>{@link CActiveRecord::findByPk()}</li>
89: * <li>{@link CActiveRecord::findAllByPk()}</li>
90: * <li>{@link CActiveRecord::findByAttributes()}</li>
91: * <li>{@link CActiveRecord::findAllByAttributes()}</li>
92: * <li>{@link CActiveRecord::count()}</li>
93: * </ul>
94: * The property value will be used as the parameter to the {@link CActiveRecord::with()} method
95: * to perform the eager loading. Please refer to {@link CActiveRecord::with()} on how to specify this parameter.
96: * @since 1.1.0
97: */
98: public $with;
99: /**
100: * @var string the alias name of the table. If not set, it means the alias is 't'.
101: */
102: public $alias;
103: /**
104: * @var boolean whether the foreign tables should be joined with the primary table in a single SQL.
105: * This property is only used in relational AR queries for HAS_MANY and MANY_MANY relations.
106: *
107: * When this property is set true, only a single SQL will be executed for a relational AR query,
108: * even if the primary table is limited and the relationship between a foreign table and the primary
109: * table is many-to-one.
110: *
111: * When this property is set false, a SQL statement will be executed for each HAS_MANY relation.
112: *
113: * When this property is not set, if the primary table is limited or paginated,
114: * a SQL statement will be executed for each HAS_MANY relation.
115: * Otherwise, a single SQL statement will be executed for all.
116: *
117: * @since 1.1.4
118: */
119: public $together;
120: /**
121: * @var string the name of the AR attribute whose value should be used as index of the query result array.
122: * Defaults to null, meaning the result array will be zero-based integers.
123: * @since 1.1.5
124: */
125: public $index;
126: /**
127: * @var mixed scopes to apply
128: *
129: * This property is effective only when passing criteria to
130: * the one of the following methods:
131: * <ul>
132: * <li>{@link CActiveRecord::find()}</li>
133: * <li>{@link CActiveRecord::findAll()}</li>
134: * <li>{@link CActiveRecord::findByPk()}</li>
135: * <li>{@link CActiveRecord::findAllByPk()}</li>
136: * <li>{@link CActiveRecord::findByAttributes()}</li>
137: * <li>{@link CActiveRecord::findAllByAttributes()}</li>
138: * <li>{@link CActiveRecord::count()}</li>
139: * </ul>
140: *
141: * Can be set to one of the following:
142: * <ul>
143: * <li>One scope: $criteria->scopes='scopeName';</li>
144: * <li>Multiple scopes: $criteria->scopes=array('scopeName1','scopeName2');</li>
145: * <li>Scope with parameters: $criteria->scopes=array('scopeName'=>array($params));</li>
146: * <li>Multiple scopes with parameters: $criteria->scopes=array('scopeName1'=>array($params1),'scopeName2'=>array($params2));</li>
147: * <li>Multiple scopes with the same name: array(array('scopeName'=>array($params1)),array('scopeName'=>array($params2)));</li>
148: * </ul>
149: * @since 1.1.7
150: */
151: public $scopes;
152:
153: /**
154: * Constructor.
155: * @param array $data criteria initial property values (indexed by property name)
156: */
157: public function __construct($data=array())
158: {
159: foreach($data as $name=>$value)
160: $this->$name=$value;
161: }
162:
163: /**
164: * Remaps criteria parameters on unserialize to prevent name collisions.
165: * @since 1.1.9
166: */
167: public function __wakeup()
168: {
169: $map=array();
170: $params=array();
171: foreach($this->params as $name=>$value)
172: {
173: if(strpos($name,self::PARAM_PREFIX)===0)
174: {
175: $newName=self::PARAM_PREFIX.self::$paramCount++;
176: $map[$name]=$newName;
177: }
178: else
179: {
180: $newName=$name;
181: }
182: $params[$newName]=$value;
183: }
184: if (!empty($map))
185: {
186: $sqlContentFieldNames=array(
187: 'select',
188: 'condition',
189: 'order',
190: 'group',
191: 'join',
192: 'having',
193: );
194: foreach($sqlContentFieldNames as $field)
195: {
196: if(is_array($this->$field))
197: foreach($this->$field as $k=>$v)
198: $this->{$field}[$k]=strtr($v,$map);
199: else
200: $this->$field=strtr($this->$field,$map);
201: }
202: }
203: $this->params=$params;
204: }
205:
206: /**
207: * Appends a condition to the existing {@link condition}.
208: * The new condition and the existing condition will be concatenated via the specified operator
209: * which defaults to 'AND'.
210: * The new condition can also be an array. In this case, all elements in the array
211: * will be concatenated together via the operator.
212: * This method handles the case when the existing condition is empty.
213: * After calling this method, the {@link condition} property will be modified.
214: * @param mixed $condition the new condition. It can be either a string or an array of strings.
215: * @param string $operator the operator to join different conditions. Defaults to 'AND'.
216: * @return static the criteria object itself
217: */
218: public function addCondition($condition,$operator='AND')
219: {
220: if(is_array($condition))
221: {
222: if($condition===array())
223: return $this;
224: $condition='('.implode(') '.$operator.' (',$condition).')';
225: }
226: if($this->condition==='')
227: $this->condition=$condition;
228: else
229: $this->condition='('.$this->condition.') '.$operator.' ('.$condition.')';
230: return $this;
231: }
232:
233: /**
234: * Appends a search condition to the existing {@link condition}.
235: * The search condition and the existing condition will be concatenated via the specified operator
236: * which defaults to 'AND'.
237: * The search condition is generated using the SQL LIKE operator with the given column name and
238: * search keyword.
239: * @param string $column the column name (or a valid SQL expression)
240: * @param string $keyword the search keyword. This interpretation of the keyword is affected by the next parameter.
241: * @param boolean $escape whether the keyword should be escaped if it contains characters % or _.
242: * When this parameter is true (default), the special characters % (matches 0 or more characters)
243: * and _ (matches a single character) will be escaped, and the keyword will be surrounded with a %
244: * character on both ends. When this parameter is false, the keyword will be directly used for
245: * matching without any change.
246: * @param string $operator the operator used to concatenate the new condition with the existing one.
247: * Defaults to 'AND'.
248: * @param string $like the LIKE operator. Defaults to 'LIKE'. You may also set this to be 'NOT LIKE'.
249: * @return static the criteria object itself
250: */
251: public function addSearchCondition($column,$keyword,$escape=true,$operator='AND',$like='LIKE')
252: {
253: if($keyword=='')
254: return $this;
255: if($escape)
256: $keyword='%'.strtr($keyword,array('%'=>'\%', '_'=>'\_', '\\'=>'\\\\')).'%';
257: $condition=$column." $like ".self::PARAM_PREFIX.self::$paramCount;
258: $this->params[self::PARAM_PREFIX.self::$paramCount++]=$keyword;
259: return $this->addCondition($condition, $operator);
260: }
261:
262: /**
263: * Appends an IN condition to the existing {@link condition}.
264: * The IN condition and the existing condition will be concatenated via the specified operator
265: * which defaults to 'AND'.
266: * The IN condition is generated by using the SQL IN operator which requires the specified
267: * column value to be among the given list of values.
268: * @param string $column the column name (or a valid SQL expression)
269: * @param array $values list of values that the column value should be in
270: * @param string $operator the operator used to concatenate the new condition with the existing one.
271: * Defaults to 'AND'.
272: * @return static the criteria object itself
273: */
274: public function addInCondition($column,$values,$operator='AND')
275: {
276: if(($n=count($values))<1)
277: $condition='0=1'; // 0=1 is used because in MSSQL value alone can't be used in WHERE
278: elseif($n===1)
279: {
280: $value=reset($values);
281: if($value===null)
282: $condition=$column.' IS NULL';
283: else
284: {
285: $condition=$column.'='.self::PARAM_PREFIX.self::$paramCount;
286: $this->params[self::PARAM_PREFIX.self::$paramCount++]=$value;
287: }
288: }
289: else
290: {
291: $params=array();
292: foreach($values as $value)
293: {
294: $params[]=self::PARAM_PREFIX.self::$paramCount;
295: $this->params[self::PARAM_PREFIX.self::$paramCount++]=$value;
296: }
297: $condition=$column.' IN ('.implode(', ',$params).')';
298: }
299: return $this->addCondition($condition,$operator);
300: }
301:
302: /**
303: * Appends an NOT IN condition to the existing {@link condition}.
304: * The NOT IN condition and the existing condition will be concatenated via the specified operator
305: * which defaults to 'AND'.
306: * The NOT IN condition is generated by using the SQL NOT IN operator which requires the specified
307: * column value to be among the given list of values.
308: * @param string $column the column name (or a valid SQL expression)
309: * @param array $values list of values that the column value should not be in
310: * @param string $operator the operator used to concatenate the new condition with the existing one.
311: * Defaults to 'AND'.
312: * @return static the criteria object itself
313: * @since 1.1.1
314: */
315: public function addNotInCondition($column,$values,$operator='AND')
316: {
317: if(($n=count($values))<1)
318: return $this;
319: if($n===1)
320: {
321: $value=reset($values);
322: if($value===null)
323: $condition=$column.' IS NOT NULL';
324: else
325: {
326: $condition=$column.'!='.self::PARAM_PREFIX.self::$paramCount;
327: $this->params[self::PARAM_PREFIX.self::$paramCount++]=$value;
328: }
329: }
330: else
331: {
332: $params=array();
333: foreach($values as $value)
334: {
335: $params[]=self::PARAM_PREFIX.self::$paramCount;
336: $this->params[self::PARAM_PREFIX.self::$paramCount++]=$value;
337: }
338: $condition=$column.' NOT IN ('.implode(', ',$params).')';
339: }
340: return $this->addCondition($condition,$operator);
341: }
342:
343: /**
344: * Appends a condition for matching the given list of column values.
345: * The generated condition will be concatenated to the existing {@link condition}
346: * via the specified operator which defaults to 'AND'.
347: * The condition is generated by matching each column and the corresponding value.
348: * @param array $columns list of column names and values to be matched (name=>value)
349: * @param string $columnOperator the operator to concatenate multiple column matching condition. Defaults to 'AND'.
350: * @param string $operator the operator used to concatenate the new condition with the existing one.
351: * Defaults to 'AND'.
352: * @return static the criteria object itself
353: */
354: public function addColumnCondition($columns,$columnOperator='AND',$operator='AND')
355: {
356: $params=array();
357: foreach($columns as $name=>$value)
358: {
359: if($value===null)
360: $params[]=$name.' IS NULL';
361: else
362: {
363: $params[]=$name.'='.self::PARAM_PREFIX.self::$paramCount;
364: $this->params[self::PARAM_PREFIX.self::$paramCount++]=$value;
365: }
366: }
367: return $this->addCondition(implode(" $columnOperator ",$params), $operator);
368: }
369:
370: /**
371: * Adds a comparison expression to the {@link condition} property.
372: *
373: * This method is a helper that appends to the {@link condition} property
374: * with a new comparison expression. The comparison is done by comparing a column
375: * with the given value using some comparison operator.
376: *
377: * The comparison operator is intelligently determined based on the first few
378: * characters in the given value. In particular, it recognizes the following operators
379: * if they appear as the leading characters in the given value:
380: * <ul>
381: * <li><code><</code>: the column must be less than the given value.</li>
382: * <li><code>></code>: the column must be greater than the given value.</li>
383: * <li><code><=</code>: the column must be less than or equal to the given value.</li>
384: * <li><code>>=</code>: the column must be greater than or equal to the given value.</li>
385: * <li><code><></code>: the column must not be the same as the given value.
386: * Note that when $partialMatch is true, this would mean the value must not be a substring
387: * of the column.</li>
388: * <li><code>=</code>: the column must be equal to the given value.</li>
389: * <li>none of the above: the column must be equal to the given value. Note that when $partialMatch
390: * is true, this would mean the value must be the same as the given value or be a substring of it.</li>
391: * </ul>
392: *
393: * Note that any surrounding white spaces will be removed from the value before comparison.
394: * When the value is empty, no comparison expression will be added to the search condition.
395: *
396: * @param string $column the name of the column to be searched
397: * @param mixed $value the column value to be compared with. If the value is a string, the aforementioned
398: * intelligent comparison will be conducted. If the value is an array, the comparison is done
399: * by exact match of any of the value in the array. If the string or the array is empty,
400: * the existing search condition will not be modified.
401: * @param boolean $partialMatch whether the value should consider partial text match (using LIKE and NOT LIKE operators).
402: * Defaults to false, meaning exact comparison.
403: * @param string $operator the operator used to concatenate the new condition with the existing one.
404: * Defaults to 'AND'.
405: * @param boolean $escape whether the value should be escaped if $partialMatch is true and
406: * the value contains characters % or _. When this parameter is true (default),
407: * the special characters % (matches 0 or more characters)
408: * and _ (matches a single character) will be escaped, and the value will be surrounded with a %
409: * character on both ends. When this parameter is false, the value will be directly used for
410: * matching without any change.
411: * @return static the criteria object itself
412: * @since 1.1.1
413: */
414: public function compare($column, $value, $partialMatch=false, $operator='AND', $escape=true)
415: {
416: if(is_array($value))
417: {
418: if($value===array())
419: return $this;
420: return $this->addInCondition($column,$value,$operator);
421: }
422: else
423: $value="$value";
424:
425: if(preg_match('/^(?:\s*(<>|<=|>=|<|>|=))?(.*)$/',$value,$matches))
426: {
427: $value=$matches[2];
428: $op=$matches[1];
429: }
430: else
431: $op='';
432:
433: if($value==='')
434: return $this;
435:
436: if($partialMatch)
437: {
438: if($op==='')
439: return $this->addSearchCondition($column,$value,$escape,$operator);
440: if($op==='<>')
441: return $this->addSearchCondition($column,$value,$escape,$operator,'NOT LIKE');
442: }
443: elseif($op==='')
444: $op='=';
445:
446: $this->addCondition($column.$op.self::PARAM_PREFIX.self::$paramCount,$operator);
447: $this->params[self::PARAM_PREFIX.self::$paramCount++]=$value;
448:
449: return $this;
450: }
451:
452: /**
453: * Adds a between condition to the {@link condition} property.
454: *
455: * The new between condition and the existing condition will be concatenated via
456: * the specified operator which defaults to 'AND'.
457: * If one or both values are empty then the condition is not added to the existing condition.
458: * This method handles the case when the existing condition is empty.
459: * After calling this method, the {@link condition} property will be modified.
460: * @param string $column the name of the column to search between.
461: * @param string $valueStart the beginning value to start the between search.
462: * @param string $valueEnd the ending value to end the between search.
463: * @param string $operator the operator used to concatenate the new condition with the existing one.
464: * Defaults to 'AND'.
465: * @return static the criteria object itself
466: * @since 1.1.2
467: */
468: public function addBetweenCondition($column,$valueStart,$valueEnd,$operator='AND')
469: {
470: if($valueStart==='' || $valueEnd==='')
471: return $this;
472:
473: $paramStart=self::PARAM_PREFIX.self::$paramCount++;
474: $paramEnd=self::PARAM_PREFIX.self::$paramCount++;
475: $this->params[$paramStart]=$valueStart;
476: $this->params[$paramEnd]=$valueEnd;
477: $condition="$column BETWEEN $paramStart AND $paramEnd";
478:
479: return $this->addCondition($condition,$operator);
480: }
481:
482: /**
483: * Merges with another criteria.
484: * In general, the merging makes the resulting criteria more restrictive.
485: * For example, if both criterias have conditions, they will be 'AND' together.
486: * Also, the criteria passed as the parameter takes precedence in case
487: * two options cannot be merged (e.g. LIMIT, OFFSET).
488: * @param mixed $criteria the criteria to be merged with. Either an array or CDbCriteria.
489: * @param string|boolean $operator the operator used to concatenate where and having conditions. Defaults to 'AND'.
490: * For backwards compatibility a boolean value can be passed:
491: * - 'false' for 'OR'
492: * - 'true' for 'AND'
493: */
494: public function mergeWith($criteria,$operator='AND')
495: {
496: if(is_bool($operator))
497: $operator=$operator ? 'AND' : 'OR';
498: if(is_array($criteria))
499: $criteria=new self($criteria);
500: if($this->select!==$criteria->select)
501: {
502: if($this->select==='*')
503: $this->select=$criteria->select;
504: elseif($criteria->select!=='*')
505: {
506: $select1=is_string($this->select)?preg_split('/\s*,\s*/',trim($this->select),-1,PREG_SPLIT_NO_EMPTY):$this->select;
507: $select2=is_string($criteria->select)?preg_split('/\s*,\s*/',trim($criteria->select),-1,PREG_SPLIT_NO_EMPTY):$criteria->select;
508: $this->select=array_merge($select1,array_diff($select2,$select1));
509: }
510: }
511:
512: if($this->condition!==$criteria->condition)
513: {
514: if($this->condition==='')
515: $this->condition=$criteria->condition;
516: elseif($criteria->condition!=='')
517: $this->condition="({$this->condition}) $operator ({$criteria->condition})";
518: }
519:
520: if($this->params!==$criteria->params)
521: $this->params=array_merge($this->params,$criteria->params);
522:
523: if($criteria->limit>=0)
524: $this->limit=$criteria->limit;
525:
526: if($criteria->offset>=0)
527: $this->offset=$criteria->offset;
528:
529: if($criteria->alias!==null)
530: $this->alias=$criteria->alias;
531:
532: if($this->order!==$criteria->order)
533: {
534: if($this->order==='')
535: $this->order=$criteria->order;
536: elseif($criteria->order!=='')
537: $this->order=$criteria->order.', '.$this->order;
538: }
539:
540: if($this->group!==$criteria->group)
541: {
542: if($this->group==='')
543: $this->group=$criteria->group;
544: elseif($criteria->group!=='')
545: $this->group.=', '.$criteria->group;
546: }
547:
548: if($this->join!==$criteria->join)
549: {
550: if($this->join==='')
551: $this->join=$criteria->join;
552: elseif($criteria->join!=='')
553: $this->join.=' '.$criteria->join;
554: }
555:
556: if($this->having!==$criteria->having)
557: {
558: if($this->having==='')
559: $this->having=$criteria->having;
560: elseif($criteria->having!=='')
561: $this->having="({$this->having}) $operator ({$criteria->having})";
562: }
563:
564: if($criteria->distinct>0)
565: $this->distinct=$criteria->distinct;
566:
567: if($criteria->together!==null)
568: $this->together=$criteria->together;
569:
570: if($criteria->index!==null)
571: $this->index=$criteria->index;
572:
573: if(empty($this->scopes))
574: $this->scopes=$criteria->scopes;
575: elseif(!empty($criteria->scopes))
576: {
577: $scopes1=(array)$this->scopes;
578: $scopes2=(array)$criteria->scopes;
579: foreach($scopes1 as $k=>$v)
580: {
581: if(is_integer($k))
582: $scopes[]=$v;
583: elseif(isset($scopes2[$k]))
584: $scopes[]=array($k=>$v);
585: else
586: $scopes[$k]=$v;
587: }
588: foreach($scopes2 as $k=>$v)
589: {
590: if(is_integer($k))
591: $scopes[]=$v;
592: elseif(isset($scopes1[$k]))
593: $scopes[]=array($k=>$v);
594: else
595: $scopes[$k]=$v;
596: }
597: $this->scopes=$scopes;
598: }
599:
600: if(empty($this->with))
601: $this->with=$criteria->with;
602: elseif(!empty($criteria->with))
603: {
604: $this->with=(array)$this->with;
605: foreach((array)$criteria->with as $k=>$v)
606: {
607: if(is_integer($k))
608: $this->with[]=$v;
609: elseif(isset($this->with[$k]))
610: {
611: $excludes=array();
612: foreach(array('joinType','on') as $opt)
613: {
614: if(isset($this->with[$k][$opt]))
615: $excludes[$opt]=$this->with[$k][$opt];
616: if(isset($v[$opt]))
617: $excludes[$opt]= ($opt==='on' && isset($excludes[$opt]) && $v[$opt]!==$excludes[$opt]) ?
618: "($excludes[$opt]) AND $v[$opt]" : $v[$opt];
619: unset($this->with[$k][$opt]);
620: unset($v[$opt]);
621: }
622: $this->with[$k]=new self($this->with[$k]);
623: $this->with[$k]->mergeWith($v,$operator);
624: $this->with[$k]=$this->with[$k]->toArray();
625: if (count($excludes)!==0)
626: $this->with[$k]=CMap::mergeArray($this->with[$k],$excludes);
627: }
628: else
629: $this->with[$k]=$v;
630: }
631: }
632: }
633:
634: /**
635: * @return array the array representation of the criteria
636: */
637: public function toArray()
638: {
639: $result=array();
640: foreach(array('select', 'condition', 'params', 'limit', 'offset', 'order', 'group', 'join', 'having', 'distinct', 'scopes', 'with', 'alias', 'index', 'together') as $name)
641: $result[$name]=$this->$name;
642: return $result;
643: }
644: }
645: