Active Record Relation and CGridview

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? :(

Topic moved.

this is a master-detail relationship: a business has many contacts

in business/view/id/1 you can create a grid with all contacts belonging to that business

if you want a (main)-contact to one business, you have to define another column as reference in your business table

Thank you for the reply. So is there any other way to simulate a query similar to the following:


SELECT `t`.* , `businesscontacts`.`contact_firstname` AS `t1_c2`, `businesscontacts`.`contact_lastname` AS `t1_c3`, `businesscontacts`.`contact_id` AS `t1_c0` FROM `tbl_business` `t` LEFT OUTER JOIN `business_contact` `businesscontacts` ON (`businesscontacts`.`business_id`=`t`.`business_id`)

Also, I see that my condition is not drastically different from the author-post relation example given in Address Relation example in official Yii website. If I define business_id in businesscontacts table as a Foreign Key, will that work?

Just had the same problem, this is the solution that worked for me.

In the CGridView params array instead of just ‘contact_firstname’ try:

array(           


        'name'=>'Name', 


        'value'=>'$data->contactbusiness->contact_firstname',


    ),

This worked great for me. Play around with it a bit if it doesn’t work at first (I may have misunderstood your model)

(this is where I got my solution http://www.yiiframework.com/wiki/278/cgridview-render-customized-complex-datacolumns/ )

Sorry, I think I wasn’t clear enough. My problem was that a single business row would again be linked with another contact row. I later figured out that the contact row was being assigned in an array.

I tried


'value' => '$data->contactbusiness["contact_firstname"]'

It did not however work. And out of nowhere, I tried this crazy idea which worked


'value' => '$data->contactbusiness[0]["contact_firstname"]'

Making it a bit more fancy, I eventually resorted using:




array(

        'name' => 'contact_firstname',

        'header' => 'First Name',        

        'value' => 'array_shift($data->businesscontacts)->contact_firstname'

)



Hence I learnt that database mapping is a hard nut to crack! I achieved what I required, but I know its not how it is actually done. Waiting to stumble across some knowledge!

This solution looks like it will only work if there is one “child” businesscontacts…since it looks at the first businesscontacts in the array (businesscontacts[0]), then skips to the next $data element…did you ever get it to cycle through all children? I ask because I’m solving a similar problem now, where I’d like to be able to return all children models to the dataprovider…

data0->businesscontacts[0]

data0->businesscontacts[1]

data0->businesscontacts[2]

data1->businesscontacts[0]

data1->businesscontacts[1]…etc