Cannot add having condition on undefined result va

Question

I'm executing this query in an entity repository and keep getting

Cannot add having condition on undefined result variable

but the query has no aggregation at all. Why is this happening to me?

public function getPersonalizableItemsByOwner(User $owner)
{
    $qb = $this
        ->getEntityManager()
        ->createQuery('SELECT pi FROM '.$this->getEntityName().' pi WHERE order_id = :owner_id AND (deletedAt IS NULL OR deletedAt > :referenceDate)')
        ->setParameters(array('owner_id' => $owner->getId(), 'referenceDate' => date('Y-m-d H:i:s')));

        return $qb->getResult();
}

PS: I have very little knowledge of Doctrine, i'm tasked to add soft delete support through KnpLabs SoftDeleteable trait but only in some specific situation, thus i can't use a globally available filter and must implement it manually.

Answer 1

There was a typo in the DQL, the field was supposed to be ownerId and not order_id. Further more, the the namespace of each field needs to be provided it seems such as:

public function getPersonalizableItemsByOwner(User $owner)
{
    $qb = $this
        ->getEntityManager()
        ->createQuery('SELECT pi FROM '.$this->getEntityName().' pi WHERE pi.ownerId = :owner_id AND (pi.deletedAt IS NULL OR pi.deletedAt > :referenceDate)')
        ->setParameters(array('owner_id' => $owner->getId(), 'referenceDate' => date('Y-m-d H:i:s')));

    return $qb->getResult();
}

Answer 2

You must not check for deletedAt to be null but the check if deletedAt's ID to be null.

Changing to : deletedAt.id IS NULL should fix it.

edit : Got this error this morning and fixed it thanks to : http://www.christophe-meneses.fr/article/corriger-l-erreur-request-critical-uncaught-php-exception-doctrine-orm-query-queryexception-semantical-error-cannot-add-having-condition-on-a-non-result-variable