Self-join relation, how to?

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?



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

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.

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

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(                         



                '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;