DQL vs SQL, why i don't have the same result?

Question

I started to create DQL request, but I have a little problem and I don't know why.

   SELECT count(*)
          FROM bw_ach bw
          where type_demande = 'MES' and soustype_demande in ('MES avec intervention', 'MES dégradée') 
          and tenu_delai_standard = 'Oui' and date_statut_cloture BETWEEN :dateFrom AND :dateTo

I have this request in SQL which work correctly and I get a normal result but I tried to transform it on DQL so I get this:

return $this->createQueryBuilder('p')
            ->select('COUNT(p)')
            ->from(bwACH::class, 'bw')
            ->andWhere("bw.TYPE_DEMANDE = 'MES'")
            ->andWhere("bw.SOUSTYPE_DEMANDE in ('MES avec intervention', 'MES dégradée')")
            ->andWhere("bw.TENU_DELAI_STANDARD = 'Oui'")
            ->andWhere('bw.DATE_STATUT_CLOTURE BETWEEN :dateFrom AND :dateTo')
            ->setParameter('dateFrom', $dateFrom)
            ->setParameter('dateTo', $dateTo)

But I don't have the same result, with DQL it is a lot bigger. I think it's something with my select which is not correct, but what? I don't know.

Edit:

I got the answer it was a stupid thing

return $this->createQueryBuilder('p')
            ->select('COUNT(p)')
            ->andWhere("p.TYPE_DEMANDE = 'MES'")
            ->andWhere("p.SOUSTYPE_DEMANDE in ('MES avec intervention', 'MES dégradée')")
            ->andWhere("p.TENU_DELAI_STANDARD = 'Oui'")
            ->andWhere('p.DATE_STATUT_CLOTURE BETWEEN :dateFrom AND :dateTo')
            ->setParameter('dateFrom', $dateFrom)
            ->setParameter('dateTo', $dateTo)

But i still trying to search why my from is useless. Thanks everyone :)

visit `http://yourLocalUrl/_profiler`, you have a **doctrine** tab that dumps every SQL queries executed within the request. Just compare executed DQL with your native SQL query to find out where the difference is. — Mcsky, Mar 03 '21 at 14:51

score 1 · Answer 1 · answered Mar 03 '21 at 23:34

The problem is probably with your date parameters in php $dateFrom and $dateTo, they are not interpreted as you think

you should try to change the date part like this :

->setParameter('dateFrom', $dateFrom->format("Y-m-d H:i:s"))
->setParameter('dateTo', $dateTo->format("Y-m-d H:i:s"))

score 0 · Answer 2 · answered Mar 04 '21 at 16:08

The from is useless because it overrides the one the EntityRepository sets when creating the queryBuilder :

/**
     * Creates a new QueryBuilder instance that is prepopulated for this entity name.
     *
     * @param string $alias
     * @param string $indexBy The index for the from.
     *
     * @return QueryBuilder
     */
    public function createQueryBuilder($alias, $indexBy = null)
    {
        return $this->_em->createQueryBuilder()
            ->select($alias)
            ->from($this->_entityName, $alias, $indexBy);
    }

Ad the alias you gave (p) is not the same as the alias you set in your from also (bw). And then you ask to count p

Tanks a lot for this explication – martin genot Mar 04 '21 at 16:28 — martin genot, Mar 04 '21 at 16:28

DQL vs SQL, why i don't have the same result?

2 Answers2