rjma30 0 Posted March 3, 2014 Report Share Posted March 3, 2014 Hello, Any recommendation for viewing/displaying data with MANY_MANY relationship? Thanks! Quote Link to post Share on other sites
Edward 108 Posted March 4, 2014 Report 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)'), ); } } Quote Link to post Share on other sites
rjma30 0 Posted March 5, 2014 Author Report 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 Quote Link to post Share on other sites
rjma30 0 Posted March 5, 2014 Author Report 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) Quote Link to post Share on other sites
Larry 428 Posted March 10, 2014 Report 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? Quote Link to post Share on other sites
rjma30 0 Posted March 24, 2014 Author Report 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()', ), ... Quote Link to post Share on other sites
rjma30 0 Posted March 24, 2014 Author Report 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); Quote Link to post Share on other sites
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.