Active record/query condition based on a related model attribute...

Yii 1.1 General Discussion
1

How can i achieve this? I would like to add a where condition to the current model based on a related model column

2

try this




$model=ModelName::model()->with('relatedModel')->find('relatedModel.fieldName=:condition',array(':condition'=>'%xyz%'));
3

I got this:

$criteria=new CDbCriteria;

    $criteria->with=array('tags');


    if (isset($_GET['tag'])) {


       $criteria->condition='published=:published AND tags.name=:tag';


       $criteria->params=array('published'=>1, 'tag'=>$_GET['tag']);


    } else {


       $criteria->condition='published=:published';


       $criteria->params=array('published'=>1);


    }

but Yii says that id doesn’t find the tags.name column

4

sorry, in 1.0.11 work fine

the name of relation is ‘tags’? , in 1.1.x the alias is equal to the name of relation

you call a findAll? see this http://www.yiiframework.com/forum/index.php?/topic/6577-ar-findall-bug-when-condition-contains-with/ maybe help you

try




$criteria=new CDbCriteria;

//$criteria->with=array('tags'); remove !

        if (isset($_GET['tag'])) {

           $criteria->condition='published=:published AND tags.name=:tag';

           $criteria->params=array('published'=>1, 'tag'=>$_GET['tag']);

        } else {

           $criteria->condition='published=:published';

           $criteria->params=array('published'=>1);

        }


ModelName::model()->with('tags')->findAll($criteria);
5

The problem might be in your $criteria->params where you omitted the ‘:’. It should be

$criteria->params=array(’:published’=>1);

6

My code is :

$criteria=new CDbCriteria;

    if (isset($_GET['tag'])) {


       $criteria->condition='published=:published AND tags.name=:tag';


       $criteria->params=array('published'=>1, 'tag'=>$_GET['tag']);


    } else {


       $criteria->condition='published=:published';


       $criteria->params=array('published'=>1);


    }


    


    $pages=new CPagination(Post::model()->with('tags')->count($criteria));


    $pages->pageSize=3;


    $pages->applyLimit($criteria);


    $posts=Post::model()->with('tags')->findAll($criteria);


    $this->render('list',array('posts'=>$posts,'pages'=>$pages,));

In the log i see where the error occurs. I see sql generated by active record and i see that at the point when i do: $posts=Post::model()->with(‘tags’)->findAll($criteria); it shows me this in the log:

14:56:33.191131 trace system.db.ar.CActiveRecord

Post.findAll() eagerly

14:56:33.191704 trace system.db.CDbCommand

Querying SQL: SELECT t.id AS t0_c0, t.title AS t0_c1,

t.body AS t0_c2, t.shortBody AS t0_c3, t.created_at AS

t0_c4, t.updated_at AS t0_c5, t.user_id AS t0_c6,

t.published AS t0_c7, t.permalink AS t0_c8 FROM Post t

WHERE (published=:published AND tags.name=:tag) LIMIT 3

…as you can see there is no join between the Post and Tag tables so it won’t find the Tag name column. But why active record makes no join even if i use the with('tags) method?

7

try update your CActiveRecord class

with http://code.google.com/p/yii/source/detail?r=1704

8

nothing…the same issue/error

9

you’ve done what he said onman

10

Do you have ‘together’=>false specified in your relation by any chance? That would prevent a join between the two tables.

11

yes that is not the prblem

12

no i don’t

13

Can you put the code of Post model?

14

I isolated the problem. The problem (related model column not found 'cause there is an active record generated sql without the join to Tag table) is present with this line:

$pages->applyLimit($criteria);

If i remove this line everything is ok but pagination doesn’t work of course

15

In other words if i apply a limit to the criteria also by hand:

$criteria->limit = 3;

…the generated active record sql is different and without a join so the error. But i don’t understand why this behaviour

16

the code is simple




        // in CPaganation class

        public function applyLimit($criteria)

        {

                $criteria->limit=$this->pageSize;

                $criteria->offset=$this->currentPage*$this->pageSize;

        }

the only diff is




  $criteria->offset=$this->currentPage*$this->pageSize;

is rare

see the values of $pages after

$pages->applyLimit($criteria);

var_dump($pages);

What database do you use?

sorry, I am only risking solutions

good luck

17

Anyway the problem with that code is still present. I resolved by using DAO instead of active record

18

put this issue in http://www.yiiframework.com/forum/index.php?/forum/11-bug-reports/

maybe the Master sees it and help you

it is rare that only happen to you<_<

19

Actually, I may have this problem as well going from 1.0.7 to 1.1.0 with the following code:




$allArticles = Article::model()->with('category', 'author', 'reviews', 'proficiency')->together()->findAll(array('order'=>'Article.proficiencyId,Article.title'));

This worked in the 1.0.x series but breaks in 1.1.x. I saw something in the docs about CSort but wasn’t sure if it applied to the code above, which returns a database error about being unable to find the sort columns in the unambiguated table. Those columns do exist.

20

maybe the problem is the alias of "Article"

in 1.1.0 the alias is harcode to "t"

see http://www.yiiframework.com/doc/guide/database.arr

Disambiguating Column Names

try




$allArticles = Article::model()->with('category', 'author', 'reviews', 'proficiency')->together()->findAll(array('order'=>'t.proficiencyId,t.title'));