Skip to content

Instantly share code, notes, and snippets.

@adamsafr

adamsafr/CountryRepository.php

Last active December 25, 2024 07:44
Show Gist options
  • Save adamsafr/38ef86a9c52d7f258a2a7116f115628d to your computer and use it in GitHub Desktop.
Save adamsafr/38ef86a9c52d7f258a2a7116f115628d to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. adamsafr revised this gist Nov 29, 2017. 1 changed file with 13 additions and 14 deletions.
    27 changes: 13 additions & 14 deletions CountryRepository.php
    View file
    Original file line number Diff line number Diff line change
    @@ -6,6 +6,7 @@
    use AppBundle\Entity\CountryTranslation;
    use Doctrine\ORM\EntityRepository;
    use Doctrine\ORM\Query\ResultSetMapping;
    use Doctrine\ORM\QueryBuilder;

    class CountryRepository extends EntityRepository
    {
    @@ -14,19 +15,21 @@ class CountryRepository extends EntityRepository
    */
    public function getSelectList()
    {
    $q1 = $this->createQueryBuilder('c')
    $qbs = [];

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

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

    $q3 = $this->createQueryBuilder('c')
    $qbs[] = $this->createQueryBuilder('c')
    ->addSelect('t')
    ->leftJoin('c.translations', 't')
    ->where('c.eu = ?3')
    @@ -46,13 +49,7 @@ public function getSelectList()
    ->addFieldResult('t', 'content_7', 'content')
    ;

    $sql = $this->unionSQL([
    $q1->getQuery()->getSQL(),
    $q2->getQuery()->getSQL(),
    $q3->getQuery()->getSQL(),
    ]);

    $query = $this->getEntityManager()->createNativeQuery($sql, $rsm);
    $query = $this->getEntityManager()->createNativeQuery($this->unionQueryBuilders($qbs), $rsm);
    $query
    ->setParameter(1, 'FR')
    ->setParameter(2, true)
    @@ -62,13 +59,15 @@ public function getSelectList()
    }

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

    return '('.$imploded.')';
    }
  2. adamsafr revised this gist Nov 29, 2017. 1 changed file with 25 additions and 11 deletions.
    36 changes: 25 additions & 11 deletions CountryRepository.php
    View file
    Original file line number Diff line number Diff line change
    @@ -2,40 +2,36 @@

    namespace AppBundle\Repository;

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

    class CountryRepository extends EntityRepository
    {
    /**
    * @return Country[]
    */
    public function getSelectList()
    {
    $frQuery = $this->createQueryBuilder('c')
    $q1 = $this->createQueryBuilder('c')
    ->addSelect('t')
    ->leftJoin('c.translations', 't')
    ->where('c.code = ?1')
    ->orderBy('c.name', 'ASC');

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

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

    $sql = '('
    .$frQuery->getQuery()->getSQL()
    .') UNION ('
    .$euQuery->getQuery()->getSQL().
    ') UNION ('
    .$otherQuery->getQuery()->getSQL().
    ')';

    $rsm = new ResultSetMapping();
    $rsm
    ->addEntityResult($this->getClassName(), 'c')
    @@ -50,6 +46,12 @@ public function getSelectList()
    ->addFieldResult('t', 'content_7', 'content')
    ;

    $sql = $this->unionSQL([
    $q1->getQuery()->getSQL(),
    $q2->getQuery()->getSQL(),
    $q3->getQuery()->getSQL(),
    ]);

    $query = $this->getEntityManager()->createNativeQuery($sql, $rsm);
    $query
    ->setParameter(1, 'FR')
    @@ -58,4 +60,16 @@ public function getSelectList()

    return $query->getResult();
    }

    /**
    * @param array $sqlQueries
    *
    * @return string
    */
    private function unionSQL(array $sqlQueries)
    {
    $imploded = implode(') UNION (', $sqlQueries);

    return '('.$imploded.')';
    }
    }
  3. adamsafr created this gist Nov 29, 2017.
    61 changes: 61 additions & 0 deletions CountryRepository.php
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,61 @@
    <?php

    namespace AppBundle\Repository;

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

    class CountryRepository extends EntityRepository
    {
    public function getSelectList()
    {
    $frQuery = $this->createQueryBuilder('c')
    ->addSelect('t')
    ->leftJoin('c.translations', 't')
    ->where('c.code = ?1')
    ->orderBy('c.name', 'ASC');

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

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

    $sql = '('
    .$frQuery->getQuery()->getSQL()
    .') UNION ('
    .$euQuery->getQuery()->getSQL().
    ') UNION ('
    .$otherQuery->getQuery()->getSQL().
    ')';

    $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($sql, $rsm);
    $query
    ->setParameter(1, 'FR')
    ->setParameter(2, true)
    ->setParameter(3, false);

    return $query->getResult();
    }
    }