Cgridview For Many_Many Relationship

[rjma30 0]

Hello,

 

Any recommendation for viewing/displaying data with MANY_MANY relationship?

 

Thanks!

[Edward 108]

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)'),
        );
    }
}

[rjma30 0]

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

[rjma30 0]

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)
 

[Larry 428]

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?

[rjma30 0]

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()',

    ),

 

...

[rjma30 0]

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);