If its not a project limitation just use native sql and execute the query, i find it easier when writing complex queries.
$sql=’your query’; //The query, $sql can be anything – variable name
$connection=Yii::app()->db; //Instantiating a database connection from a singleton class! Dont worry its inbuilt!
$command=$connection->createCommand($sql); //creating the command
$result=$command->queryScalar(); The command gets executed and the result is stored. queryScalar() will return only one value.
NOTE:- The way you type and the way you need to execute will differ. There are several inbuilt methods such as queryScalar(), queryAll(), query(), etc. which can be used to obtain the exact result that is required. Passing parameters to the query is just as similar where you can merrily concatenate the parameter with a “.”