Doctrine 2 DQL CONCAT fields and constant strings

Question

I have the fields firstname and lastname in my MySQL table. For convenience, I want to add a computed column to my Doctrine 2 entity called full_name. In plain old MySQL I would do something like this

SELECT CONCAT(firstname, " ", lastname) AS full_name FROM customers;

However, concatenating fields and constant strings (" " in this case) seems not to work with Doctrine's implementation of CONCAT. When using the following code

$repository
    ->createQueryBuilder('customer')
    ->select('CONCAT(customer.firstname, " ", customer.lastname) AS full_name')
    // ...

I get the error

[Syntax Error] line 0, col 91: Error: Expected StateFieldPathExpression | string | InputParameter | FunctionsReturningStrings | AggregateExpression, got '"'

How can I achieve the same behavior as in MySQL?

score 41 · Accepted Answer · answered Jan 14 '16 at 11:33

41

Apparently, strings in DQL can only be encapsulated by single quotes, not double quotes. A brief search in the documentation did not turn up a direct mention of this behavior, but I noticed that all examples which included constant strings used single quotes.

Changing

->select('CONCAT(customer.firstname, " ", customer.lastname) AS full_name')

to

->select('CONCAT(customer.firstname, \' \', customer.lastname) AS full_name')

or

->select("CONCAT(customer.firstname, ' ', customer.lastname) AS full_name")

solved the issue

answered Jan 14 '16 at 11:33

Subsurf

1,256
1
17
28

Could you share full example of it? An error says Unknow column full_name. @Subsurf – Ali Emre Çakmakoğlu Jul 11 '17 at 13:15
Unfortunately, since I have asked this question, the code containing my initial problem has been removed from our codebase, so I can't give an example right now. There must be some other error in your query. – Subsurf Jul 17 '17 at 17:28
For the record, this is not surprising at all because the same is also true for SQL in general. See: https://stackoverflow.com/questions/1992314/what-is-the-difference-between-single-and-double-quotes-in-sql – jlh Jan 24 '18 at 15:05
That's good to know, I am mostly working with MySQL which allows double quotes for strings, so I never knew it was not part of the official SQL – Subsurf Jan 25 '18 at 14:21

score 3 · Answer 2 · answered May 04 '18 at 09:14

3

This works for me:

$builder->select([
   'customer.id as id',
   'customer.number as number',
   'CONCAT(CONCAT(customer.firstname, \' \'), customer.lastname) as name'
]);

answered May 04 '18 at 09:14

pino

136
6

score 1 · Answer 3 · answered Jul 14 '17 at 01:57

solution I use in Doctrine 2.4+:

$concat = new Query\Expr\Func('CONCAT', $name[$k]);
$concat .= ' as ' . $k;
$concat = str_replace(',', ',\' \',', $concat);
$this->query->addSelect($concat);

so $name[$k] is an array of fields, as many as you wish. I then add some spacing between the fields with the str_replace. $k is the name of the concat field, so the result of $concat is

"CONCAT(p.email,' ', h.phoneNumber,' ', p.officialName) as details"

Hope this helps someone. On MySQL database PDO Platform.

Craig

Doctrine 2 DQL CONCAT fields and constant strings

3 Answers3