concat field

[taroumaru]

for Concat field,

I modify Component. I do not know if this is best...

// check for ORDER BY in GET request
        if(isset($this->controller->request->query) && isset($this->controller->request->query['iSortCol_0'])){
            $orderBy = $this->getOrderByStatements();
            if(!empty($orderBy)){
                if( substr($orderBy,0,2) == "0." ){ 
                    $orderBy = substr($orderBy,2);
                }
                $this->controller->paginate = array_merge($this->controller->paginate, array('order'=>$orderBy));
            }
        }

[cnizzardini]

I'll run your code and see how it works. What was the reasoning for this?

[taroumaru]

This is my paginate code.

$this->paginate = array(
            'fields' => array(
                'Contact.id',
                'ContactType.name',
                //'Contact.name_reading',
                'concat(Contact.name," ",Contact.email) as name',
            ),
    ....

When I sort with "0.name", Without my code, MySQL return error...

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column '0.name' in 'order clause'

then, I modify your code.

[cnizzardini]

Would the following code work better for you:

$this->User->virtualFields = array(
    'full_name'=>'CONCAT(User.first_name," ",User.last_name)'
);
$this->paginate = array(
    'fields' => array('User.full_name','User.username', 'User.first_name', 'User.last_name', 'UserGroup.name','Location.name','User.id'),
    'conditions' => array(
        'active'=>1
    ),
    'order' => 'User.full_name ASC'
);

Cake lets you add virtual fields on the fly without adding them permenantly to the Models attributes. This is useful in scenarios where you do not always need a concat. This allows you to access the virtual field as if it were a permanent part of the resulting data.

edit

I am now noticing that you missed a part of the documentation. Check the documentation for the part where it says "With CONCAT Fields. Note once a CONCAT is used we must tell the component the order the fields should be in."

So you would need the following

$this->DataTable->fields = array('Contact.id', 'ContactType.name', '0.name');

I still like the virtualFields way of doing this better.

[taroumaru]

When I use this virtualfields.

$this->User->virtualFields = array(
    'full_name'=>'CONCAT(User.first_name," ",User.last_name)'
);

I need this setting.

$this->DataTable->fields = array('User.id', 'User.name', 'User.full_name');

'User.full_name' --> work '0.full_name' --> not work correctly.

Please advice me.