Doctrine: Union with JOIN example

CountryRepository.php

<?php

namespace AppBundle\Repository;

use AppBundle\Entity\Country;
use AppBundle\Entity\CountryTranslation;
use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\Query\ResultSetMapping;
use Doctrine\ORM\QueryBuilder;

class CountryRepository extends EntityRepository
{
    /**
     * @return Country[]
     */
    public function getSelectList()
    {
        $qbs = [];

        $qbs[] = $this->createQueryBuilder('c')
            ->addSelect('t')
            ->leftJoin('c.translations', 't')
            ->where('c.code = ?1')
            ->orderBy('c.name', 'ASC');

        $qbs[] = $this->createQueryBuilder('c')
            ->addSelect('t')
            ->leftJoin('c.translations', 't')
            ->where('c.eu = ?2')
            ->orderBy('c.name', 'ASC');

        $qbs[] = $this->createQueryBuilder('c')
            ->addSelect('t')
            ->leftJoin('c.translations', 't')
            ->where('c.eu = ?3')
            ->orderBy('c.name', 'ASC');

        $rsm = new ResultSetMapping();
        $rsm
            ->addEntityResult($this->getClassName(), 'c')
            ->addFieldResult('c', 'id_0', 'id')
            ->addFieldResult('c', 'name_1', 'name')
            ->addFieldResult('c', 'code_2', 'code')
            ->addFieldResult('c', 'eu_3', 'eu')
            ->addJoinedEntityResult(CountryTranslation::class, 't', 'c', 'translations')
            ->addFieldResult('t', 'id_4', 'id')
            ->addFieldResult('t', 'locale_5', 'locale')
            ->addFieldResult('t', 'field_5', 'field')
            ->addFieldResult('t', 'content_7', 'content')
        ;

        $query = $this->getEntityManager()->createNativeQuery($this->unionQueryBuilders($qbs), $rsm);
        $query
            ->setParameter(1, 'FR')
            ->setParameter(2, true)
            ->setParameter(3, false);

        return $query->getResult();
    }

    /**
     * @param array $queryBuilders
     *
     * @return string
     */
    private function unionQueryBuilders(array $queryBuilders)
    {
        $imploded = implode(') UNION (', array_map(function (QueryBuilder $q) {
            return $q->getQuery()->getSQL();
        }, $queryBuilders));

        return '('.$imploded.')';
    }
}

$this->createQueryBuilder('c')
    ->addSelect('t')
    ->leftJoin('c.translations', 't')
    ->where(
        $expr->orX(
            $expr->eq('c.code', ':first'),
            $expr->eq('c.eu', ':second'),
            $expr->eq('c.eu', ':third'),
        )
    )
    ->setParameters(
        [
            'first' => 'FR',
            'second' => true,
            'third' => false,
        ]
    )
    ->orderBy('c.name', 'ASC');

?

@podorozhny thanks for the comment! I had a task to receive countries in specific order:

• France
• EU countries
• Other countries

It' awesome!