Eloquent ORM is double-quoting tables and fields

Question

First, I'm using Eloquent ORM from here: https://github.com/illuminate/database

Without Laravel, I just want to use the Eloquent Query Builder in my project.

But:

use Illuminate\Database\Connection;
use PDO;

$pdo = new PDO('mysql:host=localhost;dbname=mydb', 'root');
$connection = new Connection($pdo);

$user = $connection->table('user')->where('name', '=', 'foo')->get();

Is producing the following error:

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 '"user" where "name" = 'foo'' at line 1

Viewing the query that is performing in \Illuminate\Database\Connection::select():

the $query variable equals to: select * from "user" where "name" = ?

It's an invalid query, because the doble quotes " around user:

mysql> select * from "user";
ERROR 1064 (42000): 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 '"user"' at line 1

There is where Eloquent quotes with double quotes: https://github.com/illuminate/database/blob/master/Grammar.php#L98

Have you looked at Torch (previously IlluminateNonLaravel)?. It explains how to use Illuminate components outside of Laravel including Database. — Ben Swinburne

Shadow Shadow · Accepted Answer · 2015-12-06T22:28:54

You can turn on the ansi_quotes sql mode within mysql (SET sql_mode='ANSI_QUOTES';), then schema object names within " are interpreted correctly, see mysql documentation on schema object names for details.
There may be a setting at eloquent level that tells eloquent query builder how to quote schema object names. Update: set the connection object's grammar to mysql, which will use backtick to quote object names.
```
// set new grammar class

DB::connection()->setQueryGrammar(new MySqlGrammar());
```

Eloquent ORM is double-quoting tables and fields

2 Answers