rjma30 Posted March 3, 2014 Share Posted March 3, 2014 Hello, Any recommendation for viewing/displaying data with MANY_MANY relationship? Thanks! Link to comment Share on other sites More sharing options...
Edward Posted March 4, 2014 Share Posted March 4, 2014 You shouldn't have direct many to many relationships in your code assuming you have, you first need to make an intermediary table to get one to many relationships then use the use many to many relationship configuration in relations() method in your Yii model(). Then use the relationship in the CGridView and it should work fine. Reference http://www.yiiframework.com/doc/guide/1.1/en/database.arr class Post extends CActiveRecord { ...... public function relations() { return array( 'author'=>array(self::BELONGS_TO, 'User', 'author_id'), 'categories'=>array(self::MANY_MANY, 'Category', 'tbl_post_category(post_id, category_id)'), ); } } Link to comment Share on other sites More sharing options...
rjma30 Posted March 5, 2014 Author Share Posted March 5, 2014 Hi Edward, Here's my config: public function relations() { return array( 'centers' => array(self::MANY_MANY, 'Center', 'center_has_device(device_id, center_id)'), ); public function search() { $criteria=new CDbCriteria; $criteria->with=array('centers'); return new CActiveDataProvider($this, array( 'criteria'=>$criteria, )); } CURRENT DATA: mysql> SELECT * FROM device;+-----------+------+---------------+| device_id | type | management_ip |+-----------+------+---------------+| 4 | 5 | 3232261243 || 5 | 3 | 2887713278 |+-----------+------+---------------+2 rows in set (0.00 sec)mysql> SELECT * FROM center_has_device;+-----------+-----------+| center_id | device_id |+-----------+-----------+| 1 | 5 || 2 | 5 || 3 | 4 || 3 | 5 |+-----------+-----------+4 rows in set (0.01 sec)mysql> SELECT * FROM center;+-----------+----------------------+---------------+------------+----------------------+----------------------+-----------+------------------------+-----+-----------------+------------+| center_id | location_description | center_number | country_id | site_type | comment | lock_sync | email | vip | nco_engineer_id | nco_date |+-----------+----------------------+---------------+------------+----------------------+----------------------+-----------+------------------------+-----+-----------------+------------+| 1 | Manila | 1 | 1 | Regus Standard - BOS | This is a test site! | 0 | center1@regus.com | 1 | 1 | 2014-01-01 || 2 | Ortigas | 2 | 1 | Regus Standard - BOS | Test! | 1 | john.angeles@regus.com | 0 | 1 | 2014-01-01 || 3 | Test | 34 | 3 | Regus Standard - DC | Test | 1 | test@yahoo.com | 1 | 3 | 2014-02-05 |+-----------+----------------------+---------------+------------+----------------------+----------------------+-----------+------------------------+-----+-----------------+------------+3 rows in set (0.00 sec) So I want to display all devices with center_number (all mappings as per intermediate table). My CGridView only displays all the data in my device table. It seems relational query in CActiveDataProvider does not work. I think I'm missing something. Thanks a lot! RJ Link to comment Share on other sites More sharing options...
rjma30 Posted March 5, 2014 Author Share Posted March 5, 2014 So basically I want to display below information in my CGridView: mysql> SELECT c.center_number, d.management_ip, d.type -> FROM device AS d LEFT JOIN center_has_device USING (device_id) -> LEFT JOIN center AS c USING (center_id) ORDER BY c.center_number ASC;+---------------+---------------+------+| center_number | management_ip | type |+---------------+---------------+------+| 1 | 2887713278 | 3 || 2 | 2887713278 | 3 || 34 | 3232261243 | 5 || 34 | 2887713278 | 3 |+---------------+---------------+------+4 rows in set (0.05 sec) Link to comment Share on other sites More sharing options...
Larry Posted March 10, 2014 Share Posted March 10, 2014 Sorry for not getting to this sooner. First, just to confirm, have you dumped out the values of the data provider in your search() method to confirm that the right information is being retrieved there? Link to comment Share on other sites More sharing options...
rjma30 Posted March 24, 2014 Author Share Posted March 24, 2014 No worries I was able to make it work. Thanks anyway! ======================= $criteria=new CDbCriteria; $criteria->with = 'centers'; $criteria->together = true; $criteria->compare('centers.center_number', $this->device_id, true); $criteria->compare('type',$this->type); $criteria->compare('manufacturer',$this->manufacturer); $criteria->compare('management_ip', ($this->inetAtoN($this->management_ip)) ? $this->inetAtoN($this->management_ip) : '', true); $criteria->order = 'centers.center_number ASC, type ASC'; =================== public function centersToString() { $centers = $this->centers; if($centers) { $string = ''; foreach($centers as $center) { $string .= $center->center_number . ' / '; } return substr($string,0,strlen($string)-2); } return null; } ========================== <?php $this->widget('zii.widgets.grid.CGridView', array( 'id'=>'device-grid', 'dataProvider'=>$model->search(), 'filter'=>$model, 'enableSorting' => false, 'columns'=>array( array( 'header' => 'Center', 'name' => 'device_id', 'value' => '$data->centersToString()', ), ... Link to comment Share on other sites More sharing options...
rjma30 Posted March 24, 2014 Author Share Posted March 24, 2014 No worries I was able to make it work. Thanks anyway! ======================= $criteria=new CDbCriteria; $criteria->with = 'centers'; $criteria->together = true; $criteria->compare('centers.center_number', $this->device_id, true); Link to comment Share on other sites More sharing options...
Recommended Posts