Not being able to get activities for follower's newsfeed in Yii?

Question

I have got two tables

Activity  

   ID | user_type    | user_id | status  
    1 | PROFESSIONAL | 15      | Hello    
    2 | VENDOR       | 15      | Hi    
    3 | PROFESSIONAL | 16      | My status    
    4 | PROFESSIONAL | 18      | Abcd    

Followers

    ID  | user_type    | user_id | follower_type | follower_id  
    1   | PROFESSIONAL | 15      | PROFESSIONAL  | 16    
    2   | VENDOR       | 15      | PROFESSIONAL  | 16    
    3   | PROFESSIONAL | 20      | PROFESSIONAL  | 17  

I want to get all the activities for PROFESSIONAL id 16 along with activities from all users to whom PROFESSIONAL id 16 is following.

So I should get the result as -

Activity  

    ID | user_type    | user_id | status  
    1  | PROFESSIONAL | 15      | Hello    
    2  | VENDOR       | 15      | Hi    
    3  | PROFESSIONAL | 16      | My status  

I'm not sure how I can get it in a single query. Also, I'm using Yii 1.1. So I want to fetch the records using Yii models. Any help in building either the MYSQL query or using Yii CActiveRecord to get desired results would be good.

Answer 1

Ok. So for now, I'm doing this as below

$criteria = new CDbCriteria;    
$criteria->select = 't.*';    
$criteria->join = 'LEFT JOIN followers AS f ON (t.user_id = f.user_id AND t.user_type = f.user_type)     
WHERE (f.follower_id='.$user_id.' AND f.follower_type = "'.$user_type.'")    
OR (t.user_id='.$user_id.' AND t.user_type = "'.$user_type.'")';    

I would love to know if there is any better performing solution available.