Some of my model queries connect multiple tables together, and yet only return a single value.
EG:
public function getIDByName($user_id, $name, $major, $minor, $patch)
{
$criteria = new CDbCriteria;
$criteria->order = 'major DESC, minor DESC, patch DESC';
$criteria->addCondition('t.user_id=:user_id');
$criteria->addCondition('t.name=:name');
$criteria->addCondition('version.family_id=t.offer_type_id');
$criteria->addCondition('version.type=:version_type');
$criteria->addCondition('(version.major=:major OR :major IS NULL)');
$criteria->addCondition('(version.minor=:minor OR :minor IS NULL)');
$criteria->addCondition('(version.patch=:patch OR :patch IS NULL)');
$criteria->params = array(
':user_id' => $user_id,
':name' => $name,
':version_type' => LookupHelper::getID("version.type", "offer_type"),
':major' => $major,
':minor' => $minor,
':patch' => $patch,
);
$model = OfferType::model()->with('extra', 'extra.version', 'user')->find($criteria);
return model->extra->offer_type_extra_id;
}
This is very inefficient as far more data is being returned from the database than is needed.
If I try to restrict it using:
$criteria = new CDbCriteria;
$criteria->select = "extra.offer_type_extra_id";
...
Then I get an error
Active record "OfferType" is trying to select an invalid column "extra.offer_type_extra_id". Note, the column must exist in the table or be an expression with alias.
My first question is why this does not work? I do have a work around, but I would like to know why, is it a Yii issue, or is there something wrong with my methodology.
Secondly, my work around is verbose. Like so:
public function getIDByName($user_id, $name, $major, $minor, $patch)
{
$criteria = new CDbCriteria;
$criteria->select = 'offer_type_id';
$criteria->with = array(
'extra' => array(
'select' => 'offer_type_extra_id',
),
'user' => array(
'select' => 'user_id',
),
'extra.version' => array(
'select' => 'version_id',
),
);
$criteria->order = 'major DESC, minor DESC, patch DESC';
$criteria->addCondition('t.user_id=:user_id');
$criteria->addCondition('t.name=:name');
$criteria->addCondition('version.family_id=t.offer_type_id');
$criteria->addCondition('version.type=:version_type');
$criteria->addCondition('(version.major=:major OR :major IS NULL)');
$criteria->addCondition('(version.minor=:minor OR :minor IS NULL)');
$criteria->addCondition('(version.patch=:patch OR :patch IS NULL)');
$criteria->params = array(
':user_id' => $user_id,
':name' => $name,
':version_type' => LookupHelper::getID("version.type", "offer_type"),
':major' => $major,
':minor' => $minor,
':patch' => $patch,
);
$model = OfferType::model()->find($criteria);
if(isset($model))
return $model->extra->offer_type_extra_id;
return false;
}
This is annoyingly verbose and I have to select at least one column from each table(an empty string errors, setting to null returns all columns). Is there a simpler way?
Thanks for taking the time to read this.