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