Undestanding Relational Queries with CActiveDataProvider

Hi,

I’m having trouble figuring out how to run a query where the criteria is in a related table.

I’ve got two tables in a many to many relationship




table1: athletes (class Athlete) ... 

    id: int

    first_name varchar(45) etc...


table2: events (class Event)

    id_event: int (yes, I know my naming conventions aren't consistent)

    event: varchar(45) etc..



And then there’s a table that relates the two:




table3:

athletes_events

    id: int

    id_athlete: int

    id_event: int



in the model for athletes, I’ve specified this relation:




'events'=>array(self::MANY_MANY, 'Event','athletes_events(id_athlete,id_event)'),



I’m trying to retrieve athletes participating only in a certain event, so now in the Athlete controller, I’ve set up $dataProvider as follows…




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

	    'criteria'=>array(

 	        'with'=>'events',

		'together'=>true,

		'condition'=>'events.id_event=:id_event',

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

		'order'=>'last_name',

	    ),

	    'pagination'=>array(

		'pageSize'=>self::PAGE_SIZE,

	    ),

	));



but for some reason this is returning an error. I’ve noticed Yii is running two queries- why?

the first is seems to be getting a count for some reason - and this executes the join specified by the "with" clause of the criteria:




SELECT COUNT(DISTINCT `t`.`id`) FROM `athletes` `t` LEFT OUTER JOIN `athletes_events` `events_events` ON (`t`.`id`=`events_events`.`id_athlete`) LEFT OUTER JOIN `events` `events` ON (`events`.`id_event`=`events_events`.`id_event`) WHERE (events.id_event=:id_event). Bind with parameter :id_event='6'



what’s weird is that the next query - which is the one that retrieves the data from the athlete table - does NOT execute a join, and so it returns an error:




SELECT `t`.`id` AS `t0_c0`, `t`.`first_name` AS `t0_c1`, `t`.`last_name` AS `t0_c2` FROM `athletes` `t` WHERE (events.id_event=:id_event) ORDER BY last_name LIMIT 40. Bind with parameter :id_event='6'



So… I guess I’ve got two questions:

  1. what is the purpose of that first COUNT query

and

  1. why is the COUNT query joining on events, but the second query which is retrieving data is NOT (causing an error: Base table or view not found: 1109 Unknown table ‘events’ in where clause)?

I’ve got exactly the same problem. Looks like it could be related to this http://www.yiiframework.com/forum/index.php?/topic/7344-with-not-working-on-findall/

I’m not sure if there’s someway for the dataprovider to tell the activefinder to call the together method?

Did you ever solve this?

There’s probably a better solution (I’ve only been using yii for a day) but I’ve ended up extending the CActiveDataProvider class slightly:

File: protected/components/DataProvider.php




<?php


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

		}

	}

	

}



File: protected/controllers/GroupController.php




<?php


	/**

	 * Lists all models.

	 */

	public function actionIndex()

	{

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

			'criteria'=>array(

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

				'condition'=>'access.id=:userId',

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

			),

		));

		//Set new property

		$dataProvider->joinAll = true;

		

		$this->render('index',array(

			'dataProvider'=>$dataProvider,

		));

		

	}




I have the same issue,

your code seems to do the job. I dont understand why disable the joins. a lot of us developers do our filtering using advance joins using multiple tables.

thanks a lot

is it solved?

exists the issue

[edit]

the class CDbCriteria not have property "together"

I have the same problem (latest svn, r2135).

Will try the fix.

/Tommy

here the solution. Thanks trickt101

test fisrt and then create the issue

use:




new CActiveDataProvider('Model', array(

			'criteria'=>$criteria,

                        'together'=>true,

		));



the modify at the core




<?php

/**

 * CActiveDataProvider implements a data provider based on ActiveRecord.

 *

 * CActiveDataProvider provides data in terms of ActiveRecord objects which are

 * of class {@link modelClass}. It uses the AR {@link CActiveRecord::findAll} method

 * to retrieve the data from database. The {@link criteria} property can be used to

 * specify various query options, such as conditions, sorting, pagination, etc.

 *

 * CActiveDataProvider may be used in the following way:

 * <pre>

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

 *     'criteria'=>array(

 *         'condition'=>'status=1 AND tags LIKE :tags',

 *         'params'=>array(':tags'=>$_GET['tags']),

 *         'with'=>array('author'),

 *     ),

 *     'pagination'=>array(

 *         'pageSize'=>20,

 *     ),

 * ));

 * // $dataProvider->getData() will return a list of Post objects

 * </pre>

 *

 * @author Qiang Xue <qiang.xue@gmail.com>

 * @version $Id: CActiveDataProvider.php 1917 2010-03-15 15:07:33Z qiang.xue $

 * @package system.web

 * @since 1.1

 */

class CActiveDataProvider extends CDataProvider

{

	/**

	 * @var string the primary ActiveRecord class name. The {@link getData()} method

	 * will return a list of objects of this class.

	 */

	public $modelClass;

	/**

	 * @var string the name of key attribute for {@link modelClass}. If not set,

	 * it means the primary key of the corresponding database table will be used.

	 */

	public $keyAttribute;


	private $_criteria;

        private $_together;


	/**

	 * Constructor.

	 * @param string the model class. This will be assigned to the {@link modelClass} property.

	 * @param array configuration (name=>value) to be applied to this data provider.

	 * Any public properties of the data provider can be configured via this parameter

	 */

	public function __construct($modelClass,$config=array())

	{

		

                $this->modelClass=$modelClass;

		$this->setId($modelClass);

		foreach($config as $key=>$value)

			$this->$key=$value;

	}




	/**

	 * @return param Together. If true, the method fetchData add together at the findAll

	 */

	public function getTogether()

	{

		if($this->_together===null)

			$this->_together=false;

		return $this->_together;

	}


	/**

	 * @param boolean the together option. If true, the method fetchData add together at the findAll

	 */

	public function setTogether($value)

	{

		$this->_together=(bool) $value;

	}




	/**

	 * @return CDbCriteria the query criteria

	 */

	public function getCriteria()

	{

		if($this->_criteria===null)

			$this->_criteria=new CDbCriteria;

		return $this->_criteria;

	}


	/**

	 * @param mixed the query criteria. This can be either a CDbCriteria object or an array

	 * representing the query criteria.

	 */

	public function setCriteria($value)

	{

		$this->_criteria=$value instanceof CDbCriteria ? $value : new CDbCriteria($value);

	}


	/**

	 * @return CSort the sorting object. If this is false, it means the sorting is disabled.

	 */

	public function getSort()

	{

		if(($sort=parent::getSort())!==false)

			$sort->modelClass=$this->modelClass;

		return $sort;

	}


	/**

	 * 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->_together) 

                { 

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

                }

                else

                {

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

                }

		

	}


	/**

	 * Fetches the data item keys from the persistent data storage.

	 * @return array list of data item keys.

	 */

	protected function fetchKeys()

	{

		$keys=array();

		if($this->keyAttribute===null)

		{

			foreach($this->getData() as $i=>$data)

				$keys[$i]=$data->getPrimaryKey();

		}

		else

		{

			foreach($this->getData() as $i=>$data)

				$keys[$i]=$data->{$this->keyAttribute};

		}

		return $keys;

	}


	/**

	 * Calculates the total number of data items.

	 * @return integer the total number of data items.

	 */

	protected function calculateTotalItemCount()

	{

		return CActiveRecord::model($this->modelClass)->count($this->getCriteria());

	}

}



Since we don’t want to change core files, this is how we can extend CActiveDataProvider




<?php


class DataProvider extends CActiveDataProvider

{

        private $_together = 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);

                

                if ($this->_together) 

                { 

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

                }

                else

                {

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

                }

        }

        

}

I don’t think we need the getter/setter.

/Tommy

why not? is an improvement to yii

okey, i newbie

Did anybody already put a ticket for this issue?

It will give problems upgrading if it is implemented different or not in future versions ;)

Btw shouldn’t it be…


<?php


class DataProvider extends CActiveDataProvider {


    public $together = 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);


        if ($this->together) {

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

        } else {

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

        }

    }


}

If the property is private then it will never be set ;)

Also If i would set the condition in criteria I still get problems…




$criteria=new CDbCriteria(array(

                'condition'=>'status = '.Books::STATUS_PUBLISHED,

                'order'=>'t.id DESC',

                'with'=> array('categories',                    

                    'alias' => 'categories',

                    'condition' => 'categories.name = "birds"'

                    

                ),

            ));



Its working for me. I have posted my code here..

look this (since 1.1.4)

http://www.yiiframework.com/doc/api/CDbCriteria#together-detail