Yii Framework Forum: beginner question with MANY_MANY and CDbCriteria - Yii Framework Forum

Jump to content

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

beginner question with MANY_MANY and CDbCriteria Rate Topic: ***** 2 Votes

#1 User is offline   Mantus 

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

Posted 16 July 2010 - 10:29 AM

Hi,

I have a problem understanding how to search for records without making plain sql.

I have a class User and a class Group which are both CActiveRecord.

Now I want to find all users with a group name which contains "1a".

So I've tried this:

class User extends CActiveRecord {
.....

public function search() {
        $criteria=new CDbCriteria;
        
        $criteria->with = array('groups'=>array('select'=>'groups.name'));
        //$criteria->select = 'id, groups.name';
        //$criteria->condition = 'groups.name LIKE ' . "'%1a%'";
       	//$criteria->compare('groups.name',$this->groups->name);
       	$criteria->addSearchCondition('groups.name','%1a%');
        
        
        return new CActiveDataProvider('User', array(
            	'criteria'=>$criteria,
            	'sort'=>array(
            	'defaultOrder'=>'username DESC',
            ),
        ));
    }

Could please anyone tell me how to do this correctly or where I can find an example/documentation for this?

The relation:

class User extends CActiveRecord {
.....

	public function relations()
	{
		return array(
				'groups' => array(
					self::MANY_MANY,
					'Group',
					'tbl_user_group(user_id, group_id)'
					),
	}


I have read the documentation. Have I overseen this topic?

Thanks for any help!
0

#2 User is offline   gallego123 

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

Posted 16 July 2010 - 12:49 PM

look this, maybe help you

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

#3 User is offline   gallego123 

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

Posted 16 July 2010 - 01:00 PM

exist a request for this feature

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

#4 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,013
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 16 July 2010 - 05:58 PM

Use a INNER JOIN for your group join:

$criteria->with = array('groups'=>array('select'=>'groups.name', 'joinType'=>'INNER JOIN'));


This way, the joined related tables will act like a filter.
0

#5 User is offline   mech7 

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

Posted 20 July 2010 - 06:23 AM

View PostMike, on 16 July 2010 - 05:58 PM, said:

Use a INNER JOIN for your group join:

$criteria->with = array('groups'=>array('select'=>'groups.name', 'joinType'=>'INNER JOIN'));


This way, the joined related tables will act like a filter.


Can you show the full code please.. I am trying something like this..

$criteria=new CDbCriteria(array(
                'condition'=>'status = '.Books::STATUS_PUBLISHED,
                'order'=>'id DESC',
                'with'=> array('categories' => array(
                    'joinType' => 'INNER JOIN',
                )),
            ));

            $criteria->addSearchCondition('categories.name','%bird%');

            $dataProvider = new CActiveDataProvider('Books', array(
                'criteria'=> $criteria,
                'pagination'=>array(
                    'pageSize'=>self::PAGE_SIZE,
                ),
            ));


But it will always say categories.name is not a column
0

#6 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,013
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 20 July 2010 - 06:34 AM

Oh, i'm sorry, i think there was some issue with using the 'together' option in 'with' in a criteria. It behaves different than querying ...->with()->together()->....

So maybe better check the workaround in the link provided by Horacio Segura above.
0

#7 User is offline   Mantus 

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

Posted 21 July 2010 - 06:51 AM

Thank you all for the very fast help! I solved the "problem" with help of the provided links like this:

class User extends CActiveRecord {
.....

public function search() {

        $criteria->with = array('groups'=>array('select'=>'groups.name'));
       	$criteria->addSearchCondition('groups.name','1a');
        
        return new ManyManyCActiveDataProvider('User', array(
            	'criteria'=>$criteria,
        	'together'=>true,
            	'sort'=>array(
            	'defaultOrder'=>'username DESC',
            ),
        ));
...


My data provider:
class ManyManyCActiveDataProvider 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);
                }
        }
        
}


Thats working for me. Still wondering what would be the intended way to do this.
0

#8 User is offline   gallego123 

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

Posted 07 September 2010 - 03:11 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

#9 User is offline   Maxx Astral 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 71
  • Joined: 19-September 13
  • Location:Kiev

Posted 23 October 2013 - 05:04 AM

View PostMike, on 16 July 2010 - 05:58 PM, said:

Use a INNER JOIN for your group join:

$criteria->with = array('groups'=>array('select'=>'groups.name', 'joinType'=>'INNER JOIN'));


This way, the joined related tables will act like a filter.

This worked like a charm. One of the reason why i chose Yii in the first place and one of the reason why people love AR. Thanks for this answer
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