Cake PHP automatically add id field to sql query

Question

I have the following code in cake php 2.1. I try to get a listing of unique string messages, all duplicates removed.

$this->loadModel('ErrorMessage');
$this->ErrorMessage->recursive = -1;
$error_messages = $this->ErrorMessage->find('list', 
  array(
    'fields' => array('DISTINCT message'),
  )
);

Which produces the following error:

Error: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISTINCT ErrorMessage. message FROM intermatte.error_messages AS `ErrorMes' at line 1

SQL Query: SELECT ErrorMessage.id, DISTINCT ErrorMessage. message FROM intermatte.error_messages AS ErrorMessage WHERE 1 = 1

The interesting part is in the query produced; Cake has included the id field automatically!

I test query alone, it produces the expected result: (list of unique strings)

SELECT DISTINCT message FROM error_messages;

Here is the create table for reference:

CREATE TABLE `error_messages` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `message` MEDIUMTEXT NULL COLLATE 'utf8_unicode_ci',
    `figure` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
    `created` DATETIME NULL DEFAULT NULL,
    `modified` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
ROW_FORMAT=COMPACT;

So the question is, why does cake include the id field automatically, and how can I tell cake not to do it?

I can of course use a query, but that is not 'nice'. ;)

score 0 · Accepted Answer · answered Jun 13 '14 at 13:57

Yes, CAKEPHP will do that, what you can do is use GROUP BY clause to overcome this issue,

$error_messages = $this->ErrorMessage->find('list', array(
                                                'fields'=>'message',
                                                'group' => 'message'));

score 0 · Answer 2 · answered Jun 13 '14 at 13:59

0

Cake will always include the id in a 'list' query unless you specify two fields in the 'fields' part of the query. If you run that query without any fields specification, by default the returned results will be in this format:

array(
    'Model.id' => 'Model.name'
)

(Unless you have overridden the default displayName in the model).

Specify two fields in your query to not include the id, e.g.

'fields' => array('ErrorMessage.message', 'ErrorMessage.message')

This will return an array in format:

array(
    'Errormessage.message' => 'ErrorMessage.message'
)

To only get unique messages, use GROUP BY:

'group' => array('ErrorMessage.message')

answered Jun 13 '14 at 13:59

gazareth

1,135
10
26

Thank you gazareth, I also found a similar post here: http://stackoverflow.com/questions/1718482/using-distinct-in-a-cakephp-find-function/1718614#1718614 – Tornseglare Jun 13 '14 at 14:13
That answer talks about using the Set class to remove duplicates. Note, it is better to use the Hash class rather than Set - it is faster and better. http://book.cakephp.org/2.0/en/core-utility-libraries/hash.html – gazareth Jun 13 '14 at 14:48

Cake PHP automatically add id field to sql query

2 Answers2