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 user.id = 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 

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

}

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



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




SELECT 

	* 

FROM 

	user u 

JOIN ( 

	SELECT * 

	FROM skill_assessment s 

	WHERE s.skill = 'HTML' 

	AND s.score >= 80 

) b ON 

	(u.id = b.userId) 

JOIN ( 

	SELECT * 

	FROM skill_assessment s 

	WHERE s.skill = 'CSS3' 

	AND s.score >= 80

) c ON 

	(u.id = c.userId);

etc...



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:




//Fails

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;

        

        //Calculated

	public $rScore;

	public $qScore;


	//ActiveRecord

	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('skill_assessments'),

                

                //SCENARIOS

                

                //register

                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'),

                

                //edit

                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:




//Works

$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’

OR

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.

Thanks

ttt

(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.