Problem understanding relations and creating joins

Yii 1.1 General Discussion
1

Hi everyone,

This is the first framework I really look into and use. So far I’ve got most of my app running smoothly thanks to the documentation and threads on the forum but I’ve got stuck whilst trying to get data from multiple tables.

The site I’m working on is a car site, here’s a simplified example of the db tables I’m having trouble with:


lpa_vehicule :

id, marque, ...


lpa_marque :

id, nom_marque, ...

marque in the lpa_vehicule table refers to the PK id in the other table. I was reading into the relations() as I thought that was what I needed now I’m not so sure. I’ve got it so it’s sort of working however it always queries the vehicule id against the other tables, I’m trying to figure out how to use marque on the lpa_marque table.

Maybe I’ve completely misunderstood this part, this is what I’ve done so far with the relations:


public function relations()

{

	// NOTE: you may need to adjust the relation name and the related

	// class name for the relations automatically generated below.

	return array(

		'marque_info' => array(self::HAS_ONE, 'Marque', 'id'),

	);

}

In the controller:


$this->_model=Vehicule::model()->with('Marque')->findbyPk($_GET['id']);

I’ve tried playing around with the foreignKey, etc. but I can’t work out how to change the actual key used from the primary table. Basically the end of the query looks like this:


LEFT OUTER JOIN `lpa_marque` `Marque` ON (`Marque`.`id`=`t`.`id`)

And I’m trying to acheive this:


LEFT OUTER JOIN `lpa_marque` `Marque` ON (`Marque`.`id`=`t`.`marque`)

I would really appreciate any help with this, maybe I’m trying to do this the wrong way if so please point me in the right direction :)

I’ve already read this page 10 times: http://www.yiiframework.com/doc/guide/database.arr

Thanks in advance!

2

Use FK here




'marque_info' => array(self::HAS_ONE, 'Marque', 'marque'),

and the relationship name here




$this->_model=Vehicule::model()->with('marque_info')->findbyPk($_GET['id']);

(Not tested, though. I’m still not used to define HAS_ONE. Being a special case of HAS_MANY I would expect the HAS_ONE FK to reside in the other table)

/Tommy

3

Try this:




class Vehicule extends CActiveRecord

{

	public function relations()

	{

		return array(

			'marque_info' => array(self::HAS_ONE, 'lpa_marque', 'marque'),

						// self::HAS_ONE	has a 1:1 relation with the table

						// 'lpa_marque'		the table for the relationship

						// 'marque'		the vehicule field which points to the

						//			record in the relationship table

		);

	}

}
4

This doesn’t work because lpa_marque doesn’t exist as a model, the model for that table is “Marque”.

Here’s some other ways I’ve tested:


'marque_info' => array(self::HAS_ONE, 'Marque', 'id'),


'marque_info' => array(self::HAS_ONE, 'Marque', 'marque'),


'marque_info' => array(self::HAS_ONE, 'Marque', 'id',

				'joinType'=>'JOIN',

				'foreignKey'=>'id'),

All the above give errors or the same result. Am I trying to do it the wrong way? After looking into the database guide some more it looks like I may need to do something like this:


'categories'=>array(self::MANY_MANY, 'Category',

                'tbl_post_category(post_id, category_id)'),

Instead of a HAS_ONE use MANY_MANY and create a table lpa_marque_relation(vehicule_id,marque_id)? It just seems pointless creating another table when it can be done so easily with a simple JOIN.

Thanks for your help.

[b]

EDIT:[/b] I’ve just tried out the MANY_MANY and that works OK but is there not a way without creating the extra table, etc?

5

After trying some more it seems that MANY_MANY isn’t what I need…

Basically this is what I want to acheive:


SELECT t.*,marque.nome_marque FROM lpa_vehicule t

JOIN lpa_marque marque ON marque.id=t.marque

It’s a very simple query but from what I can tell this can’t be done through relations?

I’ve nearly got something working with CDbCriteria but I can’t access the extra data from the query only stuff from the model ‘Vehicule’:


$crit = new CDbCriteria;

$crit->select = 't.*,Marque.nom_marque';

$crit->condition = 't.id=:id';

$crit->join = 'JOIN {{marque}} Marque ON Marque.id=t.marque';

$crit->params = array(':id'=>$_GET['id']);

$this->_model = Vehicule::model()->find($crit);


// no nom_marque here

var_dump($this->_model);

If someone can tell me how to access nom_marque here I’d be very grateful, my site development was going so smoothly until I hit this two days ago and now I’m just going round in circles… :(

6

I’ve solved the problem, probably not the right way around but it works for me and I can now get back to finishing the site ;)

In the end I added the missing functionality I was looking for, it doesn’t seem to have broken anything but it’s probably not the recommended way as it means changing a few lines in the framework…

Basically now in the relations I can select the model key I want the join on like this:


public function relations()

{

	// NOTE the 'modelKey'

	return array(

		'marque_info' => array(self::HAS_ONE, 'Marque', 'id', 'modelKey' => 'marque'),

	);

}

So now instead of automatically selecting the PK I can define a different one. Someone will probably point out that I’m doing this the wrong way, if so please correct me. If anyones interested in seeing the changes just ask, it was only a couple of lines.

7

@MCréatif do you mind posting the changes? thanks

8

Did this ever make it in? It sounds exactly like what I need