CODEWITHSUNDEEP
symfonydoctrine-orm
BentCoder
BentCoder

Reputation: 12750

Turning SQL into DQL

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.

Upvotes: 1

Views: 66

Answers (1)

Javad
Javad

Reputation: 4397

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();

Upvotes: 2

Related Questions