Selecting specific columns from related models

Hi,

I have the following models and their relevant relations and fields.

Model: MPaymentOrderItem


public function relations() {

  return array(

    'order' => array(self::BELONGS_TO, 'MPaymentOrder', 'orderId'),

  );

}

public function rules() {

  return array(

    array('id, orderId', 'safe', 'on' => 'search'),

  );

}

Model: MPaymentOrder


public function relations() {

  return array(

    'user' => array(self::BELONGS_TO, 'MUser', 'userId'),

    'items' => array(self::HAS_MANY, 'MPaymentOrderItem', 'orderId'),

  );

}

public function rules() {

  return array(

    array('id, userId', 'safe', 'on' => 'search'),

  );

}

Model: MUser


public function rules() {

  return array(

    array('zipcode', 'safe'),

    array('zipcode', 'length', 'min' => 3, 'max' => 4),

    array('zipcode', 'numerical', 'integerOnly' => 'true'),

  );

}

Knowing that, consider the following:


$crit = new CDbCriteria();

$crit->select = '*';

$crit->with = 'order.user';

$crit->group = 'user.zipcode';

$items = MPaymentOrderItem::model()->findAll($crit);

The above criteria works fine. However - since I only need the zipcodes from the users, I will do this instead:


$crit = new CDbCriteria();

$crit->select = 'user.zipcode';

$crit->with = 'order.user';

$crit->group = 'user.zipcode';

$zipItems = MPaymentOrderItem::model()->findAll($crit);

This one does not work. The error message:

"Active record "MPaymentOrderItem" is trying to select an invalid column "user.zipcode". Note, the column must exist in the table or be an expression with alias."

This part I don’t understand - I can group by the column, but I can’t select that column only? I find that strange.

Then I tried looking into the SQL:

When looking into the working criteria’s generated SQL, it looks like this:

(I’ve cut out the irrelevant fields)


SELECT `t`.`id` AS `t0_c0`, `t`.`orderId` AS `t0_c1`, `order`.`id` AS `t1_c0`, `order`.`userId` AS `t1_c1`,  `user`.`id` AS `t2_c0`, `user`.`zipcode` AS `t2_c7`

FROM `cpnc_PaymentOrderItem` `t` 

LEFT OUTER JOIN `cpnc_PaymentOrder` `order` ON (`t`.`orderId`=`order`.`id`) 

LEFT OUTER JOIN `cpnc_User` `user` ON (`order`.`userId`=`user`.`id`)  

GROUP BY user.zipcode;

So if I change this SQL manually to only selecting the user.zipcode, it will look like this:


SELECT `user`.`zipcode`

FROM `cpnc_PaymentOrderItem` `t` 

LEFT OUTER JOIN `cpnc_PaymentOrder` `order` ON (`t`.`orderId`=`order`.`id`) 

LEFT OUTER JOIN `cpnc_User` `user` ON (`order`.`userId`=`user`.`id`)  

GROUP BY user.zipcode;

Running both of these queries in phpmyadmin will give me a fine result - no errors.

so ehm… anyone got an idea?

or is this a bug?

I am facing the similar problem.Any idea YII experts?

Thanks to all

Smith




$crit->select = 't.id';

$crit->with = array('order'=>array('select'=>'order.id'), 'order.user'=>array('select'=>'user.zipcode'));



will do the trick.

I’m digging internals about this behavior to clarify to myself the cause, so comments are welcome.

The first part or code works perfectly (I tested) ($crit->with = array(‘order’=>array(‘select’=>‘order.id’), …)

The second one (‘order.user’=>array(‘select’=>‘user.zipcode’)) I didn’t check but seems corrected…