Relating two tables

Hi,

I have two tables Customer and CustomerCotanct. Customer table includes Company Name,Address,City,Pincode,website

CustomerContact includes Contact Person Name,Email,PhoneNo.

The relation between the Customer and CustomerContact is (1:N). Now in the Customer Model I have to write relation rule in the function relations() with Customer_id as the key.

For the give customer_id I need fetch the first row from the CustomerContact Model. How to relate this?

Below is the SQl I have written previously

SELECT B.name,  A.company, B.telephoneno , B.email,  A.address, A.city, A.pincode, A.website   FROM customer 

AS A LEFT JOIN customer_contact AS B ON ( A.id = B.id )  WHERE A.id = '$id'  

Customer relationships:

public function relations()

{

    return array(

        'customerContacts' => array(self::HAS_MANY, 'CustomerContact', 'Customer_id'),

     );

}

Then in CustomerContacts model:

public function relations()

{

    return array(

	'Customer' => array(self::BELONGS_TO, 'Customer', 'Customer_id'),

    );

}

If you setup relationships like this, you would be able to access your as(lazy loading):

$customer = Customer::model()->findByPk($customer_id);

//first contact row

$firstCustomerContactRow =  $customer->customerContacts[0];

Thanks Ivica,

I tried to get the rows in one statement by using the below code…

$this->_model = Customer::model()->with('customerContacts')->findByPk('CS0002');

but I got the error saying CDbCommand failed to execute the SQL statement,because it is taking no column in customerContacts.``…

LEFT OUTER JOIN `contact` `customerContacts` ON (`t`.`customer_id`=`customerContacts`.``).

And also how can we change the column names…It is taking default values like t0_c0,t1_c0… to name the columns.

Hi Ivica, I got ‘Invalid argument supplied for foreach()’

Controller

$this->_model = Customer::model()->findByPk('key002');

$firstCustomerContactRow =  $customer->contact[0];

Customer Model relation

'contact' => array(self::HAS_MANY, 'CustomerContact', 'customer_id'),

CustomerContact model relation

'customer' => array(self::BELONGS_TO, 'Customer', 'customer_id'),

i’m not pretty sure but i think it’s because u call $customer but u set

$this->_model = Customer::model()->findByPk('key002');

maybe u can change it with

$customer = Customer::model()->findByPk('key002');

I am able to retrieve the data from the model separately by using the below code… since I am not good at relating the tables in YII…

Customer::model()->findByPk('KEY0002')

CustomerContact::model()->findByAttributes(array('customer_id'=>'KEY0002'))

I want to relate the model customer and customercontact but I am getting php warnings.

 -----------       -----------------

| customer  |     | customercontact |

 -----------        ----------------

|  id       | PK  |     Name        |

| company   |     |    emailid      | 

| website   |     |      id         |  --- Foreign key

 ------------      ----------------


//Customer Model relations

'contact' => array(self::HAS_MANY, 'CustomerContact', 'customer_id')


//Customercontact  Model relations

'customer' => array(self::BELONGS_TO, 'Customer', 'customer_id'),


//Controller action

Customer::model()->with('contact')->findByPk('key');

This is solved. I was getting error because there was no primary key defined…