Model relations to a mysql view w/o a primary key

Yii 1.1 General Discussion
1

Hi All,

I’ve been working at this for a couple days and can’t seem to find a way to get it to work.

I’ve found a other threads about a similar issue but haven’t seen a clear answer.

http://www.yiiframework.com/forum/index.php?/topic/11892-display-data-from-other-model-by-some-key/

http://www.yiiframework.com/forum/index.php?/topic/25542-relations-with-non-primary-keys/

http://www.yiiframework.com/forum/index.php?/topic/17360-relations-in-model-with-primary-key-that-is-also-foreign-key/

http://www.yiiframework.com/forum/index.php?/topic/25229-help-in-relations/

http://www.yiiframework.com/forum/index.php?/topic/7125-multi-column-foreign-keys-and-ar-relations/

I have 3 mysql tables sales_rep, customer, and rep_view_customer. rep_view_customer is a mysql view and does not have a primary set(mysql does not have the functionality) but each row does have a unique id.

–sales_rep–

id

first_name

last_name

email

phone

address

city

state

zip

date_added

–commissions–

id

date_added

order_id

salesrep_id

customer_id

commission_total

status

–rep_view_customer–

entity_id

email

first_name

last_name

company

The relations of the models is: commissions is related to sales_rep(commissions.salesrep_id->sales_rep.id)

and rep_view_customer(commissions.customer_id->rep_view_customer.entity_id)

I’m trying to get some of the values to from rep_view_customer in CGridView.

Here’s my CGridView portion




<b style="font-size: 1.3em">Commissions</b>

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

	'id'=>'sales-rep-grid',

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

	'filter'=>$commissions,

	'columns'=>array(

		'id',

		/**/

		//'rep_view.increment_id',

		'rep_view_customer.first_name',

		'customer_id',

		'date_added',

		'commission_total',

		'status',

		/**/

		array(

			'class'=>'CButtonColumn',

		),

	),

)); ?>

Here’s the actionView from my controller file




	public function actionView($id)

	{

		$commissions = new Commissions;

		$commissions->salesrep_id = $id;

		$this->render('view',array(

			'model'=>$this->loadModel($id),

			'commissions'=>$commissions,

		));

	}

And here’s the relations method from my Commissions model




	public function relations()

	{

		// NOTE: you may need to adjust the relation name and the related

		// class name for the relations automatically generated below.

		return array(

			'customer' => array(self::BELONGS_TO, 'Customer', 'customer_id'),

			'salesrep' => array(self::BELONGS_TO, 'SalesRep', 'salesrep_id'),

			'rep_view_customer' => array(self::HAS_ONE, 'RepViewCustomer', '', 'on'=>'rep_view_customer.entity_id = t.customer_id'),

		);

	}

From what i read i though the ‘on’ option was the correct option to use. but when i try to view the view i get the error.

[i]CDbException

CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘t.customer_id’ in ‘where clause’. The SQL statement executed was: SELECT rep_view_customer.entity_id AS t1_c0, rep_view_customer.email AS t1_c1, rep_view_customer.first_name AS t1_c2, rep_view_customer.last_name AS t1_c3, rep_view_customer.company AS t1_c4 FROM rep_view_customer rep_view_customer WHERE (rep_view_customer.entity_id = t.customer_id)[/i]

Any Thoughts on how i might get this figure out?

Thanks

Oliver

2

Yii by default expects a primary key field of ‘id’. Since there isn’t one you get the error.

From the guide:

In your model for the view add this:




public function primaryKey()

{

    return 'entity_id';

}
3

@dniznick i tried your suggestion, i added the primary key to my RepViewCustomer model and still getting the exact same error.

Any idea why this might be happening?

I don’t think it’s an issue exactly with not having a primary key. I was able to get this to relation to bring up (incorrect)data using the Commissions model primary key(using ‘rep_view_customer’ => array(self::HAS_ONE, ‘RepViewCustomer’, ‘entity_id’)). But RepViewCustomer is related via the Commissions customer_id column not the primary key.

I must be missing something simple here as this should be a piece of cake to put together.

Let me know

Thanks

Oliver

4

Still Trying to work this out…There has to be a way to build relations to other tables on non-primary keys.

Oliver

5

I think this is a bug in Yii itself. I started logging the actual queries Yii.

Here’s what i saw

These are the queries for two normal models that are related with a view showing data from both.




SHOW COLUMNS FROM `customer`

SHOW COLUMNS FROM `customer`

SHOW CREATE TABLE `customer`

SHOW CREATE TABLE `customer`

SHOW COLUMNS FROM `sales_rep`

SHOW COLUMNS FROM `sales_rep`

SHOW CREATE TABLE `sales_rep`

SHOW CREATE TABLE `sales_rep`

SELECT `salesrep`.`id` AS `t1_c0`, `salesrep`.`first_name` AS `t1_c1`, `salesrep`.`last_name` AS `t1_c2`, `salesrep`.`email` AS `t1_c3`, `salesrep`.`phone` AS `t1_c4`, `salesrep`.`address` AS `t1_c5`, `salesrep`.`city` AS `t1_c6`, `salesrep`.`state` AS `t1_c7`, `salesrep`.`zip` AS `t1_c8`, `salesrep`.`date_added` AS `t1_c9` FROM `sales_rep` `salesrep`  WHERE (`salesrep`.`id`=:ypl0)

SELECT `salesrep`.`id` AS `t1_c0`, `salesrep`.`first_name` AS `t1_c1`, `salesrep`.`last_name` AS `t1_c2`, `salesrep`.`email` AS `t1_c3`, `salesrep`.`phone` AS `t1_c4`, `salesrep`.`address` AS `t1_c5`, `salesrep`.`city` AS `t1_c6`, `salesrep`.`state` AS `t1_c7`, `salesrep`.`zip` AS `t1_c8`, `salesrep`.`date_added` AS `t1_c9` FROM `sales_rep` `salesrep`  WHERE (`salesrep`.`id`=:ypl0)

SELECT COUNT(*) FROM `customer` `t` WHERE salesrep_id LIKE :ycp0

SELECT COUNT(*) FROM `customer` `t` WHERE salesrep_id LIKE :ycp0

SELECT * FROM `customer` `t` WHERE salesrep_id LIKE :ycp0 LIMIT 10

SELECT * FROM `customer` `t` WHERE salesrep_id LIKE :ycp0 LIMIT 10

Here’s the queries that are used when i use the ‘on’ option to relate to my MySQL View.




SHOW COLUMNS FROM `customer`

SHOW COLUMNS FROM `customer`

SHOW CREATE TABLE `customer`

SHOW CREATE TABLE `customer`

SELECT * FROM `customer` `t` WHERE `t`.`id`='8' LIMIT 1

SELECT * FROM `customer` `t` WHERE `t`.`id`='8' LIMIT 1

SHOW COLUMNS FROM `rep_view_customer`

SHOW COLUMNS FROM `rep_view_customer`

SHOW CREATE TABLE `rep_view_customer`

SHOW CREATE TABLE `rep_view_customer`

SELECT `rep_view_customer`.`entity_id` AS `t1_c0`, `rep_view_customer`.`email` AS `t1_c1`, `rep_view_customer`.`first_name` AS `t1_c2`, `rep_view_customer`.`last_name` AS `t1_c3`, `rep_view_customer`.`company` AS `t1_c4` FROM `rep_view_customer` `rep_view_customer`  WHERE (rep_view_customer.entity_id = t.customer_id)

SELECT `rep_view_customer`.`entity_id` AS `t1_c0`, `rep_view_customer`.`email` AS `t1_c1`, `rep_view_customer`.`first_name` AS `t1_c2`, `rep_view_customer`.`last_name` AS `t1_c3`, `rep_view_customer`.`company` AS `t1_c4` FROM `rep_view_customer` `rep_view_customer`  WHERE (rep_view_customer.entity_id = t.customer_id)

SELECT `rep_view_customer`.`entity_id` AS `t1_c0`, `rep_view_customer`.`email` AS `t1_c1`, `rep_view_customer`.`first_name` AS `t1_c2`, `rep_view_customer`.`last_name` AS `t1_c3`, `rep_view_customer`.`company` AS `t1_c4` FROM `rep_view_customer` `rep_view_customer`  WHERE (rep_view_customer.entity_id = t.customer_id)

SELECT `rep_view_customer`.`entity_id` AS `t1_c0`, `rep_view_customer`.`email` AS `t1_c1`, `rep_view_customer`.`first_name` AS `t1_c2`, `rep_view_customer`.`last_name` AS `t1_c3`, `rep_view_customer`.`company` AS `t1_c4` FROM `rep_view_customer` `rep_view_customer`  WHERE (rep_view_customer.entity_id = t.customer_id)
6

Doing some more research on this. I am running into problems with using Yii on views which do not have a pk.

http://code.google.com/p/yii/issues/detail?id=2873

http://tipstank.com/2010/06/18/yii-uses-database-primary-key-when-findbypk-using-composite-key/

The answer of "Do not use Active Record for these types of complex queries" is not very satisfying to me!

Edit: Another issue logged on this:

http://code.google.com/p/yii/issues/detail?id=3023

Edit: Fixed in 1.1.9? Yes!!

http://code.google.com/p/yii/issues/detail?id=2706

7

I agree.

That is great! I hope this makes things more flexible.

I love using Yii but this bug is making it difficult to build anything of any complexity with other databases/tables.

Let me know if you find any other fixes.

Thanks

Oliver

8

I’ve continued to look for a solution for this rather big issue.

I’ve resorted to using CArrayDataProvider to pass my raw SQL query to CGridView.

I’ve found that this has been a lot easier/quicker(about 1.5 hours compared to about 20 with the model relations) to figure out with sorting/filtering then trying to rig ActiveRecord to sort/filter with relations(with and without primary/foreign keys). Hopefully this will be fixed/better documented soon as i would like to stick to the default relations/models.

Oliver