symfony2 & doctrine orderBy/DQL error

Question

I have 2 entities defined:

State entity
City entity

I am trying to select the state entity and automatically join the city entity and order the results by state's name. However, I get this error:

[Syntax Error] line 0, col 74: Error: Expected Literal, got 'BY'

My code to create the query is:

$statesQuery = $this->getEm()->createQueryBuilder();
$statesQuery->select("st");
$statesQuery->from("CoreBundle:State", "st");
$statesQuery->leftJoin("CoreBundle:City", "ct");
$statesQuery->addSelect("ct");
$statesQuery->orderBy("st.name", "ASC");

$statesQuery = $statesQuery->getQuery();

$states = $statesQuery->getResult();

If I remove the order By method I get the following error:

[Syntax Error] line 0, col -1: Error: Expected Doctrine\ORM\Query\Lexer::T_WITH, got end of string.

The DQL generated queries ( with order By and w/o orderBy):

SELECT st, ct FROM CoreBundle:State st LEFT JOIN CoreBundle:City ct ORDER BY s.name ASC

SELECT st, ct FROM CoreBundle:State st LEFT JOIN CoreBundle:City ct

The state entity:

Ec\CoreBundle\Entity\State:
    type: entity
    table: states
    id:
        id:
            type: integer
            nullable: false
            unsigned: false
            comment: ''
            id: true
            generator:
                strategy: IDENTITY
    fields:
        name:
            type: string
            nullable: false
            length: 255
        friendly_url:
            type: string
            nullable: false
            length: 255
    oneToMany:
        cities:
            targetEntity: City
            mappedBy: state
    oneToOne:
        country:
            targetEntity: Country
    lifecycleCallbacks: {  }

And the city entity:

Ec\CoreBundle\Entity\City:
    type: entity
    table: cities
    id:
        id:
            type: integer
            nullable: false
            unsigned: false
            comment: ''
            id: true
            generator:
                strategy: IDENTITY
    fields:
        name:
            type: string
            nullable: false
            length: 255
        friendly_url:
            type: string
            nullable: false
            length: 255
    manyToOne:
        state:
            targetEntity: State
            inversedBy: cities
    lifecycleCallbacks: {  }

You're ordering by s.name and you don't define s in your query. — undefined
I did a lot of trial and error before posting this question here and the "s.name" probably slipped. I've corrected my question. — raullarion

Dario Dario · Accepted Answer · 2014-07-27T23:10:48

if you want to retrieve a collection of states (and the associated cities), this is enough:

// src/AcmeBundle/Controller/MyController.php
// ...

$statesQuery = $this->getDoctrine()->getManager()->createQueryBuilder();
$statesQuery->select("st")
            ->from("CoreBundle:State", "st")
            ->orderBy("st.name", "ASC");

$states = $statesQuery->getQuery()
                      ->getResult();

So the cities will be lazy loaded. For more info about lazy/eager loading check the doctrine2 doc

EDIT: Ok, if you need this only in one place, simply do:

$statesQuery = $this->getDoctrine()->getManager()->createQueryBuilder();
$statesQuery->select("st, ct")
            ->from("CoreBundle:State", "st")
            ->leftJoin("st.cities", "ct")
            ->orderBy("st.name", "ASC");

$states = $statesQuery->getQuery()
                      ->getResult();

symfony2 & doctrine orderBy/DQL error

1 Answers