Select criteria in related tables

Yii 1.1 General Discussion
1

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.

2

  1. Doesnt work before you use a model to do the execution of your criteria and offer_type_extra_id is an attribute of your Extra model not of your OfferType model.

  2. I wonder if you have setup your relations as by checking at your code, OfferType is related to UserId right? Then, why dont you use relations instead? If your problem is that the OfferType should be returned to a certain condition then I suggest you review the Definitive Guide on Dynamic Relational Query Options: http://www.yiiframework.com/doc/guide/1.1/en/database.arr#dynamic-relational-query-options , which specifically shows how to override existing relation options.

Cheers man

3

Hi Antonio

Thanks for responding.

  1. Doh. Of course. the with is before the find. Thanks

  2. Why do I have to name a select column in every table to prevent everything from coming back?

The relationships are fine. I could define the default select in the relationship in the model, however I have many queries like this, but each selecting different columns. What I want is to prevent any columns coming back (preferably in the criteria code, rather than the model default) as the relation is only required for criteria selection and not for selecting its data.