Non Pk Relationship With Condition

Yii 1.1 General Discussion
1

Hi,

I’ve been unable to work out how to define the following relationship between Object1 and Object2 where the join is not on the database PK and there is a condition. The objects are related like this Object1.object1_key=Object2.object1_key

However there are many groups of these objects, and that relationship is only valid if Object1.group=Object2.group.

Object2 BELONGS_TO Object1.

I tried the following in Object1, but no cigar:




'parent' => array(self::BELONGS_TO,

             'Object2', 'object1_key',

             'joinType' => 'JOIN',

             'foreignKey'=>'object1_key',

             'on' =>'object1.group=object2.group'


)

The resulting query has no join, and so the reference to object2 throws an exception. I’ve tried to find the correct syntax for the relationship with no joy. Can anyone help?

thanks

JMB

2

I am guessing everyone is thinking ‘why would you do that anyway’. So maybe if I explain, you can either see what I’m trying to do, or tell me to do it another way.

I have a version controlled structure of about 16 related objects. The db ID is used to identify the individual versions of each object, however the relationships are not between the individual versions, but between the objects.

The objects are identified by the object_key, and the object_key sequences are created by the application, but are only unique within each group, so an object that belongs to another object (e.g. object2 belongs to object 1) the relationship is described by the object_key within the domain of the group.

e.g.

Object1 has id, object1_key, group_id, data

object2 has id, object2_key, object1_key (the foreign key), group_id, data

I want to be able to say object2->relationship->data and get the data from object1, but currently I just get a random result of the first object1 from another group.

Thanks

JMB

3

Hi All,

for some reason I’m not getting any takers on this one, so I’ve had to kludge my way around it for the moment. I’d love a solution if anyone has an idea.

Is there something that’s not clear?

thanks

JMB

4

How’s the relationship between Obj1 and Obj2? Obj1 HAS MANY Obj2 and Obj2 BELONGS TO Obj1?

5

Yes, correct, its a one-to-many.

6

Try something like this (Object1 = Foo and Object2 = Bar in my example):

Foo relations:




public function relations()

{

	return array(

		'bars' => array(self::HAS_MANY, 'Bar', 'foo_key', 

			'on' => 't.group_id = bars.group_id',

		),

	);

}

Bar relations:




public function relations()

{

	return array(

		'foo' => array(self::BELONGS_TO, 'Foo', array('foo_key' => 'foo_key'),

			'on' => 't.group_id = foo.group_id',

		),

	);

}

Now say we load an instance of Bar:




$bar = Bar::model()->with('foo')->findByPk(2);

$foo = $bar->foo;


// This should output a row from the bar table with a pk of 2

echo CVarDumper::dump($bar->attributes, 10, true);


/**

 * This should output a row from the foo table if it finds a related row with a matching key (foo_key in this case)

 * and a matching group_id between the 2 tables.

 */

if ($foo !== null)

{

	echo CVarDumper::dump($foo->attributes, 10, true);

}

Going the other way should also work fine:




$foo = Foo::model()->with('bars')->findByPk(1);

$bars = $foo->bars;


echo CVarDumper::dump($foo->attributes, 10, true);


foreach ($bars as $bar)

{

	echo CVarDumper::dump($bar->attributes, 10, true);

}
7

OK, so the real case is an object called Usecase which belongs to an object called Package. The key is a field in both called package_id, and the group is project_id.

The relationship in Usecase is:


  'package' => array(self::BELONGS_TO, 'Package', array('package_id' => 'package_id'),

                        'on' => 't.project_id = package.project_id',

Using this to select $model->package

I get:

Unknown column ‘t.project_id’ in ‘where clause’. The SQL statement executed was: SELECT package.id AS t1_c0, package.package_id AS t1_c1, package.name AS t1_c2, package.stage AS t1_c3, package.number AS t1_c4, package.project_id AS t1_c5, package.release_id AS t1_c6, package.budget AS t1_c7, package.contract_amount AS t1_c8, package.extlink AS t1_c9 FROM package package WHERE (t.project_id = package.project_id) AND (package.package_id=:ypl0)

We seem to be missing the JOIN to Usecase as t.

8

That is normal, you are missing the join because you are lazy loading Package. If you look at my example, I am eager loading the related object using “with” (Bar::model()->with(‘foo’)->findByPk(1))

9

OK - so the ‘foo’ in “…->with(‘foo’)->…” refers to the relationship rather than the Model?

Anyway it seems to work, so many thanks! I’ll need to read up on the eager loading thing, as I don’t really understand it.

cheers

JMB

10

Yup, you pass in relationships there so that models and related models are returned to you in one shot.