How To Refer Main Table Column In Relational Condition

Yii 1.1 General Discussion
1

Hi everyone,

I’ve got an issue in defining relational condition.

The schema is quite simple. Parent HAS_MANY Child.




parent

  id

  name

  ...


child

  id

  parent_id

  name

  birthday

  ...

I wanted to define a HAS_MANY relation of "siblings" for Child model. At first it seems quite simple to me. "You just have to find the children sharing the same parent, with the IDs different from that of the child itself."




// Child.php

public function relations()

{

	return array(

		'parent' => array(self::BELONGS_TO, 'Parent', 'parent_id'),

		'siblings' => array(self::HAS_MANY, 'Child', 

			array('parent_id' => 'parent_id'),

			'condition' => 't.id != siblings.id',

			'order' => 'siblings.birthday',

		),

	);

}

But I’ve got an error saying




SQLSTATE[42S22]: Column not found: 1054 Unknown column 't.id' in 'where clause'.

The error occured in a lazy loading scenario in which tables are not joined.

(I haven’t tested it in eager loading yet.)

Now I’m stacked. :(

Any suggestions will be appreciated.

[EDIT]

I have defined a named scope with parameters and using it for the moment.




public function siblings($parent, $self)

{

	$this->getDbCriteria()->mergeWith(array(

		'condition' => 'parent_id = :parent_id and id != :self',

		'params' => array(':parent_id' => $parent, ':self' => $self),

		'order' => 't.birthday',

	));

	return $this;

}

But can I define the "siblings" relation?

2

Hi ‘softark’

I tried your relation in another setup and it result in the following query:


 SELECT 'siblings'."InvoiceLineId" AS "t1_c0", 'siblings'."InvoiceId" AS "t1_c1", 'siblings'."TrackId" AS "t1_c2", 'siblings'."UnitPrice" AS "t1_c3", 'siblings'."Quantity" AS "t1_c4" FROM 'invoiceline' 'siblings'  WHERE (t.InvoiceLineId!=siblings.InvoiceLineId) AND ('siblings'."InvoiceId"=:ypl0)

As you can see, yii fills in the InvoiceId value directly.

So the question is, can we do the same in the relation in a way that it adjusts depending on eager or lazy loading.

3

Thanks, le_top.

Yeah, that’s it.

I got the same style of SQL as a result.




WHERE(t.id != siblings.id) AND (siblings.parent_id = :ypl0)

I’m afraid I have to give it up.

4

The only way I see is to make the relation definition dependent the fact that the alias of the table is ‘t’ or not ‘t’ - however when it is ‘t’, you’ld also need to know if this is a lazy load or not.

So a bit complex.

5

Well, before starting this thread, I had tried "id != siblings.id" instead of "t.id != siblings.id"

Of course it didn’t work, although I’ve got no error. Because “id” and “siblings.id” are the same thing in the query, syntax is OK but the result will always be none. :lol:

Yii seems to exclude the main table in lazy loading. And I think it’s reasonable. We don’t want to retrieve the data from the main table once again. So I think we have to provide the value of “id” column as a parameter, as it does for “parent_id”. That means, I’m now pretty sure, we can’t define such a relation that has a condition referring a column in the main model table in this way. :(

I might be overlooking something, though.

6

Dear Friend

That is defying the logic of bringing the main table into relationship declaration.

We can do the following to simulate a relationship.




public function afterFind()

{

        $this->metaData->addRelation('siblings',array(self::HAS_MANY,'Child','',

            'condition'=>'siblings.parent_id=:parent_id AND siblings.id!=:id',

            'params'=>array(':parent_id'=>$this->parent_id,':id'=>$this->id),

            'order' => 'siblings.birthday',

             ));


        return parent::afterFind();

}

There are two things to be noted.

1.I have put an empty string in the place of related foreignkey.This is to avoid annoying(in this scenario) condtion made by YII relating the foreing key and the primary key during configuration of relations.

  1. The second thing is putting the things in afterFind method.I am not able to define

    the relations in relations method. That may be because of the fact that when relations are configured

    the attribute values are undefined.

Regards.

7

Thank you, seenivasan.

Your code and explanations helped me a lot in understanding the matter.

8

Dear Friend

You only gave me the thread to grasp.

Till then I was also fiddling with idea of using table alias of main table.

I made some scratches on the surface.

Yii is ocean deep.

Whenever I opened the file CActiveFinder.php , I get syncopy.

I am completely ignorant of the exact things happening behind the scenes.

Anyway ignorance is bliss!

Regards.

9

:D

I’ve never dared to.