Equivalent SQL query in DQL with ManyToMany relation with join table

Question

As the title, I am struggling to get the correct DQL version (or the correspondent query builder) of a SQL query.

There are the tables involved: SQL Graphic Schema

I need to retrive, for each product ordered by id, its images ordered by 'ord'.

This is the correct query in SQL (hopefully.. btw it works :P).

SELECT
    PG.product_id, 
    PIJG.img_id,
    PI.uri,
    PI.ord
FROM
    ProductGeneral PG 
        JOIN
    ProductImgJoinGeneral PIJG ON PG.product_id = PIJG.product_id
        JOIN
    ProductImg PI ON PIJG.img_id = PI.img_id
ORDER BY 
    PG.product_id ASC, 
    PI.ord ASC;

and these are the Entities (well only the relationships):

class ProductGeneral {
    //all the standard columns are omitted

    /**
     * @var \Doctrine\Common\Collections\Collection
     * @ORM\ManyToMany(targetEntity="AppBundle\Entity\ProductImg", inversedBy="product")
     * @ORM\JoinTable(name="productImgJoinGeneral",
     *   joinColumns={
     *     @ORM\JoinColumn(name="product_id", referencedColumnName="product_id")
     *   },
     *   inverseJoinColumns={
     *     @ORM\JoinColumn(name="img_id", referencedColumnName="img_id")
     *   }
     * )
     */
    private $img;
}

class ProductImg {
    //all the standard columns are omitted

    /**
     * @var \Doctrine\Common\Collections\Collection
     * @ORM\ManyToMany(targetEntity="AppBundle\Entity\ProductGeneral", mappedBy="img")
 */
    private $product;
}

Any help?

Answer 1

The PIJG table has no mapped Entity in your Doctrine mapping, so you can't SELECT on it. But PIJG.img_id = PI.img_id, so you can do this:

$qb = $this->productGeneralRepository->createQueryBuilder('PG')
    ->select('PG.product_id, PI.img_id, PI.uri, PI.ord')
    ->innerJoin('PG.img', 'PI')
    ->addOrderBy('PG.product_id', 'ASC')
    ->addOrderBy('PI.ord', 'ASC');

Then if you want raw DQL, just get $qb->getDql(). The innerJoin method automatically executes the double JOIN thanks to your Doctrine mapping