Versions
Look up a class, method, property or event

CDbCriteria

Package system.db.schema
Inheritance class CDbCriteria » CComponent
Since 1.0
Source Code framework/db/schema/CDbCriteria.php
CDbCriteria represents a query criteria, such as conditions, ordering by, limit/offset.

It can be used in AR query methods such as CActiveRecord::find and CActiveRecord::findAll.

$criteria=new CDbCriteria(); $criteria->compare('status',Post::STATUS_ACTIVE); $criteria->addInCondition('id',array(1,2,3,4,5,6));

$posts = Post::model()->findAll($criteria);

Public Properties

Hide inherited properties

PropertyTypeDescriptionDefined By
alias string the alias name of the table. CDbCriteria
condition string query condition. CDbCriteria
distinct boolean whether to select distinct rows of data only. CDbCriteria
group string how to group the query results. CDbCriteria
having string the condition to be applied with GROUP-BY clause. CDbCriteria
index string the name of the AR attribute whose value should be used as index of the query result array. CDbCriteria
join string how to join with other tables. CDbCriteria
limit integer maximum number of records to be returned. CDbCriteria
offset integer zero-based offset from where the records are to be returned. CDbCriteria
order string how to sort the query results. CDbCriteria
paramCount integer the global counter for anonymous binding parameters. CDbCriteria
params array list of query parameter values indexed by parameter placeholders. CDbCriteria
scopes mixed scopes to apply

This property is effective only when passing criteria to the one of the following methods:

Can be set to one of the following:
  • One scope: $criteria->scopes='scopeName';
  • Multiple scopes: $criteria->scopes=array('scopeName1','scopeName2');
  • Scope with parameters: $criteria->scopes=array('scopeName'=>array($params));
  • Multiple scopes with parameters: $criteria->scopes=array('scopeName1'=>array($params1),'scopeName2'=>array($params2));
  • Multiple scopes with the same name: array(array('scopeName'=>array($params1)),array('scopeName'=>array($params2)));
CDbCriteria
select mixed the columns being selected. CDbCriteria
together boolean whether the foreign tables should be joined with the primary table in a single SQL. CDbCriteria
with mixed the relational query criteria. CDbCriteria

Public Methods

Hide inherited methods

MethodDescriptionDefined By
__call() Calls the named method which is not a class method. CComponent
__construct() Constructor. CDbCriteria
__get() Returns a property value, an event handler list or a behavior based on its name. CComponent
__isset() Checks if a property value is null. CComponent
__set() Sets value of a component property. CComponent
__unset() Sets a component property to be null. CComponent
__wakeup() Remaps criteria parameters on unserialize to prevent name collisions. CDbCriteria
addBetweenCondition() Adds a between condition to the condition property. CDbCriteria
addColumnCondition() Appends a condition for matching the given list of column values. CDbCriteria
addCondition() Appends a condition to the existing condition. CDbCriteria
addInCondition() Appends an IN condition to the existing condition. CDbCriteria
addNotInCondition() Appends an NOT IN condition to the existing condition. CDbCriteria
addSearchCondition() Appends a search condition to the existing condition. CDbCriteria
asa() Returns the named behavior object. CComponent
attachBehavior() Attaches a behavior to this component. CComponent
attachBehaviors() Attaches a list of behaviors to the component. CComponent
attachEventHandler() Attaches an event handler to an event. CComponent
canGetProperty() Determines whether a property can be read. CComponent
canSetProperty() Determines whether a property can be set. CComponent
compare() Adds a comparison expression to the condition property. CDbCriteria
detachBehavior() Detaches a behavior from the component. CComponent
detachBehaviors() Detaches all behaviors from the component. CComponent
detachEventHandler() Detaches an existing event handler. CComponent
disableBehavior() Disables an attached behavior. CComponent
disableBehaviors() Disables all behaviors attached to this component. CComponent
enableBehavior() Enables an attached behavior. CComponent
enableBehaviors() Enables all behaviors attached to this component. CComponent
evaluateExpression() Evaluates a PHP expression or callback under the context of this component. CComponent
getEventHandlers() Returns the list of attached event handlers for an event. CComponent
hasEvent() Determines whether an event is defined. CComponent
hasEventHandler() Checks whether the named event has attached handlers. CComponent
hasProperty() Determines whether a property is defined. CComponent
mergeWith() Merges with another criteria. CDbCriteria
raiseEvent() Raises an event. CComponent
toArray() CDbCriteria

Property Details

alias property
public string $alias;

the alias name of the table. If not set, it means the alias is 't'.

condition property
public string $condition;

query condition. This refers to the WHERE clause in an SQL statement. For example, age>31 AND team=1.

distinct property
public boolean $distinct;

whether to select distinct rows of data only. If this is set true, the SELECT clause would be changed to SELECT DISTINCT.

group property
public string $group;

how to group the query results. This refers to the GROUP BY clause in an SQL statement. For example, 'projectID, teamID'.

having property
public string $having;

the condition to be applied with GROUP-BY clause. For example, 'SUM(revenue)<50000'.

index property (available since v1.1.5)
public string $index;

the name of the AR attribute whose value should be used as index of the query result array. Defaults to null, meaning the result array will be zero-based integers.

join property
public string $join;

how to join with other tables. This refers to the JOIN clause in an SQL statement. For example, 'LEFT JOIN users ON users.id=authorID'.

limit property
public integer $limit;

maximum number of records to be returned. If less than 0, it means no limit.

offset property
public integer $offset;

zero-based offset from where the records are to be returned. If less than 0, it means starting from the beginning.

order property
public string $order;

how to sort the query results. This refers to the ORDER BY clause in an SQL statement.

paramCount property
public static integer $paramCount;

the global counter for anonymous binding parameters. This counter is used for generating the name for the anonymous parameters.

params property
public array $params;

list of query parameter values indexed by parameter placeholders. For example, array(':name'=>'Dan', ':age'=>31).

scopes property (available since v1.1.7)
public mixed $scopes;

scopes to apply

This property is effective only when passing criteria to the one of the following methods:



Can be set to one of the following:
  • One scope: $criteria->scopes='scopeName';
  • Multiple scopes: $criteria->scopes=array('scopeName1','scopeName2');
  • Scope with parameters: $criteria->scopes=array('scopeName'=>array($params));
  • Multiple scopes with parameters: $criteria->scopes=array('scopeName1'=>array($params1),'scopeName2'=>array($params2));
  • Multiple scopes with the same name: array(array('scopeName'=>array($params1)),array('scopeName'=>array($params2)));

select property
public mixed $select;

the columns being selected. This refers to the SELECT clause in an SQL statement. The property can be either a string (column names separated by commas) or an array of column names. Defaults to '*', meaning all columns.

together property (available since v1.1.4)
public boolean $together;

whether the foreign tables should be joined with the primary table in a single SQL. This property is only used in relational AR queries for HAS_MANY and MANY_MANY relations.

When this property is set true, only a single SQL will be executed for a relational AR query, even if the primary table is limited and the relationship between a foreign table and the primary table is many-to-one.

When this property is set false, a SQL statement will be executed for each HAS_MANY relation.

When this property is not set, if the primary table is limited or paginated, a SQL statement will be executed for each HAS_MANY relation. Otherwise, a single SQL statement will be executed for all.

with property (available since v1.1.0)
public mixed $with;

the relational query criteria. This is used for fetching related objects in eager loading fashion. This property is effective only when the criteria is passed as a parameter to the following methods of CActiveRecord:

The property value will be used as the parameter to the CActiveRecord::with() method to perform the eager loading. Please refer to CActiveRecord::with() on how to specify this parameter.

Method Details

__construct() method
public void __construct(array $data=array ( ))
$data array criteria initial property values (indexed by property name)
Source Code: framework/db/schema/CDbCriteria.php#157 (show)
public function __construct($data=array())
{
    foreach(
$data as $name=>$value)
        
$this->$name=$value;
}

Constructor.

__wakeup() method (available since v1.1.9)
public void __wakeup()
Source Code: framework/db/schema/CDbCriteria.php#167 (show)
public function __wakeup()
{
    
$map=array();
    
$params=array();
    foreach(
$this->params as $name=>$value)
    {
        if(
strpos($name,self::PARAM_PREFIX)===0)
        {
            
$newName=self::PARAM_PREFIX.self::$paramCount++;
            
$map[$name]=$newName;
        }
        else
        {
            
$newName=$name;
        }
        
$params[$newName]=$value;
    }
    if (!empty(
$map))
    {
        
$sqlContentFieldNames=array(
            
'select',
            
'condition',
            
'order',
            
'group',
            
'join',
            
'having',
        );
        foreach(
$sqlContentFieldNames as $field)
        {
            if(
is_array($this->$field))
                foreach(
$this->$field as $k=>$v)
                    
$this->{$field}[$k]=strtr($v,$map);
            else
                
$this->$field=strtr($this->$field,$map);
        }
    }
    
$this->params=$params;
}

Remaps criteria parameters on unserialize to prevent name collisions.

addBetweenCondition() method (available since v1.1.2)
public CDbCriteria addBetweenCondition(string $column, string $valueStart, string $valueEnd, string $operator='AND')
$column string the name of the column to search between.
$valueStart string the beginning value to start the between search.
$valueEnd string the ending value to end the between search.
$operator string the operator used to concatenate the new condition with the existing one. Defaults to 'AND'.
{return} CDbCriteria the criteria object itself
Source Code: framework/db/schema/CDbCriteria.php#468 (show)
public function addBetweenCondition($column,$valueStart,$valueEnd,$operator='AND')
{
    if(
$valueStart==='' || $valueEnd==='')
        return 
$this;

    
$paramStart=self::PARAM_PREFIX.self::$paramCount++;
    
$paramEnd=self::PARAM_PREFIX.self::$paramCount++;
    
$this->params[$paramStart]=$valueStart;
    
$this->params[$paramEnd]=$valueEnd;
    
$condition="$column BETWEEN $paramStart AND $paramEnd";

    return 
$this->addCondition($condition,$operator);
}

Adds a between condition to the condition property.

The new between condition and the existing condition will be concatenated via the specified operator which defaults to 'AND'. If one or both values are empty then the condition is not added to the existing condition. This method handles the case when the existing condition is empty. After calling this method, the condition property will be modified.

addColumnCondition() method
public CDbCriteria addColumnCondition(array $columns, string $columnOperator='AND', string $operator='AND')
$columns array list of column names and values to be matched (name=>value)
$columnOperator string the operator to concatenate multiple column matching condition. Defaults to 'AND'.
$operator string the operator used to concatenate the new condition with the existing one. Defaults to 'AND'.
{return} CDbCriteria the criteria object itself
Source Code: framework/db/schema/CDbCriteria.php#354 (show)
public function addColumnCondition($columns,$columnOperator='AND',$operator='AND')
{
    
$params=array();
    foreach(
$columns as $name=>$value)
    {
        if(
$value===null)
            
$params[]=$name.' IS NULL';
        else
        {
            
$params[]=$name.'='.self::PARAM_PREFIX.self::$paramCount;
            
$this->params[self::PARAM_PREFIX.self::$paramCount++]=$value;
        }
    }
    return 
$this->addCondition(implode($columnOperator ",$params), $operator);
}

Appends a condition for matching the given list of column values. The generated condition will be concatenated to the existing condition via the specified operator which defaults to 'AND'. The condition is generated by matching each column and the corresponding value.

addCondition() method
public CDbCriteria addCondition(mixed $condition, string $operator='AND')
$condition mixed the new condition. It can be either a string or an array of strings.
$operator string the operator to join different conditions. Defaults to 'AND'.
{return} CDbCriteria the criteria object itself
Source Code: framework/db/schema/CDbCriteria.php#218 (show)
public function addCondition($condition,$operator='AND')
{
    if(
is_array($condition))
    {
        if(
$condition===array())
            return 
$this;
        
$condition='('.implode(') '.$operator.' (',$condition).')';
    }
    if(
$this->condition==='')
        
$this->condition=$condition;
    else
        
$this->condition='('.$this->condition.') '.$operator.' ('.$condition.')';
    return 
$this;
}

Appends a condition to the existing condition. The new condition and the existing condition will be concatenated via the specified operator which defaults to 'AND'. The new condition can also be an array. In this case, all elements in the array will be concatenated together via the operator. This method handles the case when the existing condition is empty. After calling this method, the condition property will be modified.

addInCondition() method
public CDbCriteria addInCondition(string $column, array $values, string $operator='AND')
$column string the column name (or a valid SQL expression)
$values array list of values that the column value should be in
$operator string the operator used to concatenate the new condition with the existing one. Defaults to 'AND'.
{return} CDbCriteria the criteria object itself
Source Code: framework/db/schema/CDbCriteria.php#274 (show)
public function addInCondition($column,$values,$operator='AND')
{
    if((
$n=count($values))<1)
        
$condition='0=1'// 0=1 is used because in MSSQL value alone can't be used in WHERE
    
elseif($n===1)
    {
        
$value=reset($values);
        if(
$value===null)
            
$condition=$column.' IS NULL';
        else
        {
            
$condition=$column.'='.self::PARAM_PREFIX.self::$paramCount;
            
$this->params[self::PARAM_PREFIX.self::$paramCount++]=$value;
        }
    }
    else
    {
        
$params=array();
        foreach(
$values as $value)
        {
            
$params[]=self::PARAM_PREFIX.self::$paramCount;
            
$this->params[self::PARAM_PREFIX.self::$paramCount++]=$value;
        }
        
$condition=$column.' IN ('.implode(', ',$params).')';
    }
    return 
$this->addCondition($condition,$operator);
}

Appends an IN condition to the existing condition. The IN condition and the existing condition will be concatenated via the specified operator which defaults to 'AND'. The IN condition is generated by using the SQL IN operator which requires the specified column value to be among the given list of values.

addNotInCondition() method (available since v1.1.1)
public CDbCriteria addNotInCondition(string $column, array $values, string $operator='AND')
$column string the column name (or a valid SQL expression)
$values array list of values that the column value should not be in
$operator string the operator used to concatenate the new condition with the existing one. Defaults to 'AND'.
{return} CDbCriteria the criteria object itself
Source Code: framework/db/schema/CDbCriteria.php#315 (show)
public function addNotInCondition($column,$values,$operator='AND')
{
    if((
$n=count($values))<1)
        return 
$this;
    if(
$n===1)
    {
        
$value=reset($values);
        if(
$value===null)
            
$condition=$column.' IS NOT NULL';
        else
        {
            
$condition=$column.'!='.self::PARAM_PREFIX.self::$paramCount;
            
$this->params[self::PARAM_PREFIX.self::$paramCount++]=$value;
        }
    }
    else
    {
        
$params=array();
        foreach(
$values as $value)
        {
            
$params[]=self::PARAM_PREFIX.self::$paramCount;
            
$this->params[self::PARAM_PREFIX.self::$paramCount++]=$value;
        }
        
$condition=$column.' NOT IN ('.implode(', ',$params).')';
    }
    return 
$this->addCondition($condition,$operator);
}

Appends an NOT IN condition to the existing condition. The NOT IN condition and the existing condition will be concatenated via the specified operator which defaults to 'AND'. The NOT IN condition is generated by using the SQL NOT IN operator which requires the specified column value to be among the given list of values.

addSearchCondition() method
public CDbCriteria addSearchCondition(string $column, string $keyword, boolean $escape=true, string $operator='AND', string $like='LIKE')
$column string the column name (or a valid SQL expression)
$keyword string the search keyword. This interpretation of the keyword is affected by the next parameter.
$escape boolean whether the keyword should be escaped if it contains characters % or _. When this parameter is true (default), the special characters % (matches 0 or more characters) and _ (matches a single character) will be escaped, and the keyword will be surrounded with a % character on both ends. When this parameter is false, the keyword will be directly used for matching without any change.
$operator string the operator used to concatenate the new condition with the existing one. Defaults to 'AND'.
$like string the LIKE operator. Defaults to 'LIKE'. You may also set this to be 'NOT LIKE'.
{return} CDbCriteria the criteria object itself
Source Code: framework/db/schema/CDbCriteria.php#251 (show)
public function addSearchCondition($column,$keyword,$escape=true,$operator='AND',$like='LIKE')
{
    if(
$keyword=='')
        return 
$this;
    if(
$escape)
        
$keyword='%'.strtr($keyword,array('%'=>'\%''_'=>'\_''\\'=>'\\\\')).'%';
    
$condition=$column.$like ".self::PARAM_PREFIX.self::$paramCount;
    
$this->params[self::PARAM_PREFIX.self::$paramCount++]=$keyword;
    return 
$this->addCondition($condition$operator);
}

Appends a search condition to the existing condition. The search condition and the existing condition will be concatenated via the specified operator which defaults to 'AND'. The search condition is generated using the SQL LIKE operator with the given column name and search keyword.

compare() method (available since v1.1.1)
public CDbCriteria compare(string $column, mixed $value, boolean $partialMatch=false, string $operator='AND', boolean $escape=true)
$column string the name of the column to be searched
$value mixed the column value to be compared with. If the value is a string, the aforementioned intelligent comparison will be conducted. If the value is an array, the comparison is done by exact match of any of the value in the array. If the string or the array is empty, the existing search condition will not be modified.
$partialMatch boolean whether the value should consider partial text match (using LIKE and NOT LIKE operators). Defaults to false, meaning exact comparison.
$operator string the operator used to concatenate the new condition with the existing one. Defaults to 'AND'.
$escape boolean whether the value should be escaped if $partialMatch is true and the value contains characters % or _. When this parameter is true (default), the special characters % (matches 0 or more characters) and _ (matches a single character) will be escaped, and the value will be surrounded with a % character on both ends. When this parameter is false, the value will be directly used for matching without any change.
{return} CDbCriteria the criteria object itself
Source Code: framework/db/schema/CDbCriteria.php#414 (show)
public function compare($column$value$partialMatch=false$operator='AND'$escape=true)
{
    if(
is_array($value))
    {
        if(
$value===array())
            return 
$this;
        return 
$this->addInCondition($column,$value,$operator);
    }
    else
        
$value="$value";

    if(
preg_match('/^(?:\s*(<>|<=|>=|<|>|=))?(.*)$/',$value,$matches))
    {
        
$value=$matches[2];
        
$op=$matches[1];
    }
    else
        
$op='';

    if(
$value==='')
        return 
$this;

    if(
$partialMatch)
    {
        if(
$op==='')
            return 
$this->addSearchCondition($column,$value,$escape,$operator);
        if(
$op==='<>')
            return 
$this->addSearchCondition($column,$value,$escape,$operator,'NOT LIKE');
    }
    elseif(
$op==='')
        
$op='=';

    
$this->addCondition($column.$op.self::PARAM_PREFIX.self::$paramCount,$operator);
    
$this->params[self::PARAM_PREFIX.self::$paramCount++]=$value;

    return 
$this;
}

Adds a comparison expression to the condition property.

This method is a helper that appends to the condition property with a new comparison expression. The comparison is done by comparing a column with the given value using some comparison operator.

The comparison operator is intelligently determined based on the first few characters in the given value. In particular, it recognizes the following operators if they appear as the leading characters in the given value:

  • <: the column must be less than the given value.
  • >: the column must be greater than the given value.
  • <=: the column must be less than or equal to the given value.
  • >=: the column must be greater than or equal to the given value.
  • <>: the column must not be the same as the given value. Note that when $partialMatch is true, this would mean the value must not be a substring of the column.
  • =: the column must be equal to the given value.
  • none of the above: the column must be equal to the given value. Note that when $partialMatch is true, this would mean the value must be the same as the given value or be a substring of it.


Note that any surrounding white spaces will be removed from the value before comparison. When the value is empty, no comparison expression will be added to the search condition.

mergeWith() method
public void mergeWith(mixed $criteria, string|boolean $operator='AND')
$criteria mixed the criteria to be merged with. Either an array or CDbCriteria.
$operator string|boolean the operator used to concatenate where and having conditions. Defaults to 'AND'. For backwards compatibility a boolean value can be passed: - 'false' for 'OR' - 'true' for 'AND'
Source Code: framework/db/schema/CDbCriteria.php#494 (show)
public function mergeWith($criteria,$operator='AND')
{
    if(
is_bool($operator))
        
$operator=$operator 'AND' 'OR';
    if(
is_array($criteria))
        
$criteria=new self($criteria);
    if(
$this->select!==$criteria->select)
    {
        if(
$this->select==='*')
            
$this->select=$criteria->select;
        elseif(
$criteria->select!=='*')
        {
            
$select1=is_string($this->select)?preg_split('/\s*,\s*/',trim($this->select),-1,PREG_SPLIT_NO_EMPTY):$this->select;
            
$select2=is_string($criteria->select)?preg_split('/\s*,\s*/',trim($criteria->select),-1,PREG_SPLIT_NO_EMPTY):$criteria->select;
            
$this->select=array_merge($select1,array_diff($select2,$select1));
        }
    }

    if(
$this->condition!==$criteria->condition)
    {
        if(
$this->condition==='')
            
$this->condition=$criteria->condition;
        elseif(
$criteria->condition!=='')
            
$this->condition="({$this->condition}$operator ({$criteria->condition})";
    }

    if(
$this->params!==$criteria->params)
        
$this->params=array_merge($this->params,$criteria->params);

    if(
$criteria->limit>0)
        
$this->limit=$criteria->limit;

    if(
$criteria->offset>=0)
        
$this->offset=$criteria->offset;

    if(
$criteria->alias!==null)
        
$this->alias=$criteria->alias;

    if(
$this->order!==$criteria->order)
    {
        if(
$this->order==='')
            
$this->order=$criteria->order;
        elseif(
$criteria->order!=='')
            
$this->order=$criteria->order.', '.$this->order;
    }

    if(
$this->group!==$criteria->group)
    {
        if(
$this->group==='')
            
$this->group=$criteria->group;
        elseif(
$criteria->group!=='')
            
$this->group.=', '.$criteria->group;
    }

    if(
$this->join!==$criteria->join)
    {
        if(
$this->join==='')
            
$this->join=$criteria->join;
        elseif(
$criteria->join!=='')
            
$this->join.=' '.$criteria->join;
    }

    if(
$this->having!==$criteria->having)
    {
        if(
$this->having==='')
            
$this->having=$criteria->having;
        elseif(
$criteria->having!=='')
            
$this->having="({$this->having}$operator ({$criteria->having})";
    }

    if(
$criteria->distinct>0)
        
$this->distinct=$criteria->distinct;

    if(
$criteria->together!==null)
        
$this->together=$criteria->together;

    if(
$criteria->index!==null)
        
$this->index=$criteria->index;

    if(empty(
$this->scopes))
        
$this->scopes=$criteria->scopes;
    elseif(!empty(
$criteria->scopes))
    {
        
$scopes1=(array)$this->scopes;
        
$scopes2=(array)$criteria->scopes;
        foreach(
$scopes1 as $k=>$v)
        {
            if(
is_integer($k))
                
$scopes[]=$v;
            elseif(isset(
$scopes2[$k]))
                
$scopes[]=array($k=>$v);
            else
                
$scopes[$k]=$v;
        }
        foreach(
$scopes2 as $k=>$v)
        {
            if(
is_integer($k))
                
$scopes[]=$v;
            elseif(isset(
$scopes1[$k]))
                
$scopes[]=array($k=>$v);
            else
                
$scopes[$k]=$v;
        }
        
$this->scopes=$scopes;
    }

    if(empty(
$this->with))
        
$this->with=$criteria->with;
    elseif(!empty(
$criteria->with))
    {
        
$this->with=(array)$this->with;
        foreach((array)
$criteria->with as $k=>$v)
        {
            if(
is_integer($k))
                
$this->with[]=$v;
            elseif(isset(
$this->with[$k]))
            {
                
$excludes=array();
                foreach(array(
'joinType','on') as $opt)
                {
                    if(isset(
$this->with[$k][$opt]))
                        
$excludes[$opt]=$this->with[$k][$opt];
                    if(isset(
$v[$opt]))
                        
$excludes[$opt]= ($opt==='on' && isset($excludes[$opt]) && $v[$opt]!==$excludes[$opt]) ?
                            
"($excludes[$opt]) AND $v[$opt]$v[$opt];
                    unset(
$this->with[$k][$opt]);
                    unset(
$v[$opt]);
                }
                
$this->with[$k]=new self($this->with[$k]);
                
$this->with[$k]->mergeWith($v,$operator);
                
$this->with[$k]=$this->with[$k]->toArray();
                if (
count($excludes)!==0)
                    
$this->with[$k]=CMap::mergeArray($this->with[$k],$excludes);
            }
            else
                
$this->with[$k]=$v;
        }
    }
}

Merges with another criteria. In general, the merging makes the resulting criteria more restrictive. For example, if both criterias have conditions, they will be 'AND' together. Also, the criteria passed as the parameter takes precedence in case two options cannot be merged (e.g. LIMIT, OFFSET).

toArray() method
public array toArray()
{return} array the array representation of the criteria
Source Code: framework/db/schema/CDbCriteria.php#637 (show)
public function toArray()
{
    
$result=array();
    foreach(array(
'select''condition''params''limit''offset''order''group''join''having''distinct''scopes''with''alias''index''together') as $name)
        
$result[$name]=$this->$name;
    return 
$result;
}

Total 8 comments

#7422 report it
c@cba at 2012/03/21 07:02pm
use of 'join' to implement LEFT JOIN

Let's say we have tables/models user and group, and user_group. One user can belong to many groups.
We want to find the users u for which one of the following is true:

  1. there is an entry in user_group ug with ug.user_id = u.id, but (group_id = 0)
  2. there is no entry in user_group ug with ug.user_id = u.id

The following code accomplishes that:

$criteria = new CDbCriteria;
$criteria->mergeWith(array(
    'join'=>'LEFT JOIN user_group ug ON ug.user_id = t.id',
    'condition'=>'ug.group_id = 0 OR ug.group_id IS NULL',
));
User::model()->findAll($criteria);
#6856 report it
marcovtwout at 2012/02/09 08:27am
Different ways to compare column values with criteria

These options all result in the exact same query: SELECT (..) WHERE (t.id=:ycp0).

Choose the one that you like best:

$criteria = new CDbCriteria;
// Classic method
$criteria->addCondition('t.id = :id');
$criteria->params = array(':id' => Yii::app()->user->id);
// Often used in search functions. Note: if passed value is empty, the WHERE is not added!
$criteria->compare('t.id', Yii::app()->user->id);
// This is my current favorite
$criteria->addColumnCondition(array('t.id' => Yii::app()->user->id));
// A bit weird here, but you can also do this
$criteria->addInCondition('t.id', array(Yii::app()->user->id));
#6449 report it
gesti at 2012/01/11 09:55am
$criteria->togeather = true;

Not the first time I bump into this: If your model has a HAS_MANY in relations and you want to join it up (in case for example you want to search in the related table as well), then next to setting up the with, you also need to set the together to true. So in your model you have:

class MyFirstTable extends CActiveRecord {
...
    public function relations() {
        return array(
            'mySecondTable' => array(self::HAS_MANY, 'MySecondTable', 'second_table_id'),
        );
    }
...
}

Then to be able to search in the second table data as well you need to have the search method like this:

public function searchWithRelated() {
        $criteria = new CDbCriteria;
 
        $criteria->together = true; //without this you wont be able to search the second table's data
        $criteria->with = array('mySecondTable');
        $criteria->compare('id', $this->id, true);
        $criteria->compare('mySecondTable.column', $this->mySecondTable_column, true);
 
        return new CActiveDataProvider($this, array(
            'criteria' => $criteria,
            'sort'=>array(
                'defaultOrder'=>'t.id DESC',
            ),
            'pagination' => array(
                'pageSize' => 100,
            ),
        ));
    }
#5751 report it
got 2 doodle at 2011/11/09 11:43am
Don't waste time like I did.

Watch out, the documentation for compare shows operators like this. <: =: <=: ..etc. Don't include the comma!

$criteria->compare('year(`EventStartdate`)','>=:'.$currentYear); // this will give the wrong results with absolutely no error
$criteria->compare('year(`EventStartdate`)','>='.$currentYear); // this makes a greater than or equal to comparison.
#4414 report it
rsingh at 2011/07/06 03:17pm
When using LIMIT on a foreign tables column value, then

In my case, I was trying to return the results of my query in a descending fashion, depending on a foreign table's column. So I used code like

$criteria = new CDbCriteria;
$criteria->with = array('foreign_table1',
                        'foreign_table2', 
                        'foreign_table2.foreign_table3');
$criteria->select = array('id');
$criteria->condition = "foreign_table1.col1=:col_val AND 
                        foreign_table3.col3=:col_val2";
$criteria->params = array(':col_val' => some_val, ':col_val2' => other_val);
$criteria->order = 'foreign_table3.col5 DESC';
$criteria->limit = 10;

So, my SQL would crash because I would get a column not found error, or an access error. I spent a day focusing on my condition statement, when the problem was actually with the use of the limit command. Without the LIMIT command, I got my results. With the LIMIT command, an error

So, based upon looking around in the forum, the reason was that I should have used TOGETHER, as so:

$criteria = new CDbCriteria;
$criteria->with = array('foreign_table1',
                        'foreign_table2', 
                        'foreign_table2.foreign_table3');
$criteria->together = true; // ADDED THIS
$criteria->select = array('id');
$criteria->condition = "foreign_table1.col1=:col_val AND 
                        foreign_table3.col3=:col_val2";
$criteria->params = array(':col_val' => some_val, ':col_val2' => other_val);
$criteria->order = 'foreign_table3.col5 DESC';
$criteria->limit = 10;

Because TOGETHER links together your foreign tables and allows you to execute one sql statement, this is what is needed not only to make LIMIT for a foreign table's column to work

#4327 report it
pligor at 2011/06/26 09:00am
order by desc

Since it is not explicitly defined I tested that if you have a column named let's say "priority" then you can get the results in a descending order like this:

$criteria=new CDbCriteria;
$criteria->select = "priority";
$criteria->order = "priority desc";
#259 report it
edwaa at 2010/07/26 03:29am
define 'condition' before any 'compare'

If you set the 'condition' property after you've defined any 'compares' it will overwrite all the 'compare' statements. So, be sure to define 'condition' before you call the compare method.

#1117 report it
romanoza at 2009/12/04 10:22am
addCondition potential vulnerabilities

If you use addCondition and your condition is a string, which contains SQL clauses, you should escape user supplied values.

Leave a comment

Please to leave your comment.