Getting names to show in dropdown list with cakephp

cakephp, cakephp-2.2, cakephp-model, php

Solution

$this->set('projectLeaders',$this->Project->ProjectLeader->find('list'));

This will just list the records from the `project_leaders` table and most likely, as the table doesn't itself contain a name/title field (which cake would pick up automatically as the `displayField`) be like so:

array(
    1 => 1
)

Use an appropriate find

To get a meaningful list of project leaders, you need to ensure you get a join between `project_leaders` and `hr_employees` one way to do that is using the containable behavior and simply specifying which fields to use in the list:

$projectLeaders = $this->Project->ProjectLeader->find('list', array(
    'contain' => array(
        'HrEmployee'
    ),
    'fields' => array(
        'ProjectLeader.id',
        'HrEmployee.name',
    )
));
$this->set(compact('projectLeaders'));

Is your db structure appropriate for your needs?

Having a table equivalent to saying "this user is admin" may not be the best idea - it would be easier to avoid data problems, and give you simpler queries, if the table `project_leaders` was (only) a boolean field on your `hr_employees` table and `project_leader_id` pointed at the `hr_employee` table, not some other data-abstraction.

Of course I don't know your whole schema, there very well may be a good reason for having a seperate `project_leaders` table.

Alternatively - denormalize

If you add a name field to `project_leaders` - you don't need a join to know the name of a project leader or any funkiness:

alter table project_leaders add name varchar(255) after id;
update project_leaders set name = (select name from hr_employees where hr_employees.id = hr_employee_id);

In this way you can know who is the relevant project lead with one query/join instead of needing to do two joins to get an employee's name.

Problem

I want to show the names of all our project leaders in a dropdown list. The project leaders are only some of the employees that work in the company. Here are my tables: ``` project_leaders ,----,----------------, | id | hr_employee_id | |----|----------------| | 1 | 18 | '----'----------------' projects ,----,------,-------------------, | id | name | project_leader_id | |----|------|-------------------| | 1 | cake | 1 | '----'------'-------------------' hr_employees ,----,------,---------, | id | name | surname | |----|------|---------| | 18 | joe | dirt | '----'------'---------' ``` My ProjectsController looks like this: ``` public function add() { if ($this->request->is('post')) { $this->Project->create(); if ($this->Project->save($this->request->data)) { $this->_sendProjectRequestEmail($this->Project->getLastInsertID() ); $this->Session->setFlash(__('The project has been saved')); $this->redirect(array('action' => 'index')); } else { $this->Session->setFlash(__('The project could not be saved. Please, try again.')); } } $this->set('projectLeaders',$this->Project->ProjectLeader->find('list'); } ``` This only returns the id of the Project Leaders, not the name and surname. So instead of Joe Dirt, it returns 1. I've tried doing $this->Project->ProjectLeader->HrEmployee->find('list') but that lists all the employees. I've also tried specifying the fields, but it returns a unknown field error. What am I doing wrong?

Original source