Relational AR: Active record is trying to select an invalid column

Hi,

I have problems with relational queries and MANY_MANY Relations. Maybe you can help me with that.

My Report Model relations:




class Report extends ...


public function relations()

	{		

	return array(	

           'customers'=>array(self::MANY_MANY,'Customer','shr_report_customer(report_id,customer_id)'),

           'reportcustomer_report' => array(self::HAS_MANY,'ReportCustomer','report_id'),

	);

	}



The 2nd relation should not be important, it’s the connection to the associative table.

For testing purposing I want to fill a table, so I use an ActiveDataProvider which will be given to the view.




$criteria=new CDbCriteria;

$criteria->select ="t.id,t.reportbegin,t.reportend,customers.name";			

$criteria->with = array('customers');

$criteria->together = true;	

$dataProvider=new CActiveDataProvider('Report',array(

		'criteria'=>$criteria,		

		)

	);






Error:




Active record "Report" is trying to select an invalid column "customers.name". 

Note, the column must exist in the table or be an expression with alias.

I just don’t get it why that doesnt work.

Did you try explicitly setting the alias for that relation as ‘customers’?

i.e.,




 return array(   

           'customers'=>array(self::MANY_MANY,'Customer','shr_report_customer(report_id,customer_id)', 'alias'=>'customers'),

           'reportcustomer_report' => array(self::HAS_MANY,'ReportCustomer','report_id'),

        );



Yes I tried that, forgot to mention it. I tried even to avoid the MANY_MANY relation and take the associative table into account. Do I have to do use the join option?

Yes, that’s what I would probably do next, explicitly join the table and remember to join it AS customers.

I believe the problem is here:

$criteria=new CDbCriteria;

$criteria->select ="t.id,t.reportbegin,t.reportend"; <— updated

$criteria->with = array(‘customers’=>array(‘select’=>‘name’)); <— I think should be like this

$criteria->together = true;

$dataProvider=new CActiveDataProvider(‘Report’,array(

'criteria'=&gt;&#036;criteria,          


 )

);

// You are saying to select from your Report table the customers.name column

Didn’t know you could do that, very slick – is it taking a full CDbCriteria as the array for ‘customers’ or do you have to pass it an array?

Hi Dana,

Check

http://www.yiiframework.com/doc/api/1.1/CActiveRecord#with-detail

it is actually a full CDbCriteria (config is array anyway : ) ) :

http://code.google.com/p/yii/source/browse/tags/1.1.5/framework/db/ar/CActiveRecord.php#1551

Hm that query does look strange if I follow your suggestion. I get no error, but I was not able to retrieve values for the name column in my view

customers_customers can’t be right or?




Querying SQL: SELECT "t"."id" AS "t0_c0", "t"."reportbegin" AS "t0_c2",

 "t"."reportend" AS "t0_c3", "customers"."name" AS "t1_c3", "customers"."id" AS

 "t1_c0" FROM "shr_report" "t" LEFT OUTER JOIN "shr_report_customer" 

"customers_customers" ON ("t"."id"="customers_customers"."report_id") LEFT OUTER 

JOIN "shr_customer" "customers" ON ("customers"."id"="customers_customers"."customer_id") LIMIT 10 



customers_customers is an alias…

And I don’t see anything wrong with the query… Have you test it against the DB as it says?

Sorry, should have done that. It’s working. I have to check the transfer to the GridView, must have done something wrong there.

Thank you for your help.

Congrats… happy to help

one question:

if I pass that specific CActiveDataProvider, should it not be possible to use that CActiveDataProvider within the CGridView?

The view, $dp is the passed ActiveDataProvider




$this->widget('zii.widgets.grid.CGridView', array(

    'dataProvider'=>$dp,

	'columns'=>array(

        'reportbegin',       

        'reportend',  

	'customers.name', 

        array(            

            'class'=>'CButtonColumn',

        ),

    ),

));




Of corse it is possible, and it works fine.

The only problem you can have is that the validation doesn’t work, because you don’t call $model->search();

If you want to make it work, you can leave in the CGridView:


'dataProvider'=>$model->search(),

and change the function search in the related model in order to add your extra criteria.

Validation? I thought this has to be called only when inserting rows?

I still can’t see the values of the Customer.name column.

I added the criteria to the Report->search method. But still no values in the relation column. It’s the same if I want to display the related id column. I am kind of confused. Where else could I have done a mistake?




public function search()

{

	$criteria=new CDbCriteria;		

	$criteria->select ="t.reportbegin,t.reportend";

	$criteria->with = array('customers'=>array('select'=>'id'));

	$criteria->together = true;			

	$criteria->compare('id',$this->id);

	$criteria->compare('counterreports_id',$this->counterreports_id);

	$criteria->compare('reportbegin',$this->reportbegin,true);

	$criteria->compare('reportend',$this->reportend,true);

	$criteria->compare('importdate',$this->importdate,true);

	$criteria->compare('xmlblob',$this->xmlblob,true);

	$criteria->compare('status',$this->status);

	$criteria->compare('uploaduser',$this->uploaduser,true);		

		

	return new CActiveDataProvider(get_class($this), array(

			'criteria'=>$criteria,

	));

}






$this->widget('zii.widgets.grid.CGridView', array(

    'dataProvider'=>Report::model()->search(),

	'columns'=>array(

        'reportbegin',          

        'reportend',  

        'customers.name',        

    ),

));



The direct SQL query brings back the correct result when I query the Postgresql Database

I am really sorry, I wrote validation where I should write filtering.

About the new question, I think that your problem can be solved by changing the select in the with:




public function search()

{

        $criteria=new CDbCriteria;              

        $criteria->select ="t.reportbegin,t.reportend";




        $criteria->with = array('customers'=>array('select'=>'id, name'));

         // or simply $criteria->with = array('customers');


        $criteria->together = true;                     

        $criteria->compare('id',$this->id);

        $criteria->compare('counterreports_id',$this->counterreports_id);

        $criteria->compare('reportbegin',$this->reportbegin,true);

        $criteria->compare('reportend',$this->reportend,true);

        $criteria->compare('importdate',$this->importdate,true);

        $criteria->compare('xmlblob',$this->xmlblob,true);

        $criteria->compare('status',$this->status);

        $criteria->compare('uploaduser',$this->uploaduser,true);                

                

        return new CActiveDataProvider(get_class($this), array(

                        'criteria'=>$criteria,

        ));

}



it does not work :(

For testing I tried to use an array in the column configuration and I get a

"Trying to get property of non-object" message in CComponent.php(616) : eval()

I guess that’s related and should work if everything is all right.




$this->widget('zii.widgets.grid.CGridView', array(

    'dataProvider'=>Report::model()->search(),

	 'columns'=>array(

        'reportbegin',          

        'reportend',	

	 array(

    	'name'=>'name',

    	'value'=>'$data->customers->name',

         )

..



no ideas?

If I dump $dataprovider->getData(), the values are somewhere in it as private properties. The question is how I can access them :)

Since the cardinality of the relationship is …-TO-MANY you should normally get an array of related objects. But that may be different when querying via a CDataProvider / accessing via CGridView.

At least you could try this


'value'=>'$data->customers[someindex]->name',

Question is which index to use. It’s possible $row might give you the correct data.

(not tested)

/Tommy

Thank you, looks like ‘value’=>’$data->customers[0]->name’

gives me the correct value for the name column. Still kind of confusing… :)

My query log shows that Yii gets all fields from related tables, doesnt’t matter if set for example $criteria->with = array(‘customers’=>array(‘select’=>‘name’)) or not. This might by a performance problem when dealing with many column tables - do you have a same problem?

Greetenigs,

Andrzej