ar scopes+relations query problem

Archives Yii 1.0.x Discussions
1

Hi there,

I have a complicated join query using active record and it didn’t display what I needed. Not too sure whether I made any mistakes. I have went through the yii guide, active record source code, reference documentation but has yet to get a solution. Please advise.

In controller:




Category::model()->published(0,50)->findAll();

In model: (Category)




public function relations()

{

  'user_owned'=>array(

    self::MANY_MANY,

    'User',

    'user-category(user_id,category_id)',

    'condition'=>'??.id=:id',

    'params'=>array(':id'=>Yii::app()->user->id),

  ),

}


public function scopes()

{

  return array(

    'published'=>array(

      'condition'=>'deleted_time IS NULL',

      'order'=>'name ASC',

      'with'=>'user_owned',

    ),

  );

}


public function published($offset=0,$limit=null)

{

  $this->getDbCriteria()->mergeWith(array(

    'offset'=>$offset,

    'limit'=>$limit,

  ));

  return $this;

}

In application.log:




2009/09/09 09:19:36 [trace] [system.db.ar.CActiveRecord] Category.findAll()

2009/09/09 09:19:36 [trace] [system.db.CDbCommand] Querying SQL: SELECT * FROM `category` LIMIT 50

I am expecting some like these sort of query:

SELECT * FROM category LEFT OUTER JOIN user-category user_owned_t1 ON (category.id=user_owned_t1.category_id) LEFT OUTER JOIN user t1 ON (t1.id=user_owned_t1.user_id) WHERE (t1.id=:id) LIMIT 50. Bind with parameter :id=‘2’

Thanks.

2 

you essentially defined the scope 'published' twice.


change your published method to


[code]

public function published($offset=0,$limit=-1)

{

  $this->getDbCriteria()->mergeWith(array(

    'offset'=>$offset,

    'limit'=>$limit,

    'condition' = < 'deleted_time IS NULL',

    'with' 'user_owned'

  ));

  return $this;

}

and remove the scopes method

I think that ought to work

3

Hi MarcS,

I have tried your advice and you are right that I defined the ‘published’ twice I guess. Because initially I thought defining ‘published’ as function will override scopes ‘published’ by merging both criteria together.

Anyway the result is almost there except that ‘with’ does not seem to work in ‘mergeWith’. I got this sql statement:




SELECT * FROM `category` WHERE deleted_time IS NULL ORDER BY name ASC LIMIT 50

thanks MarcS anyway.

4

CDbCriteria doesn’t support with() (yet).

5

ok thanks i guess i have to use this:




Category::model()->with('user-owned')->published(0,50)->findAll();

thanks to all.