Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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();
}
like image 247
jjgarcía Avatar asked Jan 12 '16 12:01

jjgarcía


1 Answers

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();
}
like image 54
M.J Avatar answered Sep 30 '22 12:09

M.J