DQL query produces error Expected Literal, got 'BY'

Question

I am having a problem with a DQL query to retrieve users and roles from a MySql database. I am using Zend Framework 2 and Doctrine 2.

The query is as follows.

public function getUsers()
{
    $builder = $this->getEntityManager()->createQueryBuilder();
    $builder->select('u, r')
            ->from('Application\Entity\Users', 'u')
            ->leftJoin('Application\Entity\UserRoles', 'r')
            ->orderBy("u.emailAddress", "ASC");

    InfoLogger::vardump($builder->getDQL());

    return $builder->getQuery()->getResult(Query::HYDRATE_OBJECT);
}

The above query produces the error, [Syntax Error] line 0, col 91: Error: Expected Literal, got 'BY'

The generated DQL is

SELECT u, r FROM Application\Entity\Users u LEFT JOIN Application\Entity\UserRoles r ORDER BY u.emailAddress ASC

Can someone please spot what is wrong with this query, many thanks.

Affaire

Answer 1

I have figured it out.

The problem was with the line

->leftJoin('Application\Entity\UserRoles', 'r')

This should have been

->leftJoin('u.userRole', 'r')

userRole is defined in my Entity\Users class.

Affaire