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: 515
  • 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   Kostas Apazidis (KonApaz) 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,335
  • 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!
It is also powerful and flexible for large scale websites
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