Yii Framework Forum: Problem with CDbCriteria and relational data - Yii Framework Forum

Jump to content

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

Problem with CDbCriteria and relational data Rate Topic: -----

#1 User is offline   Digital God 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 178
  • Joined: 30-January 09

Posted 28 July 2010 - 05:44 AM

I'm trying to add a criteria for relation record.
here is my code
        $criteria=new CDbCriteria(array(
            'condition' => 't.status = '.Publication::STATUS_PUBLISHED,
            'order' => 't.create_time DESC',
            'together' => true,
            'with' => array(
                'category' => array(
                    'condition' => 'category.status = '.Category::STATUS_ACTIVE,
                    
                ), 'category.community'
            )
        ));

        if(isset($_GET['blog']))
            $criteria->addSearchCondition('community.adress', $_GET['blog']);
        else
            $criteria->addSearchCondition('onmain', true);

        $dataProvider=new CActiveDataProvider('Publication', array(
            'pagination'=>array(
                'pageSize'=> 5,
            ),
            'criteria'=>$criteria,
        ));


i use CListView to show records. But i get sql error

Quote

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'community.adress' in 'where clause'


the "COUNT" query is ok
SELECT COUNT(*) FROM `publications` `t` LEFT OUTER JOIN `publications_has_categories` `category_category` ON (`t`.`id`=`category_category`.`publication_id`) LEFT OUTER JOIN `categories` `category` ON (`category`.`id`=`category_category`.`category_id`) LEFT OUTER JOIN `communities` `community` ON (`category`.`community_id`=`community`.`id`) WHERE ((t.status = 1) AND (community.adress = 'test')) AND (category.status = 1)


it has all joins, but the "data" query doesn't

SELECT `t`.`id` AS `t0_c0`, `t`.`author_id` AS `t0_c1`, `t`.`title` AS `t0_c2`, `t`.`url` AS `t0_c3`, `t`.`description` AS `t0_c4`, `t`.`short_content` AS `t0_c5`, `t`.`full_content` AS `t0_c6`, `t`.`create_time` AS `t0_c7`, `t`.`update_time` AS `t0_c8`, `t`.`reads` AS `t0_c9`, `t`.`comments` AS `t0_c10`, `t`.`status` AS `t0_c11`, `t`.`onmain` AS `t0_c12` FROM `publications` `t` WHERE ((t.status = 1) AND (community.adress = 'test')) ORDER BY t.create_time DESC LIMIT 5


if i remove addSearchCondition, it makes 3 queries.

first query is with count, second is with limit, and third is data. Why it makes 3 queries? why pagination is separate query?
И сказал админ f*$#. И стало FAQ!
Posted Image
0

#2 User is offline   zaccaria 

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

Posted 28 July 2010 - 06:06 AM

You should specify to do the with 'togheter' in order to retrive all in one query.

Like that you should see 2 queries, one with count and one with limit. That is done for retrive the totalItemCount (by counting) and then select only the needed items from the table (limit).
0

#3 User is offline   Digital God 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 178
  • Joined: 30-January 09

Posted 28 July 2010 - 06:26 AM

View Postzaccaria, on 28 July 2010 - 06:06 AM, said:

You should specify to do the with 'togheter' in order to retrive all in one query.

Like that you should see 2 queries, one with count and one with limit. That is done for retrive the totalItemCount (by counting) and then select only the needed items from the table (limit).


I already put it in CDbCriteria

...
'together' => true,
...

И сказал админ f*$#. И стало FAQ!
Posted Image
0

#4 User is offline   zaccaria 

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

Posted 28 July 2010 - 06:51 AM

Sorry, I didn't noticed.

There is a similar bug in CActiveRecord hereand here, maybe is related.

Often happens that count and select have different behaviour, and this, in my opinion, is a bug by himself. I think that this two query should always be identical except for count and limit, but often it doesn't happen.

I can't find any mistake in your code, maybe is a bug. You can post in bug report.
0

#5 User is offline   Digital God 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 178
  • Joined: 30-January 09

Posted 28 July 2010 - 06:56 AM

forget.. i've found issue http://code.google.c.../detail?id=1078
maybe qiang will fix it.
И сказал админ f*$#. И стало FAQ!
Posted Image
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