Turning SQL into DQL

Question

I'm trying to turn this SQL into DQL with createQueryBuilder() and innerJoin() but confused a bit.

ORM

class Brands
{
    private $id;
    /**
     * @ORM\OneToMany(targetEntity="Cars", mappedBy="brands")
     */
    private $cars;
}

class Cars
{
    protected $id;
    /**
     * @ORM\ManyToOne(targetEntity="Brands", inversedBy="cars")
     * @ORM\JoinColumn(name="brands_id", referencedColumnName="id", nullable=false)
     */
    protected $brands;
}

SQL

SELECT
cars.id AS CarId,
cars.model AS CarModel,
brands.id AS BrandId,
brands.name AS BrandName
FROM cars
INNER JOIN brands ON brands.id = cars.brands_id
ORDER BY
cars.model ASC
AND
brands.name ASC

REPO

$repo = $this->getEntityManager()->getRepository('CarBrandBundle:Cars');

$query = $repo->createQueryBuilder('c')
              ->innerJoin(.....)

Accepted answer here didn't look clean to me. I believe there is better way of doing it.

Could you post the entities mapping? To make sure the entities are related or not — Javad, Jul 21 '14 at 21:31

score 2 · Accepted Answer · answered Jul 21 '14 at 21:43

2

Here is what may help you:

$fields = array('c.id', 'c.model', 'b.id', 'b.name');
$repo = $this->getEntityManager()->getRepository('CarBrandBundle:Cars');

$query = $repo->createQueryBuilder('c')
       ->select($fields)
       ->join('c.brands', 'b')
       ->addOrderBy('c.model', 'ASC')
       ->addOrderBy('b.name', 'ASC')
       ->getQuery();
$result = $query->getResult();

answered Jul 21 '14 at 21:43

Javad

4,339
3
21
36

Good; happy to hear that – Javad Jul 21 '14 at 22:10

Turning SQL into DQL

1 Answers1