Lazy loading and IN condition

Archives Yii 1.0.x Discussions
1

Hello again…

I have the following:

Table1

id        number        status      idT2

1          123-010            a        1

2          124-154            b        1

3          125-458            c        2

4          128-548            a        3

5          126-558            b        4

Table2

id      name

1        XX

2        YY

3        WW

4        ZZ

5        MM

in my Table2 model I have te following relation declaration



'tables1'=>array(self::HAS_MANY,'Table1','idT2');

now, in an action of my Table2Controller i have the following:



$t2 = $this->loadTable2();





  $inCondition=Table2::model()->dbConnection->commandBuilder->createInCondition(Table2::model()->tableName(), 'status', array('a','b'));





   $criteria->condition = $inCondition;


   $t1s= $t2->tables1($criteria);

But alway $t1s is an empty array…

Anybody can help me with this?

2

Did you check the generated SQL? Is it correct?

3

If I do this



Yii::log('---------------------');





$inCondition=Table2::model()->dbConnection->commandBuilder->createInCondition(Table2::model()->tableName(), 'status', array('a','b'));





   $criteria->condition = $inCondition;


   $t1s= $t2->tables1($criteria);


Yii::log('---------------------');

I have the following log:



2009/06/12 09:16:52 [info] [application] -------------------


2009/06/12 09:16:52 [trace] [system.db.CDbCommand] Querying SQL: SHOW COLUMNS FROM `Table1`


2009/06/12 09:16:52 [trace] [system.db.CDbCommand] Querying SQL: SHOW CREATE TABLE `Table1`


2009/06/12 09:16:52 [trace] [system.db.ar.CActiveRecord] lazy loading Table2.tables1


2009/06/12 09:16:52 [info] [application] -------------------

BTW, my log config:






'log'=>array(


   'class'=>'CLogRouter',


   'routes'=>array(


      array(


         'class'=>'CFileLogRoute',


         'levels'=>'error, warning, trace, profile, info',


      ),


   ),


),
4

Do you mean $t2->tables1($criteria) doesn't execute any SQLs?

What is $t2? Is it a new record?

5

Aparently $t2->tables1($criteria) doesn't execute any SQLs!!! I  think this too…

$t2 is an AR object.  I do this in Table2Controller: $t2 = $this->loadTable2();

6

Do you already have a method named tables1() ?

If this still doesn't solve problem, please insert at line 530 in CActiveRecord.php a die() statement and see if it is reached.

7

tables1 is a relation declaration declared in Table2 model.



'tables1'=>array(self::HAS_MANY,'Table1','idT2');

I'm using the version 1.0.6 (the SVN one, updated today)

The guide says:

"Starting from version 1.0.5, dynamic query options can also be used when using the lazy loading approach to perform relational query. To do so, we should call a method whose name is the same as the relation name and pass the dynamic query options as the method parameter."

8

I am asking if you already have a method named tables1(). Please also try modifying CActiveRecord to identify where the problem is.

9

I have no method named tables1().

and if I insert a die on line 530 of CActiveRecord, the execution stops at this point.

10

found a solution:

if I do this:



<?php





$t2 = $this->loadTable2();





  $inCondition=Table2::model()->dbConnection->commandBuilder->createInCondition(Table2::model()->tableName(), 'status', array('a','b'));





   $criteria->condition = $inCondition;


   $t1s= $t2->tables1(array($criteria)); // <-- note that I put the $criteria inside an array.

all works perfect… but I think that the whole thing should work simply passing the $criteria object…

EDIT:

The line 531 of CActiveRecord says:



if($params!==array()) // dynamic query

and obviously the code of this if does his work… but what I have been passing was not an array, just an object of CDbCriteria class…

11

forgive what I say about the solution…

If I put the criteria inside an array, it is ignored totally and the result is the same that do



$t1s = $t2->tables1;
12

Finally I have the solution, but is a bit large to explain:

First thing I do is



$t1s = $2->tables1($criteria->toArray());

But this gives me an error on the sql query:



`Table2`.`id` AS `t0_c0`, t1.`id` AS `t1_c0`, t1.`number` AS `t1_c3`, t1.`status` AS `t1_c9` FROM `Table2` LEFT OUTER JOIN `Table1` t1 ON (t1.`idT2`=`Table2`.`id`) AND (`Table1`.`status` IN ('a', 'b')) WHERE (`Table2`.`id`=1);

The error say "Unknown column 'Table1.status' in 'on clause' "

Then, in my relation declaration y add an alias



'tables1'=>array(self::HAS_MANY,'Table1','idT2','alias'=>'Table1');

Now, this works as espected…

The generated sql after setting the alias is:



SELECT `Table2`.`id` AS `t0_c0`, Table1.`id` AS `t1_c0`, Table1.`number` AS `t1_c3`, Table1.`status` AS `t1_c9` FROM `Table2` LEFT OUTER JOIN `Table1` Table1 ON (Table1.`idT2`=`Table2`.`id`) AND (`Table1`.`status` IN ('a', 'b')) WHERE (`Table2`.`id`=1)

Now all works perfect.

BTW: As discussed in other thread about made the Table name the default alias, what happens to me is a good example of why this enhancement would be fantastic…

http://www.yiiframew…pic,2626.0.html