CGridview and Yii Active Record Relation

Question

I have two tables tbl_business and business_contacts of the following structure:

  tbl_business
  ---
  business_id (PK)
  othercolumns

and

  business_contacts
  ---
  contact_id (PK)
  business_id 
  othercolumns

The scenario is that one business row has many contacts. I am using cGridview using gii's CRUD generator and needed to display firstname and lastname from business_contacts (one of multiple possible rows in the table) for each tbl_business record.

As far as I understand, I've updated the relation function in tbl_business's model as:

  'businesscontacts' => array(self::HAS_MANY,'BusinessContact','business_id','select' => 'contact_firstname, contact_lastname')

and for the same, a contact relation is defined in the business_contacts' model as:

  'contactbusiness' => array(self::BELONGS_TO,'BusinessContact','business_id')

I expected that would work for pulling related records so that I can have something in the grid like, business_id, contact_firstname, contact_lastname , ... otherbusinesstablecolumns .. but I'm only getting blank values under firstname and lastname .. could someone please help me understand the error? :(

Answer 1

So you are trying to display a table of Businesses (tbl_business) using CGridView? And in each Business's row you want to list multiple Contacts (business_contacts)?

CGridView does not support displaying HAS_MANY relations by default. CGridView makes it easy to list which Business a Contact BELONGS_TO (i.e. you can use a column name like contactbusiness.business_id), but not all of the Contacts that are in a business.

You can do it yourself though, by customizing a CDataColumn. (Note: this will not allow you to sort and filter the column, just view. You'll have to do a lot more work in to get those working.)

First, in your Business model, add a method like this to print out all of the contacts:

public function contactsToString() {
  $return = '';
  foreach ($this->businesscontacts as $contact) {
    $return .= $contact->contact_firstname.' '.$contact->contact_firstname.'<br />';
  }
  return $return;
}

(EDIT: Or do this to print out just the first contact):

public function contactsToString() {
  if($firstContact = array_shift($this->businesscontacts)) {
    return $firstContact->contact_firstname.' '.$firstContact->contact_firstname;
  }
  return '';
}

Then make a new column in your grid and fill it with this data like so:

<?php $this->widget('zii.widgets.grid.CGridView', array(
  'id'=>'business-grid',
  'dataProvider'=>$model->yourDataProviderFunction(),
  'columns'=>
    'business_id',
    array(
      'header'=>'Business Contacts', // give new column a header
      'type'=>'HTML', // set it to manual HTML
      'value'=>'$data->contactsToString()' // here is where you call the new function
    ),
    // other columns
)); ?>

EDIT2: Yet another way of doing this, if you just want to print out ONE of a HAS_MANY relation, would be to set up a new (additional) HAS_ONE relation for the same table:

public function relations()
{
  return array(
    'businesscontacts' => array(self::HAS_MANY,'BusinessContact','business_id','select' => 'contact_firstname, contact_lastname') // original
    'firstBusinesscontact' => array(self::HAS_ONE, 'BusinessContact', 'business_id'), // the new relation
  );
}

Then, in your CGridView you can just set up a column like so:

'columns'=>array(
  'firstBusinesscontact.contact_firstname',
),

Answer 2

Getting only the first contact could be achieved like this also:

$this->widget('zii.widgets.grid.CGridView', array(
  'id'=>'business-grid',
  'dataProvider'=>$model->yourDataProviderFunction(),
  'columns'=>
    'business_id',
     //....
     array(
       'name' => 'contacts.contact_firstname', 
       'value' => '$data->contacts[0]->contact_firstname',  // <------------------------
       'type' => 'raw' 
     );
     //....
),