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'. ;)

Rikesh · Accepted Answer · 2014-06-13 13:57:50Z

0

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'));

answered Jun 13, 2014 at 13:57

Rikesh

26.5k14 gold badges82 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

gazareth · Accepted Answer · 2014-06-13 13:59:13Z

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, 2014 at 13:59

gazareth

1,15412 silver badges26 bronze badges

2 Comments

Tornseglare Over a year ago

Thank you gazareth, I also found a similar post here: stackoverflow.com/questions/1718482/…

gazareth Over a year ago

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. book.cakephp.org/2.0/en/core-utility-libraries/hash.html

Collectives™ on Stack Overflow

Cake PHP automatically add id field to sql query

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related