Search code examples
phpmysqldoctrine-ormgroup-concat

doctrine2 and group_concat

i've been looking through the docs/google and didn't find any solution.

is there any way to execute or imitate GROUP_CONCAT using doctrine2 (DQL) without native mysql?

eg.:

SELECT u.id, u.name, [GROUP_CONCAT(...)] AS user_messages
FROM models\Users u
LEFT JOIN models\Messages m
GROUP BY u.id

to get

GROUP_CONCAT(m.id,'|',m.title SEPARATOR ':')

right now i'm using the createNativeQuery() to run it, but i'm looking for a doctrine2 solution.

thanks in advance

Solution

  • Yes this is possible with DoctrineExtensions from Beberlei (a core developer of Doctrine2). In doctrine2 you can define your own query expressions by extending the functionNode class.

    The easiest way is to include the DoctrineExtensions lib in your project. I don't know wether your are using Zend Framework or Symfony or any other framework so can't help you with embedding it.

    You can checkout DoctrineExtensions here:

    https://github.com/beberlei/DoctrineExtensions

    And the group function:

    https://github.com/beberlei/DoctrineExtensions/blob/master/src/Query/Mysql/GroupConcat.php

    Be aware you should use GroupConcat as function name not GROUP_CONCAT like in MySQL.

    Hope it helps you!