Retrieving data from relational tables in yii framework Active Record pattern

Question

I want to generate a report for the admin on the application. It's a small freelancing site. I'm using Yii 1.1.14 and Mysql for the database. I've 3 relational tables on the db from where I'd like to retrieve some datas. I'm showing only those fields that are relational and may be required to understand the workscope.

user table with fields: id | name | email | password | type (it's user type, either freelancer, project-Owner or administrator)

bid table with fields: id | project_id(foreign key referencing to project('id')) | freelancer_id(foreign key referencing to user('id'))

project table with fields: id | title | description | selected_bid(the id of the bid which was selected for the project).

id is always a primary key as usual.

So what I want to get is: A table on the front end with the all of those users name, email, phone number and infos about his projects in a single row for everyone. Please suggest what's the best way to achieve this? I've been trying already. Please don't post a basic tutorial from Yii's docs or Larry's one. I had been through the basic rules.

I hope I made myself clear. Thank you everyone.

Answer 1

In you user model add

class User extends CActiveRecord{
 ...
    public function relations()
    {
        // NOTE: you may need to adjust the relation name and the related
        // class name for the relations automatically generated below.
        return array(
            'bid'=>array(self::BELONGS_TO, 'Bid', array('id'=>'freelancer_id')),
            'project'=>array(self::BELONGS_TO, 'Project', array('id'=>'selected_bid'), 'through'=>'bid')
        );
    }
}

add bid model add

class Bid extends CActiveRecord
{
    ...
    public function relations()
    {
        // NOTE: you may need to adjust the relation name and the related
        // class name for the relations automatically generated below.
        return array(
            'project'=>array(self::BELONGS_TO, 'Project', array('id'=>'selected_bid'))
        );
    }
}

now you can get all the freelancer who select for project.