Yii Framework Forum: Undestanding Relational Queries with CActiveDataProvider - Yii Framework Forum

Jump to content

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

Undestanding Relational Queries with CActiveDataProvider Rate Topic: ***** 4 Votes

#1 User is offline   cshehadi 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 37
  • Joined: 10-July 09
  • Location:New York City

Posted 20 February 2010 - 06:35 PM

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

2. 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)?
0

#2 User is offline   tricky101 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 2
  • Joined: 27-February 10

Posted 27 February 2010 - 06:01 PM

I've got exactly the same problem. Looks like it could be related to this http://www.yiiframew...ing-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?
0

#3 User is offline   tricky101 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 2
  • Joined: 27-February 10

Posted 27 February 2010 - 06:40 PM

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


4

#4 User is offline   mikelimassol 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 8
  • Joined: 08-January 10

Posted 07 March 2010 - 04:37 PM

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

View Posttricky101, on 27 February 2010 - 06:40 PM, said:

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



0

#5 User is offline   gallego123 

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

Posted 18 May 2010 - 11:10 AM

is it solved?
exists the issue
[edit]
the class CDbCriteria not have property "together"

This post has been edited by Horacio Segura: 18 May 2010 - 11:18 AM

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

#6 User is offline   tri 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,651
  • Joined: 20-November 08
  • Location:Stockholm, Sweden

Posted 18 May 2010 - 01:38 PM

View PostHoracio Segura, on 18 May 2010 - 11:10 AM, said:

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
Don't forget to read The Definitive Guide to Yii (en) (sv) | The class reference has the details
0

#7 User is offline   gallego123 

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

Posted 19 May 2010 - 06:57 AM

View Posttri, on 18 May 2010 - 01:38 PM, said:

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

Attached File(s)


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

#8 User is offline   tri 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,651
  • Joined: 20-November 08
  • Location:Stockholm, Sweden

Posted 21 May 2010 - 05:57 AM

View PostHoracio Segura, on 19 May 2010 - 06:57 AM, said:

use:
new CActiveDataProvider('Model', array(
			'criteria'=>$criteria,
                        'together'=>true,
		));



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
Don't forget to read The Definitive Guide to Yii (en) (sv) | The class reference has the details
0

#9 User is offline   gallego123 

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

Posted 21 May 2010 - 06:09 AM

View Posttri, on 21 May 2010 - 05:57 AM, said:

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

why not? is an improvement to yii

Quote

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

okey, i newbie
KISS - Keep It Simple Stupid
ASAP-As Soon As Possible
http://www.yiiframew...oc/cookbook/71/
0

#10 User is offline   mech7 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 220
  • Joined: 26-March 09

Posted 20 July 2010 - 09:52 PM

Did anybody already put a ticket for this issue?
0

#11 User is offline   mech7 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 220
  • Joined: 26-March 09

Posted 20 July 2010 - 09:54 PM

View PostHoracio Segura, on 21 May 2010 - 06:09 AM, said:

why not? is an improvement to yii


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

#12 User is offline   mech7 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 220
  • Joined: 26-March 09

Posted 20 July 2010 - 10:38 PM

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

#13 User is offline   mech7 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 220
  • Joined: 26-March 09

Posted 20 July 2010 - 11:25 PM

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"'
                    
                ),
            ));

0

#14 User is offline   Mantus 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 10
  • Joined: 29-March 10
  • Location:Ludwigsburg, Germany

Posted 21 July 2010 - 07:16 AM

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

#15 User is offline   gallego123 

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

Posted 07 September 2010 - 03:10 PM

look this (since 1.1.4)
http://www.yiiframew...together-detail
KISS - Keep It Simple Stupid
ASAP-As Soon As Possible
http://www.yiiframew...oc/cookbook/71/
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