Yii Framework Forum: Selecting specific columns from related models - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Selecting specific columns from related models Rate Topic: -----

#1 User is offline   Klaus 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 7
  • Joined: 26-January 11

Posted 29 March 2012 - 06:48 AM

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.
0

#2 User is offline   Klaus 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 7
  • Joined: 26-January 11

Posted 30 March 2012 - 06:35 AM

so ehm... anyone got an idea?
or is this a bug?
0

#3 User is offline   smith19 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 21
  • Joined: 26-November 12

Posted 23 December 2012 - 02:28 AM

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

Thanks to all
Smith
0

#4 User is offline   yugene 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 513
  • Joined: 08-August 09

Posted 24 December 2012 - 06:25 AM

$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.
0

#5 User is offline   KonApaz 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,327
  • Joined: 21-February 11
  • Location:Greece

Posted 21 November 2013 - 05:58 AM

View Postyugene, on 24 December 2012 - 06:25 AM, said:

$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...
Yii is the best php framework in the world!
find our demo Yii extension on www.webkit.gr
Is it post useful? please v++ ;)
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users