Doctrine 2: group by field alias (Error: '...' does not point to a Class. )

Question

I got this Doctrine query:

        select upper(substring(e.name, 1, 1)) first_letter
        from Application\Models\Exercise e
        group by first_letter
        order by first_letter asc

But it throws an exception with the message:

Error: 'first_letter' does not point to a Class. 

If I leave out the group by and the order by, it works.

Do I have to use a native query in this case or do the sorting and grouping in my client code (probably not such a good idea depending on the amount of data in the db...) or is it possible to get this query working?

Thanks!

---

EDIT:

This is my current approach, not so nice, but works for the moment as there is not much data in the db:

    $tmpResult = $this->getEntityManager()->createQuery('
        select upper(substring(e.name, 1, 1)) first_letter
        from Application\Models\Exercise e
    ')->getResult();

    $groupedAndSortedResult = array();

    foreach($tmpResult as $row) {
        $groupedAndSortedResult[$row['first_letter']] = $row['first_letter'];
    }

    sort($groupedAndSortedResult);

    return array_values($groupedAndSortedResult);

Answer 1

My guess is that it is looking for first_letter to be an Entity. This is similar problem in some sql queries where you can't use a computed attribute from the select statement as criteria. Try the following:

select upper(substring(e.name, 1, 1)) first_letter
from Application\Models\Exercise e
group by upper(substring(e.name, 1, 1))
order by upper(substring(e.name, 1, 1)) asc

Answer 2

It seems like there is no good way to do this via DQL (at least none I could find), as Doctrine always tries to map the result to an entity. I ended up doing a native query using the PDO connection. Here is the code I use in my repository:

    return $this->getEntityManager()->getConnection()->executeQuery('
        select upper(left(tl_exercise.name, 1)) as first_letter
        from tl_exercise
        group by first_letter
        order by first_letter asc
    ')->fetchAll(\PDO::FETCH_COLUMN);

At least I got rid of the sorting/grouping in my client code. And I can use the LEFT() function which is not available in DQL by default (you can use SUBSTRING() however).

---

UPDATE: There is a way to get this done using EntityManager::createQuery and DQL, see rojoca´s answer

Answer 3

I know you've answered this question yourself but just wondering if you tried using DISTINCT:

select DISTINCT upper(substring(e.name, 1, 1)) first_letter
from Application\Models\Exercise e    
order by e.name asc

Ordering by e.name is equivalent to first_letter in this case.