CODEWITHSUNDEEP
mysqlcakephp-3.0
Eduan Lenine
Eduan Lenine

Reputation: 150

CakePHP 3.0: Mysql Syntax Error caused by a class named Group

I just created a table called group and generated the skeleton files referenced to this table.

I realized that this name enter in conflict with MySQL Reserved Words, because cakephp3.0 generates queries like that:

SELECT 
     Group.group_id AS `Group__group_id`, 
     Group.name AS `Group__name`, 
     Group.created_at AS `Group__created_at` 
FROM 
     group Group 
LIMIT 
     20 OFFSET 0

That throws this 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 'group Group 
LIMIT 20 OFFSET 0' at line 1

Is there a way to avoid this kind of error?

Upvotes: 1

Views: 461

Answers (3)

Rafael
Rafael

Reputation: 131

I'm using CakePHP 4, and to solve this problem, I just added quoteIdentifiers => true, inside config -> app_local -> datasources

Datasources' => [
    'default' => [
        'quoteIdentifiers' => true,
        'host' => '127.0.0.1',

quoteIdentifiers Set to true if you are using reserved words or special characters in your table or column names. Enabling this setting will result in queries built using the Query Builder having identifiers quoted when creating SQL. It should be noted that this decreases performance because each query needs to be traversed and manipulated before being executed.

See more in: https://book.cakephp.org/4/en/orm/database-basics.html

Upvotes: 1

Ilie Pandia
Ilie Pandia

Reputation: 1839

Indeed you can enable the quoteItendifiers but that comes with a performance hit as it says in the comment above it.

I use a different solution to this issue, by customizing the Table class for the problematic db_table like so:

Note the table alias being renamed and also the table name I have escaped manually

class GroupTable extends Table
{
    public function initialize(array $config)
    {
        parent::initialize($config); // TODO: Change the autogenerated stub
        $this->setAlias('MyGroup');
        $this->setTable("`group`");
    }

}

This will generate a query looking like this:

SELECT 
  MyGroup.id AS `MyGroup__id`, 
  MyGroup.filed1 AS `MyGroup__filed1` 
FROM 
   `group` MyGroup

With CakePHP 3.6 $Group->find()->all() runs successfully.

Upvotes: 1

Eduan Lenine
Eduan Lenine

Reputation: 150

I just found the solution. The solution is to change the value of 'quoteIdentifiers' to true in your Datasource configuration. May you need to clear the cache.

Source: https://book.cakephp.org/3.0/en/orm/database-basics.html#configuration

Upvotes: 0

Related Questions