Take the 2-minute tour ×

I have a MySQL table with 3 columns (thread_id, message_id, message). Along the lines of the solution found under the "Example using GROUP BY" in this link, I want my query to GROUP BY thread_id, but return the line of of the highest message_id (instead of default lowest) for each thread_id. I then want a nicely formatted array with lines/items just like you get for less complex find operations in CakePHP along the lines of $array[index]['Model']['field']. Using the following CakePHP syntax:

   'fields' => array('MAX(Model.message_id) as message_id', 'Model.thread_id', 'Model.message'),
   'group => 'Model.thread_id'

Now, unfortunately I am not getting that nicely formatted array. Instead I get an array which looks something like:

Array ( [0] => Array ( [0] => Array ( [message_id] => wanted/correct_message_id ) [Model] => Array ( [message] => Message from lowest/unwanted message_id line. [thread_id] => Key from lowest/unwanted message_id line))

Why does the message_id not get hooked onto the [Model] part of the array and why does CakePHP fetch the lowest message_id line and put the message and thread_id into the [Model] part of the array without the message_id column?

I want all thre columns in the [Model] part of the array and I want that line to be the highest message_id for that thread_id per my initial description. Hope this question makes sense.

share|improve this question
To get it into the model key, set it up as a virtualField –  tigrang Oct 18 '12 at 1:44
Ok. Will the desired line (highest) then be fetched into the model key? –  alieninlondon Oct 18 '12 at 12:48

1 Answer 1

Virtual fields are really useful for this kind of thing.

class MyModel extends AppModel {

    public $virtualFields = array(
        'max_message_id' => 'MAX(MyModel.message_id)'


You can now use max_message_id as if it were a normal field in your table, so you can add it to your find operations.

share|improve this answer
Ok interesting. Will the SQL query be different to my example above? In this case, does the virtual field make the default query always finding the highest message_id line instead of lowest (like I wanted)? –  alieninlondon Oct 18 '12 at 17:59
All $virtualFields does is allow you to write custom SQL like you've done, but have the result pulled into the associative array as though it's a real table field. Otherwise, it should behave in exactly the same way. You can also set the value of your model's $virtualFields before calling find() if you don't always want to be using it. –  Ben Graham Oct 18 '12 at 22:29
@user1469742 The virtual field will not affect your normal queries, so if you want to find just the normal message_id you find message_id, but if you want to find the maximum message ID, you use max_message_id. –  BadHorsie Nov 6 '12 at 13:50

Your Answer


