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.