Yii Framework Forum: Help:I have sql but I don't know how to use criteria - Yii Framework Forum

Jump to content

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

Help:I have sql but I don't know how to use criteria Rate Topic: -----

#1 User is offline   holala 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 74
  • Joined: 23-December 09

Posted 03 March 2010 - 01:05 AM

I have 2 models,Group and User,in group,the relation is:
public function relations()
{
   return array(
      'users' => array(self::MANY_MANY, 'User', 'groupuser(group_id, user_id)'),
   );
}

and i know the sql,but i don't know how to use criteria achieve it,please help,thanks!
SELECT *
FROM user AS a
INNER JOIN groupuser AS b ON b.user_id = a.id
AND b.group_id = 2

0

#2 User is offline   rohit 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 68
  • Joined: 08-July 09
  • Location:India

Posted 03 March 2010 - 02:25 AM

First create an instance of CDbCriteria
$criteria=new CDbCriteria();

by default select has * means all columns selection so no need to define
$criteria->select

As you have MANY_MANY relationship between User and Group Model.
Following may work
$criteria->condition='group_id:group_id';
$criteria->params=array(':group_id'=>2);
$model=User::model()->with('users')->findAll($criteria)

I think relations() needs modification! In which model you have put this relation?
0

#3 User is offline   aztech 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 206
  • Joined: 12-December 08
  • Location:Poland

Posted 03 March 2010 - 05:10 AM

If you want to have one SQL query don't forget to use method together()
For more information please look at note in guide.

$criteria->condition='group_id:group_id';
$criteria->params=array(':group_id'=>2);
$model=User::model()->with('users')->together()->findAll($criteria)


If you want to see how is you query looking like add following to application configuration
  /* logging feature */
  'components'=>array(
        'log'=>array(
            'class'=>'CLogRouter',
            'routes'=>array(
                array(
                    'class'=>'CFileLogRoute',
                    'levels'=>'error, warning',
                ),
                array(
                    'class'=>'CWebLogRoute',
                ),
            ),
        ),
        'db'=>array(
            'connectionString' => 'mysql:host=localhost;dbname=xxx',
            'username' => 'xxx',
            'password' => 'xxx',
            'charset' => 'utf8',
            'enableParamLogging' => true,  /* turn on logging sql statements */
        ),
    )

I'm not complete idiot... some parts are missing!
0

#4 User is offline   holala 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 74
  • Joined: 23-December 09

Posted 04 March 2010 - 12:44 AM

Thank you all.
but my model User has not relation 'users','users' is in model Group.

there is relation in model User:

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

0

#5 User is offline   aztech 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 206
  • Joined: 12-December 08
  • Location:Poland

Posted 04 March 2010 - 06:19 AM

So my example would change into following
$model=User::model()->with('groups')->together()->findAll($criteria)

I'm not complete idiot... some parts are missing!
0

#6 User is offline   rohit 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 68
  • Joined: 08-July 09
  • Location:India

Posted 04 March 2010 - 06:26 AM

View Postholala, on 04 March 2010 - 12:44 AM, said:

Thank you all.
but my model User has not relation 'users','users' is in model Group.

there is relation in model User:

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



See, we are executing model() method on a model(table) from which we need to fetch results. So thus User::model()
with() tells which relation to use. so User needs a relation to Group model, use that name instead. so it will be 'groups'

Thus finally it should become,
$model=User::model()->with('groups')->findAll($criteria)
as aztech suggested.
0

#7 User is offline   josean 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 1
  • Joined: 04-March 10
  • Location:Spain

Posted 04 March 2010 - 05:02 PM

Could I create a DataProvider based on a MANY_MANY relation?

I want to modify the actionIndex() of a controller in order to list elements filtered by a criteria determined by a N:N relation

Following with the example, what If I want to list just the users belonging to a given group?
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