Problem with CActiveDataProvider

Recently decided to move from Zend to Yii, a problem with CActiveDataProvider, namely the use of related tables.

There are three tables, posts, posts_topics, topics (the standard model of tag many to many)

Must withdraw all posts on a given topic (tag)




                       $dataProvider = new CActiveDataProvider('Post', array(

                                'criteria' => array(

                                        'condition' => 'topics.seo = :seo',

                                        'params' => array(':seo' => $_GET['topic_url']),

                                        'with' => array('topics'),

                                        'order' => 'update_time DESC'

                                ),

                                'pagination' => array(

                                        'pageSize' => Yii::app()->params['postsPerPage'],

                                ),

                        ));



And judging by the documentation is correct.

But the derivation of an error “Column not found: 1054 Unknown column ‘topics.seo’ in ‘where clause’”

I saw the log and see two queries:

first (which incidentally works and built correctly)




SELECT

  COUNT(DISTINCT `t`.`id`)

FROM `posts` `t`

  LEFT OUTER JOIN `posts_topics` `topics_topics`

    ON (`t`.`id` = `topics_topics`.`post_id`)

  LEFT OUTER JOIN `topics` `topics`

    ON (`topics`.`id` = `topics_topics`.`topic_id`)

WHERE (topics.seo = :seo)



and second (which throws out an exception)




SELECT

  `t`.`id`          AS `t0_c0`,

  `t`.`title`       AS `t0_c1`,

  `t`.`content`     AS `t0_c2`,

  `t`.`create_time` AS `t0_c3`,

  `t`.`update_time` AS `t0_c4`,

  `t`.`author_id`   AS `t0_c5`,

  `t`.`seo`         AS `t0_c6`

FROM `posts` `t`

WHERE (topics.seo = :seo)

ORDER BY update_time DESC

LIMIT 15)



Why does the second query is not related tables?

I am Russian, sorry for bad english.

Hi Octane,

I think probably there is a bug with CActiveDataPrivoder, I had the same problem too. you might use the following way to resolve it.

class DataProvider extends CActiveDataProvider

{

    //Will fire the ActiveFinder together() method when true


    public $joinAll=false;


    


    /**


     * Fetches the data from the persistent data storage.


     * @return array list of data items


     */


    protected function fetchData()


    {


            $criteria=clone $this->getCriteria();


            if(($pagination=$this->getPagination())!==false)


            {


                    $pagination->setItemCount($this->getTotalItemCount());


                    $pagination->applyLimit($criteria);


            }


            if(($sort=$this->getSort())!==false)


                    $sort->applyOrder($criteria);


            


            //Use together() for query?


            if ($this->joinAll) 


            { 


                    return CActiveRecord::model($this->modelClass)->with($criteria->with)->together()->findAll($criteria);


            }


            else


            {


                    return CActiveRecord::model($this->modelClass)->findAll($criteria);


            }


    }

}

a sample for invoking:

		$dataProvider=new DataProvider('Pages', array(


				'criteria'=>array(


						'with'=>array('categories'),


						'condition'=>'categories.id=:categoryId',


						'params'=>array(':categoryId'=>$_POST['parent_id']), 


				 'pagination'=>array(


						 'pageSize'=>self::PAGE_SIZE),


				),


		));

Similar problem here :(

It looks like the relation gets ‘loaded’ for count queries and informational queries, but not for the actual finder query. I’m curious why it’s done that way in the first place. Why not do both in one step?




    public function search ()

    {

        // Parse dateRange if it was given.


            $this->parseDateRange();


        // Setup criteria.


            $c = new CDbCriteria;

    

            if ( $this->startDate && $this->endDate )

            {

                $c->addBetweenCondition('t.created_at', $this->startDate, $this->endDate);

            }

        

            if ( is_numeric($this->performer) )

            {

                $c->with = array('performers' => array(

                    'condition' => 'performers.id = ' . $this->performer)

                );

            }    

    

    	return new CActiveDataProvider($this->model, array('criteria' => $c));

    } 



Thanks Adams, now everything works

How and where to put this to make it work? I’ve tried editing CActiveDataProvider with no luck and being a newbie of two days, I don’t really know my way around the block here. Any help appreciated.

Thanks

the extension might be placed under the directory ‘components’, then Yii would automatically load the class.

Thanks, I’ll try that.