expression with alias

Hi Gentlemen,

I have two tables todo_list and tasks with one-to-many relation. I need to get all todos which have all the task completed and total tasks > 10 for example.

This SQL works and returns what I need:

SELECT tl.*, 

  COUNT(CASE t.status WHEN 1 THEN END) new_tasks,

  COUNT(CASE t.status WHEN 2 THEN END) done_tasks,

  COUNT( total_tasks

FROM todo_list tl

  LEFT JOIN tasks t ON = t.todo_id


HAVING new_tasks > 0 AND total_tasks > 10


status values: 1 - for new tasks, 2 - for completed tasks.

How to put this SQL to controller correctly? I put this code to controller:

public function actionTodos($id)


	$criteria = new CDbCriteria;

	$aliasTodo = Todo::model()->getTableAlias(false, false);

	$aliasTask = Task::model()->getTableAlias(false, false);

	$criteria->select = array(


		'COUNT(CASE '.$aliasTask.'.status WHEN 1 THEN '.$aliasTask.'.id END) new_tasks',

		'COUNT(CASE '.$aliasTask.'.status WHEN 2 THEN '.$aliasTask.'.id END) done_tasks',

		'COUNT('.$aliasTask.'.id) total_tasks',


	$criteria->with = 'tasks';

	$criteria->group = $aliasTodo.'.id';

	$criteria->having = 'new_tasks = 0 AND total_tasks > 10'; // or other expressions for different goals

	$criteria->order = 'NULL';







and got error:

Active record "Todo" is trying to select an invalid column "COUNT(CASE t.status WHEN 1 THEN END) open_tasks".

Note, the column must exist in the table or be an expression with alias.

new_tasks, done_tasks and total_task fields must be declare in model.

public function relations()


	return array(

		'tasks' => array(self::HAS_MANY, 'Task', 'todo_id', 'together'=>true, 'alias'=>'ts'),

		'total_tasks' => array(self::STAT, 'Task', 'todo_id'),



How to add new_tasks and done_tasks fields to model?

Thanks for the advice!..

I would separate your table in two differents.