CDBCriteria using with and $condition->select not working properly

Yii 1.1 General Discussion
1

Hi,

I have a problem with CDBCriteria using select condition.

I have two tables users and users_profile. I need to select user id from users and lastname from users_profile





$criteria = new CDbCriteria();

        	$criteria->select = 't.id, profile.lastname';


        	$users = User::model()->with('profile')->findAll($criteria);

This code outputs error : Active record "User" is trying to select an invalid column "profile.lastname". Note, the column must exist in the table or be an expression with alias.

Column exists and if I leave $criteria->select = ’ * ’ everythink goes ok and the query is:





SELECT `t`.`id` AS `t0_c0`, `t`.`username` AS `t0_c4`, `t`.`email` AS  `t0_c6`, `t`.`date_insert` AS `t0_c9`, `t`.`date_lastvisit` AS `t0_c12`,  `t`.`superuser` AS `t0_c16`, `t`.`active` AS `t0_c3`,  `profile`.`user_id` AS `t1_c0`, `profile`.`lastname` AS `t1_c1`,  `profile`.`firstname` AS `t1_c2`, `profile`.`birthday` AS `t1_c3` FROM  `user_users` `t`  LEFT OUTER JOIN `user_profiles` `profile` ON  (`profile`.`user_id`=`t`.`id`)

but I need just two fields, so way I must select them all.

The same problem is not with this one table, but whit all, if I specify $criteria->select and use with function than I have always an error.

Have anyone solved this problem?

thanks

2

What about your models’ relations() methods?

3

with relations everything is ok





$relations = array(

			'profile' => array(self::HAS_ONE, 'Profile', 'user_id'),

		);

I have find out that if you specify

$criteria->select

then your sql is still returning all values but in the results you just see specified columns results, all ohters columns are emtpy.

So if you specify $criteria->select than you get about 90 % of all object, so it’s the same as not specify select values.

One more thing, you can specify CONCAT condition in your relational table, even if you describe a new variable in model there is still error in CDbCriteria




$criteria = new CDbCriteria();

            $criteria->select = '*';

            $criteria->condition = " active = '1' ";

            $criteria->with = array('profile' => array('select' => 'user_id, CONCAT(firstname, lastname) AS myvalue'));

and in Profile model add





public $myvalue;

still have error, so there is any way to group similar columns in relational table or it’s an isue.

4

try





$criteria = new CDbCriteria();

                $criteria->select = 't.id, t1.lastname';


                $users = User::model()->with('profile')->findAll($criteria);

t1 is the default alias that yii gives to the first joined table, this one should work.

Another option is to set the alias as condition in the width.

5

even if I set alias in condition correct it throws an error:

Active record "Offers" is trying to select an invalid column "userprofile.firstname". Note, the column must exist in the table or be an expression with alias.

And the relations is





return array(

            'user_profile' => array(self::BELONGS_TO, 'Profile', 'user_id', 'alias' => 'userprofile'),

		);

It’s strange, when I try to set select condition the query throws this error and when I cut the select condition from criteria everything is ok. I have try with t1 alias but face the same error.

Have other face this problem when using with and trying to select some of needet columns.

My Yii version is 1.1.4

Thanks

6

anyword on this? I face the same problem but with Yii 1.1.6

7

try to use t letter as table alias.

I have solved this problem, but cant remember how. It was a long time ago.

8 



'criteria' => array(

          'select'	=> 't.*, t.field2', 

          'with'	=> array('relationXYZ' => array('alias'  => 'r', 

                                                        'select' => 'r.field1', 

                                                        'on'     => 't.id = r.id', 

                                                       'joinType'=> 'INNER JOIN')),

          'condition'   => "t.name = 'hello' AND t.set = ".$world,

        //'together'	=> true,

       ),

instead of set ‘select’ some columns, you could set ‘select’ to false:


'select' => false,

. The result is, select nothing from the joined table. Now you could control from which table you get the different values.

9

This is what I did in my code

               <?php
                $criteria = new CDbCriteria;
                $criteria->select = 'cvheader_id, account_code, amount, details, GROUP_CONCAT(gl_description SEPARATOR "<br>") AS id, GROUP_CONCAT(gl_code SEPARATOR "<br>") AS account_code, GROUP_CONCAT(gl_description SEPARATOR "<br>") AS accountcode, GROUP_CONCAT(details SEPARATOR "<br>") as details, GROUP_CONCAT(amount SEPARATOR "<br>") as amount';
                $criteria->with = array('acc' => array('select' => 'gl_code, gl_description'));
                $criteria->addBetweenCondition("transaction_date", $fromDate, $toDate, 'AND');
                $criteria->group = 'cvheader_id';
                $result = CheckVoucherDetails::model()->findAll($criteria);
                ?>