I have two tables, a project table and a staff table. The project table references the staff table via a foreign key.
A project has more than one staff members working on it. That is a person from the technical department, from the sales and from the finance department, so i put three columns each referencing the staff table.
Now i want to run a query but instead of showing id values for the different staff members on projects, i want it to show the name, which is in the staff table, but i quite can’t seem to figure out how to do this.
S0, maybe I didn’t express my question clearly…turns out, what i wanted to to was a multiple joins on the same table, using table aliases and i wanted to build a query using the query builder and get the results in an Array.
So, after days of trying and reading from different sources, i figured out how to do the query. Here is the solution.
<?php
$command = Yii::app()->db->createCommand();
$staff1 = $command->select('project.id,project.name, project.start_date, project.end_date, project.project_ref AS project_ref , technical.surname AS Technical, sales.surname AS ARM, sm.surname AS Manager')
->from('project')
->leftjoin('staff technical','project.staff1=technical.id')
->leftjoin('staff sales','project.staff2=sales.id')
->leftjoin('staff sm','project.project_manager=sm.id')
->queryAll();
$provide= new CArrayDataProvider($staff1, array(
)); ?>
Then you can later render the data from the query using CGridview.
From the perspective of database design, I think you would be better off creating a 3rd table that holds the relationship between project and staff, just so you aren’t limited to 3 staff members in case you needed to add more (and so you wouldn’t have those extra null values cluttering the project table)
My advice would be to delete the staff columns first. Then, create another table (name it project_staff or something), with the columns project_id, staff_id, and a bool value is_manager. Now when adding staff to a project, you can just insert a new record in the project_staff table with the correct corresponding staff ID and project ID.
Relationships like these are called many to many relationships. I suggest you read up on them a bit.
