AÑADIR ORDENACIÓN POR RELACIONES TOMANY EN CONSULTAS USANDO DOCTRINE CON SYMFONY

Al construir consultas complejas con Doctrine en proyectos Symfony, hemos podido comprobar que genera bastantes dudas cómo añadir ordenación por relaciones entre entidades, ya sean OneToMany o ManyToMany. Es por esto, que vamos a intentar explicar en este artículo, cómo puede implementarse de una forma sencilla.

Como ejemplo, vamos a suponer que tenemos un modelo de datos con dos entidades, Articulo y Comentario, entre las que existe una relación OneToMany, tal y como se muestra a continuación:

class Articulo {
   // ...

   /**
    * @ORM\Column(type="string", length=255)
    */
   protected $titulo;

   /**
    * @ORM\OneToMany(targetEntity="AppBundle\Entity\Comentario", mappedBy="articulo")
    */
   protected $comentarios;

   // ...
}

class Comentario {
   // ...

   /**
    * @ORM\ManyToOne(targetEntity="AppBundle\Entity\Articulo", inversedBy="comentarios")
    * @ORM\JoinColumn(referencedColumnName="id")
    */
   protected $articulo;

   // ...
}

Una consulta básica para obtener un listado de todos los artículos almacenados en base de datos, ordenados por el campo titulo, podría ser esta:

$articulos = $articuloRepository->createQueryBuilder('a')
   ->orderBy('a.titulo', 'ASC')
   ->getQuery()
   ->getResult()
;

Obteniendo un array de objetos Articulo similar a este:

array [
   0 => Articulo {#1}
   1 => Articulo {#2}
   2 => Articulo {#3}
   ...
]

Pero qué ocurre si queremos ordenar esta consulta para mostrar primero aquellos artículos que tengan mayor número de comentarios. Lo primero que se nos puede ocurrir es añadir a la consulta un COUNT con los comentarios, y ordenar por ese resultado, es decir:

$articulos = $articuloRepository->createQueryBuilder('a')
   ->addSelect('COUNT(c.id) AS numComentarios')
   ->leftJoin('a.comentarios', 'c')
   ->orderBy('numComentarios', 'DESC')
   ->getQuery()
   ->getResult()
;

Pero si ejecutamos este código, lo que obtendremos es un error similar a este:

SQLSTATE[42000]: Syntax error or access violation: 1140 In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'articulo.id'; this is incompatible with sql_mode=only_full_group_by

¿Por qué se produce este error? Porque MySQL no permite consultas en las cuales se refiera a columnas no agregadas en la cláusula GROUP BY. Entendido, entonces solo faltaría añadir el GROUP BY a la consulta:

$articulos = $articuloRepository->createQueryBuilder('a')
   ->addSelect('COUNT(c.id) AS numComentarios')
   ->leftJoin('a.comentarios', 'c')
   ->orderBy('numComentarios', 'DESC')
   ->groupBy('a.id')
   ->getQuery()
   ->getResult()
;

La respuesta es sí y no. Añadiendo GROUP BY evitamos el error de MySQL, pero no tenemos el resultado final que nos gustaría. En la consulta anterior, tenemos un SELECT implícito en ->createQueryBuilder('a'), con todos los campos de la entidad Articulo y un SELECT añadido con el número de comentarios asociado a cada Articulo, es decir, estaríamos obteniendo un array de arrays similar a este:

array [
   0 => array:2 [
      0 => Articulo {#28}
      "numComentarios" => "354"
   ]
   1 => array:2 [
      0 => Articulo {#97}
      "numComentarios" => "295"
   ]
   2 => array:2 [
      0 => Articulo {#106}
      "numComentarios" => "265"
   ]
   ...
]

¿Cómo conseguimos unificar en un array todos los datos necesarios? Una forma podría ser añadir un SELECT con los campos que necesitemos, es decir:

$articulos = $articuloRepository->createQueryBuilder('a')
   ->select('a.id, a.titulo, ..., COUNT(c.id) AS numComentarios')
   ->leftJoin('a.comentarios', 'c')
   ->orderBy('numComentarios', 'DESC')
   ->groupBy('a.id')
   ->getQuery()
   ->getResult()
;

Con lo que obtendríamos un resultado similar a este, que es lo que perseguíamos:

array [
   0 => array [
      "id" => 28
      ...
      "numComentarios" => "354"
   ]
   1 => array [
      "id" => 97
      ...
      "numComentarios" => "295"
   ]
   2 => array [
      "id" => 106
      ...
      "numComentarios" => "265"
   ]
   ...
]