how to convert into createCommand?

Hello, How to convert this to createCommand of yii ?

        $sql = 'select distinct u.userid,u.username,u.status as registration_status,u.email,u.activationcode,

                (select count(*) from ziad where userid = u.userid) as number_of_ads

                from ziuser u left join ziad a on (u.userid = a.userid) where u.status = 0 

                order by number_of_ads desc';

if I use that code above in myModel::model()->findAllBySql($sql);

it works, but when I tried in createCommand like this

        $subquery = Yii::app()->db->createCommand()

                ->select('count(*)')

                ->from('ziad')

                ->where('userid = ziuser.userid')

                ->text;

        $users = Yii::app()->db->createCommand()

                ->selectDistinct('u.userid,u.username,u.status as registration_status,u.email,u.activationcode,

                                  ('.$subquery.') as number_of_ads')

                ->from('ziuser as u')

                ->leftJoin('ziad as a','u.userid = a.userid')

                ->where('u.status = :stat',array(':stat'=>0))

                ->order('number_of_ads desc')

                ->queryAll();

it doesn’t work at all, what am I doing wrong here ?

What is the improvement to not use directly sql?

I want to convert it because, I’m having a hard time with implementing the offset and limit due to the database being used is oracle 11g,so it would be a lot easier if I can convert it to the yii way and then just use the built-in offset and limit properties of the ceateCommand(). But anyway,I was able to figure out to solve it…there’s a tricky quoting thing that took me several hours to make it work.

        $subquery = Yii::app()->db->createCommand()

                ->select('count(*)')

                ->from('(ziad a)')

                ->where('userid = u.userid')

                ->text;

        $command = Yii::app()->db->createCommand();

                $command->selectDistinct('u.userid,u.username,u.status as registration_status,u.email,u.activationcode,('.$subquery.') as number_of_ads');

                $command->from('(ziuser u)');

                $command->leftJoin('(ziad a)','u.userid = a.userid');

                $command->where('u.status = :stat',array(':stat'=>0));

                $command->order('(number_of_ads) desc');

             if(!empty($limit)){   

                $command->limit($limit);

             }

             if(!empty($offset)){

                $command->offset($offset);

             }

                $users = $command->queryAll();