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.

Are the relations in your User model correctly setup? It could help if you show us the model. Also do you use cgridview for the table? — chris---, May 18 '14 at 11:18
I didn't use the cgridview for the table. Here's my User model http://pastebin.com/57DATHuJ — aumio, May 18 '14 at 19:43
I don't mean this in a negative way, but if you've been through the Yii's docs or Larry's tutorials, I find it hard to believe this situation was not covered. Please indicate where exactly you are having issues. Start by giving us some code that you put together, and explain why it is not working for you. — crafter, May 19 '14 at 06:28

score 0 · Answer 1 · answered May 18 '14 at 08:30

0

What I understood about your question is that you want all data together from 3 tables. In that case you can use Join Query in your controller.

answered May 18 '14 at 08:30

Yes. Infact from 2 tables: name, email, phone from user table. projects name, numbers of projects and amount from projects table. As user doesn't have any relation with the project table directly I've to reference the bid table in it. – aumio May 18 '14 at 08:44

score 0 · Answer 2 · answered May 18 '14 at 16:21

though imn't sure about your link model whether your foriegn key is also a primary key or not? but u can go through like......

class User extends CActiveRecord{
    ...
    public function relations()
      {
        return array(
        'bids'=>array(self::HAS_MANY,'Bid','| freelancer_id'),
        'users'=>array(
            self::HAS_MANY,'Project',array('user_id'=>'id'),**'through'=>'bids'**
          ),

        );
    }
  }


//then use as ....
// get all Projects with all corresponding users
$user = User::model()->with('users')->findAll();

score 0 · Accepted Answer · edited May 18 '14 at 17:49

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.

Also, can you show me how to display it on the front end and also the controller? I've been trying the same relations you've specified. I'm afraid I had some issue son the front end or controller to load them. — aumio, May 18 '14 at 21:31
The problem I'm having is It's showing a single project of each user. I want it to display all of those projects he's selected. I guess I've to loop it through a foreach on the projects field which isn't working a they way I want. Can you please give me something to put on? — aumio, May 19 '14 at 10:47

Retrieving data from relational tables in yii framework Active Record pattern

3 Answers3