Symfony2, create querybuilder where clause, not empty or not null

Question

I've one type array field in Entity,

MyEntity.php

/**
 * @var string
 *
 * @ORM\Column(name="excepcionMenu", type="array", length=255, nullable=true)
 */
private $excepcion;

I would like to get a QueryBuilder to select not empty or not null in $excepcion field.

I'm trying MyEntityRepository.php

public function findAllExcepcionesByItem($itemId) {

    $query = $this->createQueryBuilder('p')
            ->leftJoin('p.item', 'i')
            ->where('i.id = :actual')->setParameter('actual', $itemId)
            ->andWhere('p.excepcion IS NOT NULL')
            ->getQuery();

    return $query->getResult();
}

But this returns all table records.

public function findAllExcepcionesByItem($itemId) {

    $query = $this->createQueryBuilder('p')
            ->leftJoin('p.item', 'i')
            ->where('i.id = :actual')->setParameter('actual', $itemId)
            ->andWhere('p.excepcion IS NULL')
            ->getQuery();

    return $query->getResult();
}

But this returns zero records.

this field in the database stores the values in this way:

a:0:{} // empty
N; // null
a:2:{i:0;i:2;i:1;i:4;} // not empty or not null

Is it possible to do this with QueryBuilder or should be done with DQL?

thanks a lot

---

UPDATED solution contributed by @Attila Szalay

public function findAllExcepcionesByItem($itemId) {

    $query = $this->createQueryBuilder('p')
            ->leftJoin('p.item', 'i')
            ->where('i.id = :actual')->setParameter('actual', $itemId)
            ->andWhere('p.excepcion != :null')->setParameter('null', serialize(null)) //not null
            ->andWhere('p.excepcion != :empty')->setParameter('empty', serialize([])) //not empty
            ->getQuery();

    return $query->getResult();
}

Solution 1

Your data stored as serialized "string" in your database so NULL value will be a "N;" string and it is not a NULL value for db engines.

Try this:

 $query = $this->createQueryBuilder('p')
        ->leftJoin('p.item', 'i')
        ->where('i.id = :actual')->setParameter('actual', $itemId)
        ->andWhere('p.excepcion != :null')->setParameter('null', 'N;') //not null
        ->getQuery();

Solution 2

The other solution for the problem which worked for me is:

public function findAllExcepcionesByItem($itemId) {
    $query = $this->createQueryBuilder('p')
        ->leftJoin('p.item', 'i')
        ->where("i.id = :actual")->setParameter("actual", $itemId)
        ->andWhere("p.excepcion != ''") // NOT EMPTY
        ->andWhere("p.excepcion IS NOT NULL") // NOT NULL
        ->getQuery();
    return $query->getResult();
}

Solution 3

$qb = $this->createQueryBuilder('p');
$query = $qb->leftJoin('p.item', 'i')
        ->where('i.id = :actual')->setParameter('actual', $itemId)
        ->andWhere($qb->expr()->isNotNull("p.excepcion"))
        ->getQuery();

In short you need to use the Expr class, which is explained in further detail in the QueryBuilder chapter of Doctrine's documentation. I just showed you how to use it, however!

Comments

[jjgarcía]

thank you very much, but the result is exactly the same as without using regular expressions. Like ->andWhere('p.excepcion IS NOT NULL'). I think the problem is the type of field array.

[jjgarcía]

thank you @scoolnico, I have already answered this question in your comment on the main question.

[jjgarcía]

You're right, and had not fallen into this detail :) Now I only need check for a:0:{} thanks

[dzsubek]

You can use serialize(null) for NULL check instead of 'N;' string and serialize([]) for empty array.

[Breith]

->andWhere("p.excepcion IN NOT NULL") // NOT NULL IN or IS ? :)