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();
}
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();
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With