Symfony querybuilder orderby failed

Question

in a querybuilder i do an orderby, but if i orderby a 'leftjoin' my orderby doesn't work and i don't understand why.

My querybuilder :

        $qb = $this->createQueryBuilder('r')
        ->leftJoin('r.clientCategorisation', 'cc')
        ->leftJoin('cc.categorisation', 'c')
        ->addOrderBy('c.id','ASC')
    $a_result = $qb->getQuery()->getResult();
    return $a_result;

If i orderby my 'r', its ok, but if i try to order 'c' that does't work ... I can order my 'c' asc if in my view html.twig i add :

{{  clientCategorisation.categorisation.id}}

I don't understand why ??? And this method work only for 'asc', 'desc' failed.

Edit : I have a datatable and in my table my order is on an object :

A_resultats = $this->_O_resultatsR->getByRapportOrderByCat($idRapport, $A_onglet);

getByRapportOrderByCat is my querybuilder.

Then in a foreach :

foreach ( $A_resultats as $I_key => $O_resultat ) {

i construct my columns, and in this column 'analyse':

$A_valuesForTableau[$I_key]['analyse'] = $this->render('mypath', array(
                    'O_resultat'        => $O_resultat,
                    'A_clientCats'        => $A_clientCats,
            ))->getContent();

i give again my object $O_resultat to the template (if a comment this object in analyse my order is nice) and i think in the template my order from querybuilder is replace by order by asc (basic order by).

Sorry for my bad english.

edit2 : Entity resutat : class Resultat { /** * @var integer * * @ORM\Column(name="id", type="integer") * @ORM\Id * @ORM\GeneratedValue(strategy="AUTO") */ private $id;

    /**
     * @ORM\ManyToOne(targetEntity="ClientCategorisation")
     * @ORM\JoinColumn(name="client_categorisation", referencedColumnName="id", onDelete="CASCADE", nullable=true)
     */
    private $clientCategorisation;


    /**
     * Get id
     *
     * @return integer 
     */
    public function getId()
    {
        return $this->id;
    }

    /**
     * Set clientCategorisation
     *
     * @param ClientCategorisation $clientCategorisation
     * @return Resultat
     */
    public function setClientCategorisation(ClientCategorisation $clientCategorisation = null)
    {
        $this->clientCategorisation = $clientCategorisation;

        return $this;
    }

    /**
     * Get clientCategorisation
     *
     * @return ClientCategorisation 
     */
    public function getClientCategorisation()
    {
        return $this->clientCategorisation;
    }

}

Entity clientcategorisation :

class ClientCategorisation 
{
    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @ORM\ManyToOne(targetEntity="Categorisation")
     * @ORM\JoinColumn(name="categorisation", referencedColumnName="id", onDelete="CASCADE")
     */
    private $categorisation;

    /**
     * Get id
     *
     * @return integer 
     */
    public function getId()
    {
        return $this->id;
    }

    /**
     * Set categorisation
     *
     * @param string $categorisation
     * @return ClientCategorisation
     */
    public function setCategorisation($categorisation)
    {
        $this->categorisation = $categorisation;

        return $this;
    }

    /**
     * Get categorisation
     *
     * @return string 
     */
    public function getCategorisation()
    {
        return $this->categorisation;
    }

}

categorisation entity :

class Categorisation
{
    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @var string
     *
     * @ORM\Column(name="nom", type="string", length=255)
     */
    private $nom;

    /**
     * @var integer
     *
     * @ORM\Column(name="ordre", type="integer", nullable=true)
     */
    private $ordre;

    /**
     * Get id
     *
     * @return integer
     */
    public function getId()
    {
        return $this->id;
    }

    /**
     * Set nom
     *
     * @param string $nom
     * @return Categorisation
     */
    public function setNom($nom)
    {
        $this->nom = $nom;

        return $this;
    }

    /**
     * Get nom
     *
     * @return string
     */
    public function getNom()
    {
        return $this->nom;
    }


    /**
     * Set ordre
     *
     * @param integer $ordre
     * @return Categorisation
     */
    public function setOrdre($ordre)
    {
        $this->ordre = $ordre;

        return $this;
    }

    /**
     * Get ordre
     *
     * @return integer
     */
    public function getOrdre()
    {
        return $this->ordre;
    }
}

edit2 : I have found a solution, when i use 'O_resultat' my order fail, so in my template i get 'A_resultats' and that work like this :

{% for O_resultats in A_resultats %}
    {% if O_resultats.id == O_resultat.id %}

and i replace in my vieuw O_resultat by O_resultats

Answer 1

Try adding the field as select argument and mark as hidden in order to don't modify the hydration flow (object instead of array) as follow:

    $qb = $this->createQueryBuilder('r')
        ->addSelect('c.id as HIDDEN mysort')
        ->leftJoin('r.clientCategorisation', 'cc')
        ->leftJoin('cc.categorisation', 'c')
        ->addOrderBy('mysort','ASC');
    $a_result = $qb->getQuery()->getResult();

    return $a_result;

Hope this help

Answer 2

It seems like you have order-by tag or orderBy annotation in categorisation relation of clientCategorisation mapping. If you have that annotation, your order is explicitly merged by your order created in query builder.

Example:

<?php

/** @Entity **/
class User
{
    // ...

    /**
     * @ManyToMany(targetEntity="Group")
     * @OrderBy({"id" = "ASC"})
     **/
    private $groups;
}

If you create query like this:

$qb = $this->createQueryBuilder('user')
    ->leftJoin('user.groups', 'group')
    ->orderBy('group.name', 'DESC')
;

The final query executed by doctrine will be:

SELECT user FROM User LEFT JOIN user.groups group ORDER BY group.id ASC, group.name DESC;

So that the result will be ordered by group.id ASC (which is defined in your mapping), then ordered by group.name DESC (which is defined in your query builder).

If you want to be able to sort by group.name DESC (in example), you have to remove @OrderBy({"id" = "ASC"}) in annotation.

See Ordering To-Many Associations.