Substring + Concat on Virtual Fields in CakePHP - possible?

This seems like a simple question, but I can't get it to work. I've created a virtual field that combines a name and address:

var $virtualFields = array(
    'name_address' => 'CONCAT(Venue.name, " - ", Venue.address)'
);

Is there a way to use SUBSTR on this? (PHP or MYSQ开发者_Go百科L is fine - whichever is best/works).

I've tried this (to no avail):

'name_address' => 'SUBSTR(CONCAT(Venue.name, " - ", Venue.address),0,50)'

I've also tried SUBSTR on the individual fields within the CONCAT.

This is absolutely no problem. Your only error is, that you thought of the SUBSTRING-function in MySQL working like the one in PHP where the count starts at zero. But since this isn't PHP you have to start your counting from 1 and all will work fine.

Just use your virtualField like this and it should work just like you want:

var $virtualFields = array(
    'name_address' => 'SUBSTRING(CONCAT(Venue.name, " - ", Venue.address),1,50)'
);

Also, have a read on the SUBSTRING-function in the MySQL manual