Sql Query Builder

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.

I use query builder to generate the queries.

See images to see what i have in the tables.

Any help will be highly appreciated.


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.


	$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')


	->leftjoin('staff technical','project.staff1=technical.id')

	->leftjoin('staff sales','project.staff2=sales.id')

	->leftjoin('staff sm','project.project_manager=sm.id')


	$provide= new CArrayDataProvider($staff1, array(


	)); ?>

Then you can later render the data from the query using CGridview.

Hope one day this helps someone. ;D

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)

Sounds like a neater way of doing things…

How exactly would i set up the tables and the relationships?

I have limited knowledge when it comes to database design…I would say, i am only comfortable with the basics. ;D

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.


I am definitely going to read up on them. I will let you know on how this implementation goes.

Thank again.