CODEWITHSUNDEEP
symfonydoctrinequery-buildernative-sql
samia01
samia01

Reputation: 51

Convert query build to native SQL with union

I made two count queries with query build doctrine but after I found that there is several query count that repeats so I must work with UNION but there is no union in doctrine query build.

So the solution is the use of native SQL with UNION.

I do not know how to do the translation in native query

this is my request 1 

/**
     * @param Analyse $analyse
     * @return mixed
     * @throws \Doctrine\ORM\NonUniqueResultException
     */
    public function countTotalErrorByIdAnalyse(Analyse $analyse)
    {
        return $this->createQueryBuilder('a')
                    ->select('count(a)')
                    ->innerJoin('a.analyse', 'analyse')
                    ->where('analyse.id = :analyse')
                    ->setParameter('analyse', $analyse->getId())
                    ->getQuery()
                    ->getSingleScalarResult();

       
    }

this is my request 2 

 /**
     * @param Analyse $analyse
     * @param string $severity
     * @return mixed
     * @throws \Doctrine\ORM\NonUniqueResultException
     */
    public function countErrorsBySeverity(Analyse $analyse, string $severity){

      return   $this->createQueryBuilder('a')
                    ->select('count(a)')
                    ->innerJoin('a.analyse', 'analyse')
                    ->innerJoin('a.rule', 'rule')
                    ->where('rule.severity = :error')
                    ->setParameter('error', $severity)
                    ->getQuery()
                    ->getSingleScalarResult();

    }

Upvotes: 0

Views: 505

Answers (1)

Robert Saylor
Robert Saylor

Reputation: 1369

I would strongly suggest trying to work out your query in Doctrine but if native is the only way then it is the only way.

Native SQL in Symfony: Note I uses these in Symfony 3 but should work in Symfony 4. Once you get more use to Doctrine you will find it really does everything native can do and some items it does not do there are plugins available to expand Doctrine's abilities. Also, one way to get around Unions is to run two queries then combine the arrays.

$em = $this->getDoctrine()->getManager();
$sql = "SELECT .. FROM .. WHERE .. ";
$result = $em->getConnection()->prepare($sql);
$result->execute();

Another option is to use a repository: Note this uses Doctrine so all Doctrine rules apply.

    $sql =
        "
        SELECT
            ...
        FROM
            App:EntityName alias
        WHERE
            ...
        "
    ;
    $query = $this->getEntityManager()->createQuery($sql);
    $results = $query->getResult();

Upvotes: 0

Related Questions