Relation issue - not adding a join for 3-rd table

Yii 1.1 General Discussion
1

Hello!

I’ve tried to get it on my own, tried IRC channel, tried the russian comunity…, so here is the deal:

The sources:

The Transactions View - http://pastebin.com/fBWBk0ZK

The Transactions Controller - http://pastebin.com/9d9UGhCc

The Transactions Model - http://pastebin.com/ePpRQC0Y

The Accounts model - http://pastebin.com/uSbwVBxU

The TransactionsVouchers model - http://pastebin.com/iD9BU2pf

When I load the grid - everything works fine. But when I try to filter by trnvouchers.tvc_code field, I get the error:


CDbException

Description


CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'trnvouchers.tvc_code' in 'where clause'

Source File


C:\development\yii-1.1.2\framework\db\CDbCommand.php(375)


00363:             }

00364: 

00365:             if($this->_connection->enableProfiling)

00366:                 Yii::endProfile('system.db.CDbCommand.query('.$this->getText().')','system.db.CDbCommand.query');

00367: 

00368:             return $result;

00369:         }

00370:         catch(Exception $e)

00371:         {

00372:             if($this->_connection->enableProfiling)

00373:                 Yii::endProfile('system.db.CDbCommand.query('.$this->getText().')','system.db.CDbCommand.query');

00374:             Yii::log('Error in querying SQL: '.$this->getText().$par,CLogger::LEVEL_ERROR,'system.db.CDbCommand');

00375: throw new CDbException(Yii::t('yii','CDbCommand failed to execute the SQL statement: {error}',

00376:                 array('{error}'=>$e->getMessage())));

00377:         }

00378:     }

00379: }


Stack Trace


#0 C:\development\yii-1.1.2\framework\db\CDbCommand.php(265): CDbCommand->queryInternal('fetchAll', 2, Array)

#1 C:\development\yii-1.1.2\framework\db\ar\CActiveFinder.php(739): CDbCommand->queryAll()

#2 C:\development\yii-1.1.2\framework\db\ar\CActiveFinder.php(413): CJoinElement->runQuery(Object(CJoinQuery))

#3 C:\development\yii-1.1.2\framework\db\ar\CActiveFinder.php(85): CJoinElement->find(Object(CDbCriteria))

#4 C:\development\yii-1.1.2\framework\db\ar\CActiveRecord.php(1178): CActiveFinder->query(Object(CDbCriteria), true)

#5 C:\development\yii-1.1.2\framework\db\ar\CActiveRecord.php(1244): CActiveRecord->query(Object(CDbCriteria), true)

#6 C:\development\yii-1.1.2\framework\web\CActiveDataProvider.php(103): CActiveRecord->findAll(Object(CDbCriteria))

#7 C:\development\yii-1.1.2\framework\web\CDataProvider.php(120): CActiveDataProvider->fetchData()

#8 C:\development\yii-1.1.2\framework\zii\widgets\CBaseListView.php(99): CDataProvider->getData()

#9 C:\development\yii-1.1.2\framework\zii\widgets\grid\CGridView.php(220): CBaseListView->init()

#10 C:\development\yii-1.1.2\framework\web\CBaseController.php(148): CGridView->init()

#11 C:\development\yii-1.1.2\framework\web\CBaseController.php(173): CBaseController->createWidget('zii.widgets.gri...', Array)

#12 C:\development\ukash-admin\protected\views\transactions\index.php(88): CBaseController->widget('zii.widgets.gri...', Array)

#13 C:\development\yii-1.1.2\framework\web\CBaseController.php(119): require('C:\development\...')

#14 C:\development\yii-1.1.2\framework\web\CBaseController.php(88): CBaseController->renderInternal('C:\development\...', Array, true)

#15 C:\development\yii-1.1.2\framework\web\CController.php(748): CBaseController->renderFile('C:\development\...', Array, true)

#16 C:\development\yii-1.1.2\framework\web\CController.php(687): CController->renderPartial('index', Array, true)

#17 C:\development\ukash-admin\protected\controllers\TransactionsController.php(119): CController->render('index', Array)

#18 C:\development\yii-1.1.2\framework\web\actions\CInlineAction.php(32): TransactionsController->actionIndex()

#19 C:\development\yii-1.1.2\framework\web\CController.php(300): CInlineAction->run()

#20 C:\development\yii-1.1.2\framework\web\CController.php(278): CController->runAction(Object(CInlineAction))

#21 C:\development\yii-1.1.2\framework\web\CController.php(257): CController->runActionWithFilters(Object(CInlineAction), Array)

#22 C:\development\yii-1.1.2\framework\web\CWebApplication.php(320): CController->run('index')

#23 C:\development\yii-1.1.2\framework\web\CWebApplication.php(120): CWebApplication->runController('transactions/in...')

#24 C:\development\yii-1.1.2\framework\base\CApplication.php(135): CWebApplication->processRequest()

#25 C:\development\ukash-admin\index.php(12): CApplication->run()

#26 {main}

The SQL looks like this:




SELECT `t`.`trn_id` AS `t0_c0`,

`t`.`trn_started_on` AS `t0_c1`, `t`.`trn_finished_on` AS `t0_c2`,

`t`.`trn_total` AS `t0_c3`, `t`.`trn_total_real` AS `t0_c4`,

`t`.`trn_status` AS `t0_c5`, `t`.`trn_error_code` AS `t0_c6`,

`t`.`trn_email` AS `t0_c7`, `t`.`trn_by_account` AS `t0_c8`,

`t`.`trn_purse` AS `t0_c9`, `t`.`trn_paysystem` AS `t0_c10`, `t`.`trn_rate`

AS `t0_c11`, `t`.`trn_wm_id` AS `t0_c12`, `t`.`trn_system` AS `t0_c13`,

`t`.`trn_ip` AS `t0_c14`, `account`.`uac_id` AS `t2_c0`,

`account`.`uac_login` AS `t2_c1`, `account`.`uac_password` AS `t2_c2`,

`account`.`uac_active` AS `t2_c3`, `account`.`uac_last_action` AS `t2_c4`,

`account`.`uac_in_use` AS `t2_c5` FROM `transactions` `t`  LEFT OUTER JOIN

`ukash_accounts` `account` ON (`t`.`trn_by_account`=`account`.`uac_id`)

WHERE (trnvouchers.tvc_code=:ycp0) ORDER BY trn_started_on DESC LIMIT 20

As you can see there is no transaction_vouchers table joined. But the log shows that the agregation query (self::STAT relation) works just fine




SELECT COUNT(DISTINCT `t`.`trn_id`) FROM

`transactions` `t`  LEFT OUTER JOIN `transaction_vouchers` `trnvouchers` ON

(`trnvouchers`.`tvc_trn_id`=`t`.`trn_id`) LEFT OUTER JOIN `ukash_accounts`

`account` ON (`t`.`trn_by_account`=`account`.`uac_id`) WHERE

(trnvouchers.tvc_code=:ycp0)

Someone has any ideas how to fix this? :(

2

No ideas? :(

3

The pastebin links to your model seems to be wrong. Please show the source of your search() method.

4

Sorry for that, I’ve corrected all the URL’s, they all are now correct in the first message.

The actual search() code looks like this:




<?php

class Transactions extends CActiveRecord {


/* Model's code blablabla.... */




	public function search()

	{

		// Warning: Please modify the following code to remove attributes that

		// should not be searched.


		$criteria=new CDbCriteria;


		$criteria->with = array('trnvouchers', 'account', 'countVouchers');


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


		if (!empty($this->trnvouchers->tvc_code)) {

			$criteria->compare('trnvouchers.tvc_code', $this->trnvouchers->tvc_code);

		}


		if (!empty($this->account->uac_login)) {

			$criteria->compare('account.uac_login', $this->account->uac_login);

		}

		

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

			'criteria'=>$criteria,

			'pagination'=>array(

				'pageSize' => 20,

			),

		));

	}

}
5

My suspect is this self::STAT. Actually i personally avoid this relation as it’s a little too obscure for me, what’s happening on multiple relational queries :). In my understanding you can’t use a COUNT() easily with multiple JOINs.

So maybe try to disable that first.

6

Same thing with removed STAT query :(

The relation just doesn’t add the table to the join, probaby it tries to make the delayed loading. But why, if the ‘together’ flag is true by default (according to the docs)

7

Hmm. I remember there was some discussion here about different behavior of using $criteria->with against XY::model()->with()->…. Mabye your problem is related. The topic was discussed just recentently, maybe try to do some forum research.

8

It would be nice if you remember the title for the topic, can’t find it :\

UPDATE:

Well, I’m not alone in this problem, there are other posts just like mine, only no one with actually using the CGridView, but the idea is the same - JOIN is not added when using the CDbCriteria.

Actually, this thread http://www.yiiframework.com/forum/index.php?/topic/9559-cdbcriteria-lost-joins/ just given me an idea why this can be happening. This is just a guess, but still.

When you do a XY::model()->with(‘blabla’)->together()->findAll()…, you explictly say that you want it done with JOIN’s. But CDbCritirea doesn’t have the “together” flag. Maybe it’s the cause for criteria to fail and for a model to work fine?!

9

Unfortunately i don’t remember. But i guess you already found something.

Could you try the together property of your relation? To be honest: I find the docs on this property not very comprehensive. In fact: I don’t understand what happens if its true/false. (false: “… separate JOIN”, true: “joined with main table”. So joined in both cases???) So maybe play with that?

10

The “together” property doesn’t help, I tried that before posting here. Looks like in my case it is just ignored. Anyway that just looks like a major bug, so I created a bug report for it: http://code.google.com/p/yii/issues/detail?id=1358

And I finally made a work around so I get to work what I wanted. The hack is simple, but it will not work for cases when you actually want the related data to be selected into model. In my case I just need to filter by related data to get only transactions witch contain the specified value in related table.

What I did is:

[list=1]

[*]Removed the "trnvouchers" relation (commented it out).

[*]Added a public property "$trnvouchers" to my model, so I still can do "$model->trnvouchers = new TransactionVouchers();" in my controller.

[*]In “search” method made a hand join for the criteria: $criteria->join = ‘LEFT JOIN ‘.TransactionVouchers::model()->tableName().’ AS thevouchers ON t.trn_id = thevouchers.tvc_trn_id’;

[*]The tricky part - modified my “compare” like this: “$criteria->compare(‘tvc_code’, $this->trnvouchers->tvc_code);”. The tricky part here is that you CAN’T use an alias here, because it will trigger the relation resolving. So I left that out. Maybe you just can use a hand written condition here, didn’t looked into that yet.

[/list]

So now I have finally done it, and generated a bug report in a process. Hope this will be done with until 1.1.3 arrives.