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 t.id END) new_tasks,
COUNT(CASE t.status WHEN 2 THEN t.id END) done_tasks,
COUNT(t.id) total_tasks
FROM todo_list tl
LEFT JOIN tasks t ON tl.id = t.todo_id
GROUP BY tl.id
HAVING new_tasks > 0 AND total_tasks > 10
ORDER BY NULL
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';
$this->render('todos',array(
'todos'=>Todo::model()->find($criteria),
));
}
and got error:
Active record "Todo" is trying to select an invalid column "COUNT(CASE t.status WHEN 1 THEN t.id 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!..