Entries in a table related?

Yii 1.1 General Discussion
1

I have a two simple tables looking like this

contact :

id integer

name varchar

relation:

id integer

from_id integer

to_id integer

And now I want my page showing a contact to also show all related contacts. So in a sqlquery it would look something like this :

select

c.*

from contact c

inner join relation r on r.from_id = c.id

inner join contact cc on cc.id = r.to_id

Any ideas how I can configure the relations in Yii correctly for this would be great…

Thanks,

Fredrik

2 



public function relations() {

  return array(

    'related'=>array( self::MANY_MANY, 'Contact', 'relation(from_id, to_id)' ),

  );

}
3

Yes that is how I set it up and then I used this in the controller to get the data (relations = related)




               $dataProvider = new CActiveDataProvider('Contact', array(

                        'criteria' => array(

                                'with' => array('relations'),

                                'condition' => "relations.from_id = $id",

                                'together' => true,

                        ),

                        'pagination' => array(

                                'pageSize' => 1,

                        ),

                ));

But the only result I get back is the current contact I am already looking at so it looks as it is related to itself.

4

rather use:




$currentContact = Contact::model()->findByPk( $id );


$dataProvider = new CArrayProvider( $currentContact->relations );

or…

to use CActiveDataProvider you have to construct valid query (relation will not work in this case):




$dataProvider = new CActiveDataProvider('Contact', array(

  'criteria' => array(

    'join' => 'inner join relations rel ON (t.id = rel.to_id)',

    'condition' => "rel.from_id = :id",

    'params' => array( ':id'=>$id ),

  ),

you may define parametrized scope in Contact to simplyfy this:




function relatedTo( $id ) {

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

    'join' => 'inner join relations rel ON (t.id = rel.to_id)',

    'condition' => "rel.from_id = :id",

    'params' => array( ':id'=>$id ),

  ) );

  return $this;

}

and then in view/action:




$dataProvider = new CActiveDataProvider('Contact', array(

  'criteria' => array(

    'scopes'=>array( 'relatedTo'=>array( $id ) )

  )

or something like that - I am not soure about the last usage of scope in criteria but I have seen something similiar which worked :) anyway - when you have such scope you can use it also this way:




$model = Contact::model()->relatedTo( $id );

$criteria = new CDbCriteria();

$model->applyScopes( $criteria );

$dataProvider = new CActiveDataProvider('Contact', array(

  'criteria' => $criteria,

) }
5

one more thing - read this http://www.yiiframework.com/forum/index.php?/topic/5040-closed-display-log-detailed-sql-queries/ and enable query logging. this way you will know what was executed and why it was wrong :)

6

Thank you so much for your feedback redguy… if you where here (and of age) I would so owe you a beer or two right now :wink: