Yii the SQL query error based on Criteria

Question

I want to search for some model events while filtering them both on time and related users basis. Wnen i do it in the Grid it works fine. Yet, when i additionally input some data in grid inputs to search on Event's other attributes the ajax responce goes: Invalid parameter number: number of bound variables does not match number of tokens. The 'IN condition' (shown in query) is added into the criteria:

 $newCriteria = new CDbCriteria;        
 // get events id (several ) of current user
 $events = $model->getEvents($currentUser);
 // add the condition into criteria;  't' being the model table alias
 if ($events) $newCriteria->addInCondition('t.Id', $events); 
 // merge with criteria from the model (DocEvents)
 $newCriteria->mergeWith($model->criteria());

 $dataProvider = new CActiveDataProvider('DocEvents', array(
        'criteria'=>$newCriteria,//$model->criteria(), 
    ));

 $this->widget('bootstrap.widgets.TbGridView', array(
'dataProvider'=>$dataProvider,   
'filter'=>$model,'columns'=>array(
    'Id',
    'Subject',
    'EventTypeId',
    'Begin',
    'End',
    array(
        'class'=>'bootstrap.widgets.TbButtonColumn',
    ),
  ),
));     

The criteria in model file:

 public function criteria()
{
    $criteria=new CDbCriteria;

    $criteria->compare('Id',$this->Id);
    $criteria->compare('Subject',$this->Subject,true);
    $criteria->compare('Notes',$this->Notes,true);
    $criteria->compare('Place',$this->Place,true);
    $criteria->compare('EventTypeId',$this->EventTypeId,true);
    $criteria->compare('Percentage',$this->Percentage);
    $criteria->compare('ReflectInCalendar',$this->ReflectInCalendar);
    $criteria->compare('ParentId',$this->ParentId);
    $criteria->compare('Priority',$this->Priority);
    $criteria->compare('StatusId',$this->StatusId);
    $criteria->compare('Comment',$this->Comment,true);
    $criteria->compare('PlanHours',$this->PlanHours);
    $criteria->compare('Tags',$this->Tags,true);

    $conditions=array();
    if ($this->Begin) $conditions[] = 'Begin >= "' . $this->Begin . '" ';
    if ($this->End) $conditions[] = 'End <= "' . $this->End . '" ';
    $criteria->condition = implode(' AND ', $conditions);

    return $criteria;
}

How to resolve the issue?

Answer 1

The reason it fails when you input additional data is because at least one of the compare lines is being used, which ends up constructing part of the criteria's condition, along with binding a parameter for that piece of the condition (aka where clause).

However, at the end of your criteria() method, you overwrite the condition with the following line:

$criteria->condition = implode(' AND ', $conditions);

This results in having bound parameters, but all your tokens have been overwritten.

As such, use

$criteria->addCondition(implode(' AND ', $conditions));

instead to keep from overwriting the where clause that you had already created.

Or, even more simply, use the following instead of imploding your $conditions array.

if ($this->Begin) $criteria->addCondition('Begin >= "' . $this->Begin . '" ';
if ($this->End) $criteria->addCondition('End <= "' . $this->End . '" ');