Yii Framework Forum: CDbCriteria - lost joins - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

CDbCriteria - lost joins Rate Topic: -----

#1 User is offline   Cooper 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 13
  • Joined: 26-November 09

Posted 30 May 2010 - 03:42 PM

I yet another inexplicable problem with CDbCriteria.

Here is my code:

    $criteria=new CDbCriteria(array(
        'condition'=>'product_master = 0',
        'order'=>'date_added DESC',
        'with'=>array('systems','modules')
    ));
    
    if(isset($_GET['mid']))
        $criteria->addCondition('modules.oem_module_id = ' . (int)$_GET['mid']);
        
    if(isset($_GET['sid']))
        $criteria->addCondition('systems.oem_system_id = ' . (int)$_GET['sid']);
        
    $pages = new CPagination(Product::model()->count($criteria));
		$pages->pageSize=self::PAGE_SIZE;
		$pages->applyLimit($criteria);

    $products = Product::model()->findAll($criteria);


As can be seen, I have three tables: products, systems(alias declared in relations), modules (alias declared in relations).

The problem:

Product::model()->count($criteria) executes a perfect query with the expected joins.

Product::model()->findAll($criteria) fails. The joins are missing, thus systems.oem_system_id or modules.oem_module_id are declared as "Column not found".


What am I doing wrong??
0

#2 User is offline   Cooper 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 13
  • Joined: 26-November 09

Posted 02 June 2010 - 01:56 AM

The only way I can get this to work is to use the following:

    $products = Product::model()->with('systems','modules')->together()->findAll($criteria);


To me, this looks like a bug, since the relations are already declared in $criteria and work just fine with the count() query.
0

#3 User is offline   zaccaria 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 2,232
  • Joined: 04-October 09
  • Location:Moscow

Posted 02 June 2010 - 06:59 AM

Reading the documentation of CActiveFinder, it looks that without togheter the query is done without the join, in order to select the main table and the related table in a separate statement.

That's mean that without togheter you cannot put condition/group/having on fields that belongs to other tables.

So it looks that the SELECT is not bagous, it follows the documentation. What is strange is that the count is working properly, because, reading the documentation, it shouldn't do the join in a single sql statement.

That's fact is just an aspect of a strange behaviour of CActiveRecord::count() and CActiveRecord::find() follows different rules.

Because of that sometime happens that the count retrives wrong result (if there are HAVING clause, for example).

Do not rely on the fact that count and find and count are doing the same work, that's not true.
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users