Join 2 Table User And Authassignment And Want To Diplay Both Tables Data

Yii 1.1 General Discussion
1

table user:

id, username,firstname,lastname,psw,etc…

authassignment table:

itemname ,userid(foreign key of user)

i want to display username ,firstname, itemname in user grid view.

this is argent plz help me soon thanks

chamara

--------------this is my user relation

public function relations() {

  // NOTE: you may need to adjust the relation name and the related


  // class name for the relations automatically generated below.


  return array(


      'authassignment'=>array(self::BELONGS_TO, 'authassignment', 'id'), 


      'authitems' => array(self::MANY_MANY, 'Authitem', 'authassignment(userid, itemname)'),


  );

}

-------------this is authassinment relation

public function relations()

{


	// NOTE: you may need to adjust the relation name and the related


	// class name for the relations automatically generated below.


	return array(


      'itemname' => array(self::BELONGS_TO, 'Authitem', 'itemname'),


      'user' => array(self::BELONGS_TO, 'User', 'userid'),


	);


}

---------user grid

$this->widget(‘bootstrap.widgets.TbGridView’, array(

'id' => 'user-grid',


'type' => 'striped bordered condensed',


'dataProvider' => $model->search(),


'filter' => $model,


'pager' => array(


    'class' => 'CLinkPager',


),


'template' => "{items}{summary}{pager}",


'enablePagination' => true,


'columns' => array(


    array('name' => 'id', 'header' => '#'),


    array('name' => 'first_name', 'header' => 'First name'),


    array('name' => 'username', 'header' => 'User name'),


    array('name' => 'email', 'header' => 'E-mail'),


    array('name' => 'address', 'header' => 'Address'),


    array('name' => 'tele', 'header' => 'telephone No'),


    array('name' => 'role', 'header' => 'role', 'type' => 'raw', 'value' => '$data->itemname', 'sortable' => true,),


    array(


        'class' => 'bootstrap.widgets.TbButtonColumn',


        'template' => '{view}',


        'htmlOptions' => array('style' => 'width: 50px'),


    ),


),

));

---------user search

  $criteria = new CDbCriteria;


  $criteria->select = 't.id,a.itemname,t.first_name,t.username,t.email,t.address,t.tele';


  $criteria->join = 'LEFT JOIN authassignment a ON t.id = a.userid';


//  $criteria->group = 'a.userid ASC'; 





  $criteria->compare('id', $this->id);


  $criteria->compare('first_name', $this->first_name, true);


  $criteria->compare('middle_name', $this->middle_name, true);


  $criteria->compare('last_name', $this->last_name, true);


  $criteria->compare('username', $this->username, true);


  $criteria->compare('password', $this->password, true);


  $criteria->compare('email', $this->email, true);


  $criteria->compare('picture', $this->picture, true);


  $criteria->compare('birthday', $this->birthday, true);


  $criteria->compare('country', $this->country, true);


  $criteria->compare('address', $this->address, true);


  $criteria->compare('tele', $this->tele, true);


  $criteria->compare('fax', $this->fax, true);


  $criteria->compare('web', $this->web, true);


  $criteria->compare('status', $this->status, true);


  $criteria->compare('authitems.name', $this->role, true);


  //print_r($criteria);exit;


  return new CActiveDataProvider($this, array(


              'pagination' => array(


                  'pageSize' => 10,


              ),


              'criteria' => $criteria,


          ));

}

2

http://www.yiiframework.com/wiki/385/displaying-sorting-and-filtering-hasmany-manymany-relations-in-cgridview/

3
4

in this image there is a column call roles,i want to show itemname on that

5

this is my authasignment table

i want to view it in a user grid as this

name – ---- -itemname

chamara -----supperadin,admin,user

nuwan----------admin

assign roles can u any one help me

6

set a method in the model




array('name' => 'role', 'header' => 'role', 'type' => 'raw', 'value' => '$data->itemname', 'sortable' => true,),
7

like this chamara




public function ShowAllRolesUsers($id) {


$roles = Rights::getAssignedRoles($id);

$users = array();

foreach ($roles as $role)

$users[] = $role->name;

return implode(",", $users);

}


//grid view




array('name' => 'role', 'header' => 'Assigned role','value'=>'$data->ShowAllRolesUsers($data->id)'),

what is wrong in this way?

8

thanks for ur reply rajith

this way is ok but i want to take it through join query with out write a separate function

9

OK. CHECK FOR $criteria options .

you will get it.

10

check this link

http://www.yiiframework.com/wiki/117/using-standard-filters-in-cgridview-custom-fields/

11

This is also used to solve using relations

http://www.yiiframework.com/forum/index.php/topic/8148-cgridview-filter-with-relations/