beginner question with MANY_MANY and CDbCriteria

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!

look this, maybe help you

http://www.yiiframework.com/forum/index.php?/topic/9714-sort-user-defined-column-in-cgridview/

http://www.yiiframework.com/forum/index.php?/topic/7420-undestanding-relational-queries-with-cactivedataprovider/

exist a request for this feature

http://www.yiiframework.com/forum/index.php?/topic/9571-cactivedataprovider-together/

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

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.

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.

look this (since 1.1.4)

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

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