Cakephp how to create virtual field based on another virtual field

Question

I have

Contact Table : id, first_name, last_name, company_id
Company Table : id, name

I would like to create virtual field in Contact Table so it will appear "contactInCompany"

"first_name last_name - Company.name" e.g: Andre Robin - Google

How can I achieve this, I try this way but it does not work, it does not accept another virtual field as input

public $virtualFields = array(
    'companyName' => 'SELECT name FROM companies where id = Contact.company_id',
    'customerWithCompany' => "CONCAT(Contact.first_name, ' ', 
                              Contact.last_name, ' ', Contact.companyName, '')"     
    );

I also tried it this way and it does not work

'customerWithCompany' => "CONCAT(Contact.first_name, ' ', Contact.last_name, '-', 
                         SELECT name FROM companies where id = Contact.company_id)"

I need this quite often, I will use this to put in dropdown selectbox to select contact, so I would like to have the contact name shown together with the company

Solution 1

You can not do this with virtual fields as the keys in virtual fields are just the alias

Example:

class Contact extends AppModel {

  public $virtualFields = array(
      'companyName' => 'SELECT name FROM companies where id = Contact.company_id',
      'customerWithCompany' => "CONCAT(Contact.first_name, ' ', 
                                 Contact.last_name, ' ', Contact.companyName, '')"     
       );

    public function getContacts() {
      return $this->find('all', array(
                   'fields'=>array(
                            'Contact.id',
                            'Contact.first_name', 
                            'Contact.companyName', 
                            'Contact.customerWithCompany'
                           )
                    )
               );
    }

}

If the getContacts() method is called from ContactsController

The above code will form this query:

SELECT Contact.id, Contact.first_name, 
       (SELECT name FROM companies where id = Contact.company_id) AS Contact__companyName, 
       CONCAT(Contact.first_name, ' ', Contact.last_name, ' ', Contact.companyName, '') AS Contact__customerWithCompany
FROM contacts AS Contact;

This query is will not be executed on mysql as it is not possible to access the alias of 1 column for another column.

To achieve this you have to use sub queries like below: Cake php converts virtual fields name to Model__ (In below example it is Contact__)

SELECT 
SubQuery.id, 
SubQuery.first_name, 
SubQuery.Contact____companyName,
CONCAT(SubQuery.first_name, ' ', SubQuery.last_name, ' ', SubQuery.Contact____companyName, '') AS Contact__customerWithCompany 

FROM 
(SELECT Contact.id, Contact.first_name, (SELECT name FROM companies where id = Contact.company_id) AS Contact__companyName
FROM contacts AS Contact) AS SubQuery;

If you want to build sub query in cake php use buildStatement Method of DataSource

Note : Using Joins is another better solution for above query to achieve the same results without using sub queries.

With Joins:

public function getContacts() {
    $this->virtualFields['customerWithCompany'] = "CONCAT(Contact.first_name, ' ', Contact.last_name, ' ', Company.name)";
    return $this->find('all', array(
            'fields'=>array(
                'Contact.id',
                'Contact.first_name',
                'Contact.last_name',
                'Company.name',
                'Contact.customerWithCompany'
            ),
            'joins'=>array(
                array(
                    'table'=>'companies',
                    'alias'=>'Company',
                    'type'=>'LEFT',
                    'conditions'=>array(
                        'Contact.company_id = Company.id'
                    )
                )
            )
        )
    );
}

Solution 2

In your Contact model write following:

 public $belongsTo = array(
        'Company' => array(
            'className' => 'Company',
            'foreignKey' => 'company_id',
            'conditions' => '',
            'fields' => '',
            'order' => ''
        )
    );

In Company model write:

 public $hasMany = array(
            'Contact' => array(
                'className' => 'Contact',
                'foreignKey' => 'company_id',
                'dependent' => false,
            ),
  }

Now when you search for any Contact it will contain information of the concerned company too. Print the array and you will see a Company index.

PS: If I've got your question incorrect then let me know the exact problem, I might be able to help you.

Solution 3

you don't need virtualField. Use JOIN LEFT for this simple query.

$users= $this->Contact->query('SELECT CONCAT(first_name," ",last_name," - ", companies.name) AS customerWithCompany FROM users
                           LEFT JOIN companies on contact.company_id = companies.id');

to read the data:

            $userList = array(); 
            foreach ($users as $user) {
                $userList[] = $user[0]['customerWithCompany']; 
            }

            var_dump($userList);

output:

 array(
        (int) 0 =>  'Harts Jackson - Google'
        (int) 1 =>  'Emo Rock - Yahoo'
            ...
)

And also The implementation of virtualFields has a few limitations. First you cannot use virtualFields on associated models for conditions, order, or fields arrays. Doing so will generally result in an SQL error as the fields are not replaced by the ORM. This is because it difficult to estimate the depth at which an associated model might be found.

if you don't have associated models (which as I can see you have and you want to assign condition) then you have to create a constructor method in your Contact model and set your virtualFiled in a constructor. here is How to create $virtualFields

Comments

[Harts]

I don't quite understand the way to use subquery or join. could you help explain it?

[Harts]

not working. Column not found: 1054 Unknown column 'Company.name' in 'field list'

[arilia]

try setting recursive = 2 in your find

[Harts]

I would like to use this as display field. e.g: if I do $contactInCustomer = $this->Contact->find('list'). It should give me an array with the value something like "Contact.first_name Contact.last_name Company.name". I have all those belongsTo and hasMany relationship already setup. I just need to retrieve the data result return as specified, and this is 2000ish data, I don't want to do manually for loop everytime.

[arilia]

@Harts did you try my solution?

[Hrishi]

@Harts: I Specified the cake php way of using joins. You don't need to declare virtual field at the top of model, if you do not want it in every query, Create virtual fields on the fly like above example.