Tags
I need to make a dropdown list of a staff. In dorpdown i need to put last name and first name. I already have a column in database name which contain first name and last name. But i need to show order last name then first name.
I tied to put CONCAT in query its working but the array result not showing properly. In this case I found that if i put a virtual fields in model that help me to fetch proper data.
In model
var $virtualFields = array('dropdown_name' => 'CONCAT(Staff.last_name, " ", Staff.first_name)');
In controller query:
// Generate list $staff_list = $this->Staff->find("list", array( "fields" => array("id", "dropdown_name"), "order" => array("Staff.name ASC"), "conditions" => array('Staff.status' => '1') ));
The above code give me the proper list of staff.
In view to generate dropdown
echo $form->input("Reports.staff_id", array( 'options' => $staff_list, 'empty' => '--Please Select--' , 'label'=> false, ));
In this way we can generate dropdown with different column in a one field.
The following I tried
$staff_list = $this->Staff->find("list", array( "fields" => array("Staff.id", "CONCAT(`Staff`.`last_name`, ' ', `Staff`.`first_name`)"), "order" => array("AgencyStaff.name ASC"), "conditions" => array('Staff.status' => '1') ));
The query is runing properly but the result array doesn’t keep the CONACT fields, it only keep the id balnk.
But I run the query in cakephp and check what actually give it the result .
$list = $this->Staff->query("SELECT `Staff`.`id` , CONCAT( `Staff`.`last_name` , ' ', `Staff`.`first_name` ) FROM `staffs` AS `Staff` WHERE `Staff`.`status` =1 ORDER BY `Staff`.`name` ASC");
Result is the following
Array ( [0] => Array ( [Staff] => Array ( [id] => 5 ) [0] => Array ( [CONCAT( `Staff`.`last_name` , ' ', `Staff`.`first_name` )] => Johnson Ashley ) ) ................ ................
Thats why cakephp couldn’t make the proper array if we use their ORM.
I tried to use CONCAT( `Staff`.`last_name` , ‘ ‘, `Staff`.`first_name` ) as `Staff`.`name`
but it still not working.
SO I choose the virtual fields concept and it working properly for me 🙂