Yii Framework Forum: Active record/query condition based on a related model attribute... - Yii Framework Forum

Jump to content

  • (2 Pages)
  • +
  • 1
  • 2
  • You cannot start a new topic
  • You cannot reply to this topic

Active record/query condition based on a related model attribute... Rate Topic: -----

#1 User is offline   skyblaze 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 106
  • Joined: 07-January 09

Posted 12 January 2010 - 02:12 PM

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

#2 User is offline   Horacio Segura 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 535
  • Joined: 11-August 09
  • Location:Argentina

Posted 12 January 2010 - 02:43 PM

View Postskyblaze, on 12 January 2010 - 02:12 PM, said:

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


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

KISS - Keep It Simple Stupid
ASAP-As Soon As Possible
http://www.yiiframew...oc/cookbook/71/
http://hmsegura.blogspot.com/
0

#3 User is offline   skyblaze 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 106
  • Joined: 07-January 09

Posted 13 January 2010 - 04:07 AM

View PostHoracio Segura, on 12 January 2010 - 02:43 PM, said:

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


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
0

#4 User is offline   Horacio Segura 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 535
  • Joined: 11-August 09
  • Location:Argentina

Posted 13 January 2010 - 05:22 AM

View Postskyblaze, on 13 January 2010 - 04:07 AM, said:

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


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.yiiframew...-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);

KISS - Keep It Simple Stupid
ASAP-As Soon As Possible
http://www.yiiframew...oc/cookbook/71/
http://hmsegura.blogspot.com/
0

#5 User is offline   Onman 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 175
  • Joined: 26-December 09
  • Location:The Netherlands

Posted 13 January 2010 - 05:35 AM

View Postskyblaze, on 13 January 2010 - 04:07 AM, said:

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


The problem might be in your $criteria->params where you omitted the ':'. It should be
$criteria->params=array(':published'=>1);
0

#6 User is offline   skyblaze 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 106
  • Joined: 07-January 09

Posted 13 January 2010 - 08:50 AM

View PostOnman, on 13 January 2010 - 05:35 AM, said:

The problem might be in your $criteria->params where you omitted the ':'. It should be
$criteria->params=array(':published'=>1);

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?
0

#7 User is offline   Horacio Segura 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 535
  • Joined: 11-August 09
  • Location:Argentina

Posted 13 January 2010 - 09:39 AM

View Postskyblaze, on 13 January 2010 - 08:50 AM, said:

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?


try update your CActiveRecord class

with http://code.google.c...e/detail?r=1704
KISS - Keep It Simple Stupid
ASAP-As Soon As Possible
http://www.yiiframew...oc/cookbook/71/
http://hmsegura.blogspot.com/
0

#8 User is offline   skyblaze 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 106
  • Joined: 07-January 09

Posted 13 January 2010 - 10:05 AM

View PostHoracio Segura, on 13 January 2010 - 09:39 AM, said:

try update your CActiveRecord class

with http://code.google.c...e/detail?r=1704

nothing...the same issue/error
0

#9 User is offline   Horacio Segura 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 535
  • Joined: 11-August 09
  • Location:Argentina

Posted 13 January 2010 - 10:08 AM

View Postskyblaze, on 13 January 2010 - 10:05 AM, said:

nothing...the same issue/error


you've done what he said onman

KISS - Keep It Simple Stupid
ASAP-As Soon As Possible
http://www.yiiframew...oc/cookbook/71/
http://hmsegura.blogspot.com/
0

#10 User is offline   Sander 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 169
  • Joined: 03-November 09
  • Location:Amsterdam, Netherlands

Posted 13 January 2010 - 10:10 AM

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

#11 User is offline   skyblaze 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 106
  • Joined: 07-January 09

Posted 13 January 2010 - 10:17 AM

View PostHoracio Segura, on 13 January 2010 - 10:08 AM, said:

you've done what he said onman
yes that is not the prblem
0

#12 User is offline   skyblaze 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 106
  • Joined: 07-January 09

Posted 13 January 2010 - 10:17 AM

View PostSander, on 13 January 2010 - 10:10 AM, said:

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

no i don't
0

#13 User is offline   Horacio Segura 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 535
  • Joined: 11-August 09
  • Location:Argentina

Posted 13 January 2010 - 10:31 AM

Can you put the code of Post model?
KISS - Keep It Simple Stupid
ASAP-As Soon As Possible
http://www.yiiframew...oc/cookbook/71/
http://hmsegura.blogspot.com/
0

#14 User is offline   skyblaze 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 106
  • Joined: 07-January 09

Posted 13 January 2010 - 11:18 AM

View PostHoracio Segura, on 13 January 2010 - 10:31 AM, said:

Can you put the code of Post model?

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
0

#15 User is offline   skyblaze 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 106
  • Joined: 07-January 09

Posted 13 January 2010 - 11:33 AM

View Postskyblaze, on 13 January 2010 - 11:18 AM, said:

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

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
0

#16 User is offline   Horacio Segura 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 535
  • Joined: 11-August 09
  • Location:Argentina

Posted 13 January 2010 - 03:59 PM

View Postskyblaze, on 13 January 2010 - 11:33 AM, said:

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


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
KISS - Keep It Simple Stupid
ASAP-As Soon As Possible
http://www.yiiframew...oc/cookbook/71/
http://hmsegura.blogspot.com/
0

#17 User is offline   skyblaze 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 106
  • Joined: 07-January 09

Posted 15 January 2010 - 05:25 AM

View PostHoracio Segura, on 13 January 2010 - 03:59 PM, said:

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

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

#18 User is offline   Horacio Segura 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 535
  • Joined: 11-August 09
  • Location:Argentina

Posted 15 January 2010 - 07:04 AM

View Postskyblaze, on 15 January 2010 - 05:25 AM, said:

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


put this issue in http://www.yiiframew...11-bug-reports/
maybe the Master sees it and help you

it is rare that only happen to you<_<
KISS - Keep It Simple Stupid
ASAP-As Soon As Possible
http://www.yiiframew...oc/cookbook/71/
http://hmsegura.blogspot.com/
0

#19 User is offline   drech 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 100
  • Joined: 06-April 09

Posted 15 January 2010 - 03:06 PM

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.
0

#20 User is offline   Horacio Segura 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 535
  • Joined: 11-August 09
  • Location:Argentina

Posted 15 January 2010 - 03:27 PM

View Postdrech, on 15 January 2010 - 03:06 PM, said:

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.


maybe the problem is the alias of "Article"
in 1.1.0 the alias is harcode to "t"
see http://www.yiiframew...de/database.arr
Disambiguating Column Names
try
$allArticles = Article::model()->with('category', 'author', 'reviews', 'proficiency')->together()->findAll(array('order'=>'t.proficiencyId,t.title'));

KISS - Keep It Simple Stupid
ASAP-As Soon As Possible
http://www.yiiframew...oc/cookbook/71/
http://hmsegura.blogspot.com/
0

Share this topic:


  • (2 Pages)
  • +
  • 1
  • 2
  • 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