Get data from two columns for entity in Doctrine ORM QueryBuilder

Question

I have such problem when getting data with Doctrine queryBuilder.

How to change this SQL:

SELECT c.*, ct.value as name_pl
FROM cities c
LEFT JOIN cities_translations ct ON c.id = ct.city_id AND ct.language_code = 'pl'

(getting listed data in select from 2 tables)

Into DQL with Doctrine Query Builder?

I have already this Entity:

/**
 * @ORM\Entity()
 */
class City
{
    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue
     */
    public $id

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

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

    /**
     * @ORM\OneToMany(targetEntity="CityTranslation", mappedBy="city")
     */
    public $translations;
}

And query with query builder:

$queryBuilder
        ->select('c, ct.value as name_pl')
        ->from('cities', 'c')
        ->leftJoin('c.translations', 'ct', Join::WITH, 'ct.language_code = pl')
        ->getQuery()
        ->getResult();

Effect is that it queries data correctly from the Database but it fails when mapping data into object. It returns array with 2 items (first is City object, and second is name_pl string and value) insead just list of City object (and name_pl as a field of City object)

Answer 1

Try:

$queryBuilder
        ->select('c', 'c')
        ->from('cities', 'c')
        ->leftJoin('c.translations', 'ct', Join::WITH, 'ct.language_code = pl')
        ->addSelect('ct', 'ct')
        ->addSelect('ct.value', 'name_pl')
        ->getQuery()
        ->getResult();