Forced Join In Relation With Custom Foreign Key

Yii 1.1 General Discussion
1

I need to connect records whith same user. same attempt_id and different steps.

I write in relations():




'prevStep' => array(

	self::HAS_ONE, 

	'Step', 

	array('user_id' => 'user_id'), 

	'on' => 'prevStep.attempt_id = t.attempt_id AND prevStep.step = t.step-1'

),

This works in constructions including ‘with’ call. I.e. Step::model()->with(‘prevStep’)->findAll();

But when I try to get prevStep as a property, the query is generated without JOIN clause. And, of course, MySQL cannot find the table with alias t while executing the query.

I’ve found a solution but it’s wrong because it includes the table as a through:

Now relations() looks like:




'prevStep' => array(

	self::HAS_ONE, 

	'Step', 

	array('user_id' => 'user_id'), 

	'on' => 'prevStep.attempt_id = t.attempt_id AND prevStep.step = t.step-1',

	'through' => 'prevForced',

),

'prevForced' => array(

	self::HAS_ONE,

	'Step',

	'id',

	'joinType' => 'INNER JOIN',

),

In this case the problem is solved, but there are two issues:

  1. Another join to the same table.

  2. Example includes only one realtion, in my code i have 10 of them and i need to create such proxy join for every relation.

So the question is: is there any other way to get what I’ve done? If there’s not maybe some option could be added to relation that defines forced join?

2

If you use the relations in both ways create 2 relations, it looks odd but in the case of $model->prev you have to check $this->step instead of set an on condition.

3

You suggest me to write like this:




'prevStep' => array(

        self::HAS_ONE, 

        'Step', 

        array('user_id' => 'user_id'), 

        'on' => 'prevStep.attempt_id = t.attempt_id AND prevStep.step = t.step-1'

),

'nextStep' => array(

        self::HAS_ONE, 

        'Step', 

        array('user_id' => 'user_id'), 

        'on' => 'prevStep.attempt_id = t.attempt_id AND prevStep.step = t.step+1'

),

?

4

Something like that:




'prevStep' => array(

        self::HAS_ONE, 

        'Step', 

        array('user_id' => 'user_id'), 

        'on' => 'prevStep.attempt_id = t.attempt_id AND prevStep.step = t.step-1'

),

'prevStep2' => array(

        self::HAS_ONE, 

        'Step', 

        array('user_id' => 'user_id'), 

        'condition' => 'prevStep2.attempt_id = :attempt_id AND prevStep2.step = :step',

        'params'=>array(':attempt_id'=>$this->attempt_id, ':step'=>$this->step)

),
5

Not an elegant solution IMHO. I’d prefer to write different queries for lazy load and find with related models for a single relation. The substance should have only one name in every possible cases. And of course when i write the code i wouldn’t like to think about which exactly name of the currently used substance i should use.

So I’d propose to add options for lazy loaded variation of relation that could be substituted with the original ones when not specified.