Self-join relation, how to?

Yii 1.1 General Discussion
1

Hello all,

I have a table Topic with topic_id is primary key and author_id is foreign key.

In Topic model, I want to create a relation to get all other topics from the same Author.

In SQL, IT should be "select * from Topic where topic_id <> ??topic_id and author_id = ??author_id"

How can I declare the relation?

Thanks.

2

hi,

see this documentation, it explain different ways make relations between tables.

3

Why don’t you just use the author model?

Presumably you have added a relation in the Author model - HAS_MANY Topic

So all you would need to do to get all topics from a Topic object would be $topic->author->topics.

4

I am using this way, but it returns all topics including active topic. What I want is all other topics.

5

Thanks for your replies, I think I should use another approach: write a function to return other topics in the model. No need to declare a relation.


public function getOthers($pageIndex = 1, $pageSize = 100)

    {        

        $criteria = new CDbCriteria(array(                         

                'limit'=>$pageSize,

                'offset'=>($pageIndex-1)*$pageSize,

                'order'=>'topic_id DESC',

                'condition' => 'author_id = ' . $this->author_id . ' AND topic_id <>' . $this->topic_id,                 

        ));

        


        $dataProvider = new CActiveDataProvider(get_class($this), array(

			'criteria'=>$criteria, 'pagination'=>false

		));

        

        return $dataProvider->data;        

    }