CODEWITHSUNDEEP
mysqlcakephp
user1469742
user1469742

Reputation:

CakePHP & MySQL - Using find('all') with GROUP for one field and MAX for another field

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:

$this->Model->find('all', array(
   '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.

Upvotes: 0

Views: 800

Answers (1)

BadHorsie
BadHorsie

Reputation: 14544

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.

Upvotes: 1

Related Questions