Issue in saving "order" field

[astefanetti]

Hi there !
I create a mysql table with this schema:

photos

id int(11)
album_id int(11)
image varchar(255)
alt varchar(255)
title varchar(255)
description text
order int(11)
active tinyint(1)
created datetime
modified datetime

when i try to save a new record with add action baked i obtain an Exception:

Error: [PDOException] 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 'order = 1 , modified = '2015-04-05 00:14:17' WHERE id = 1' at line 1

The query log for that insert is:

Debug: BEGIN
Debug: SELECT 1 AS existing FROM albums Albums WHERE Albums.id = 1 LIMIT 1
Debug: INSERT INTO photos (album_id, image, alt, title, description, order, active, created, modified) VALUES (1, 'a', 'a', 'a', 'a', 1, 1, '2015-04-05 00:02:08', '2015-04-05 00:02:08')
Debug: ROLLBACK

I tried to solve the problem by changing the file Cake\Database\QueryCompiler.php:

protected function _buildInsertPart($parts, $query, $generator)
{
    $table = $parts[0];
    $columns = $this->_stringifyExpressions($parts[1], $generator);
    return sprintf('INSERT INTO `%s` (`%s`)', $table, implode('`, `', $columns));
} 

The same problem exists in the update.

Debug: BEGIN
Debug: UPDATE photos SET order = 1 , modified = '2015-04-05 00:14:17' WHERE id = 1
Debug: ROLLBACK

Best Regards!
Alessio

[lorenzo]

order is a reserved word in your database. Either change the column name or enable quoteIdentifiers in your database config.

[astefanetti]

Hi Lorenzo!
I did not know the existence of this setting and good reason behind!
Thank you so much for the quick reply!
Best Regards
Alessio

[zx4357685]

Why not add the quoteIdentifiers when making the SQL as CakePHP 2.x?

[ADmad]

@zx4357685 For performance gain. You can still enable identifier quoting yourself if you want it.