Do a join in DQL

Question

I'm making several turns and do not quite understand how a "Join" in DQL, I want to use to join two tables in a Symfony project by MySQL've taken the decision that I would like to translate, but not quite understand how to perform this JOIN, what strikes me most is I do not know where to tell the table that I want to make the JOIN but only in the examples I've seen displayed only a single table call.

This is my Code MySQL:

SELECT t1.id, AVG(t2.valoracion) 
      FROM video AS t1, valoracion_video AS t2 
        WHERE t1.id=t2.video_id 
        GROUP BY t2.video_id 
        ORDER BY t2.valoracion DESC;

This's the entities :)

VIDEO

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

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

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

    /**
     * @var \DateTime
     *
     * @ORM\Column(name="fecha_publicacion", type="datetime", nullable=false)
     */
    private $fechaPublicacion;

    /**
     * @var \Categoria
     *
     * @ORM\ManyToOne(targetEntity="Categoria", inversedBy="video")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="categoria_id", referencedColumnName="id")
     * })
     */
    private $categoria;

    /**
     * @var \Proveedor
     *
     * @ORM\ManyToOne(targetEntity="\odoc\ProveedorBundle\Entity\Proveedor", inversedBy="video")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="proveedor_id", referencedColumnName="id")
     * })
     */
    private $proveedor;

    /**
     * @ORM\OneToMany(targetEntity="ValoracionVideo", mappedBy="video")
     */
    protected $valoracionvideo;    

VALORACION

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

    /**
     * @var integer
     *
     * @ORM\Column(name="valoracion", type="integer", nullable=false)
     */

    private $valoracion;

    /**
     * @var \DateTime
     *
     * @ORM\Column(name="fecha", type="datetime", nullable=false)
     */
    private $fecha;

    /**
     * @var \Video
     *
     * @ORM\ManyToOne(targetEntity="Video", inversedBy="valoracion_video")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="video_id", referencedColumnName="id")
     * })
     */
    private $video; 

    /**
     * Get id
     *
     * @return integer 
     */

Answer 1

Try the following DQL (though, I'm not certain that it will work).

Also remember to think of DQL as objects first, not SQL. That means using entity class and property names instead of table and column names.

SELECT t1.id, AVG(t2.valoracion) valoracion_media
FROM My\Entity\ValoracionVideo t2
JOIN t2.video t1
GROUP BY t1.id
ORDER BY valoracion_media DESC;

Answer 2

This is the result :)

SELECT v FROM Bundle:Entity v JOIN v.valoracionvideo a WHERE v.id = a.video GROUP BY a.video ORDER BY a.valoracion DESC