cakephp- CONCAT in query, Virtual Fields and make a drop down

Tags

CakePHP, CONCAT, virtual fields

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 🙂