CDbCriteria query help request

I’ve built a query as a string, which I am then using to pull a list of users via the ‘findAllBySql’ method, like this:

//I need to be able to do an arbitrary number of joins (up to 26 right now), so I create an array of letters

$str = "abcdefghijklmnopqrstuvwxyz";

$idxs = str_split($str);

//begin the query

//Get all users...

$sql = "SELECT * FROM user u";

//... as well as all rows from the skill_assessment table where the skill and score match the user-provided values, and = skill_assessment.userId

for($i = 0; $i < count($skill_filters); $i++)


     $sql .= "JOIN (

          SELECT * 

          FROM skill_assessment s 

          WHERE s.skill = '" . $skill_filters[$i]->skill . "' 

          AND s.score >= " . $skill_filters[$i]->level . "

          ) $idxs[$i] ON 

          ( = " . $idxs[$i] . ".userId)";


$users = UserModel::model()->findAllBySql($sql);

A regular sql query that would accomplish what I want to do would be:




	user u 



	FROM skill_assessment s 

	WHERE s.skill = 'HTML' 

	AND s.score >= 80 

) b ON 

	( = b.userId) 



	FROM skill_assessment s 

	WHERE s.skill = 'CSS3' 

	AND s.score >= 80

) c ON 

	( = c.userId);


It would be great if the skill_assessments property of the UserModel just came back populated with the associated skill_assessments, but they don’t have to.

The problem is that the UserModels that this returns seem unable to take advantage of the getRelated() method, like this:


foreach($users as $user)


     $foo = $user->getRelated('skill_assessments');


Here is my UserModel:

class UserModel extends CActiveRecord


	public $id;

	public $linkedInId;

	public $username;

	public $password;

        public $password_repeat;

	public $firstName;

	public $middleName;

	public $lastName;

	public $email;

	public $phone;

	public $type;

        public $createdOn;

        //Private User

        public $positions;

        public $skill_assessments;

        //Org User

	public $orgId;

        public $orgName;



	public $rScore;

	public $qScore;


	public static function model($className=__CLASS__)


		return parent::model($className);



    	public function tableName()


		return 'user';



        public function rules()


            return array(


                array('username', 'match', 'not' => true,

                    'pattern' => '/[^a-zA-Z0-9 ]/',

                    'message' => '<span class="error-msg">A valid username is required</span>'),

                array('phone', 'safe'),

                array('middleName', 'safe'),

                array('type', 'safe'),

                array('orgId', 'safe'),

                array('positions', 'safe'),






                array('username', 'required', 'on' => 'register'),

                array('password', 'required', 'on' => 'register'),

                array('password_repeat', 'required', 'on' => 'register'),

                array('firstName', 'required', 'on' => 'register'),

                array('lastName', 'required', 'on' => 'register'),

                array('email', 'required', 'on' => 'register'),



                array('id', 'required', 'on' => 'edit'),

                array('firstName', 'safe', 'on' => 'edit'),

                array('lastName', 'safe', 'on' => 'edit'),

                array('email', 'safe', 'on' => 'edit')




        public function attributeNames() {

            return array('username', 'password', 'firstName', 'middleName', 'lastName', 'email', 'phone', 'type', 'positions', 'skill_assessments');


        public function relations()


		return array(

			'positions' => array(self::HAS_MANY, 'PositionModel', 'userId'),

			'positions_orgs' =>array(self::HAS_MANY, 'PositionModel', 'userId', 'with'=>'org'),

			'recommendations' => array(self::HAS_MANY, 'RecommendationModel', 'userId'),

			'skill_assessments' => array(self::HAS_MANY, 'SkillAssessmentModel', 'userId'),

			'pay_rates' => array(self::HAS_MANY, 'PayRateModel', 'userId')




The strange thing is that I CAN use the getRelated() method successfully if instead of using ‘findAllBySql()’, I use ‘findByPk()’ when getting my UserModels, as I have in this other controller:


$user = UserModel::model()->findByPk($id);

$positions = $user->getRelated('positions');

So, what I need is EITHER:

A way to get those related records while using ‘findAllBySql’


Some help figuring out how to build my hand-written query using CDbCriteria.

Any help would be greatly appreciated. I can post more code as necessary.



(hoping for a reply)

Hi, not sure if I can help but have you looked at your application log? (It’s located in ‘protected\runtime\application.log’)

I have also had trouble with the various database operations. I have found that findAllBySql has not worked for me. Due to time pressure I sought different solutions like (cActiveRecord) modelname::model()->findAll() after which I process my array of activerecords.

Another solution that worked for me was

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


$results = $command->execute();

And I process my array of AR’s again.

By the way where you use

$positions = $user->getRelated('positions');

I just use

$positions = $user->positions;

But that’s probably all the same.

I suggest you look at the log, debug a lot and try a few different approaches. I was never able to determine why a specific solution that should work, did not work. Sorry I can’t be of more help.