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();
}

because it has to save several possible options. Form on Twig renders several checkbox. — jjgarcía
– jjgarcía, Commented Jan 12, 2016 at 12:41
I know, but the array type in doctrine is not exactly an array in php. Removing nullable=true of the field definition in the entity, the result is the same, to create a new empty item, the value stored in the database is still N; and the query result too. — jjgarcía
– jjgarcía, Commented Jan 12, 2016 at 12:56

M J · Accepted Answer · 2017-06-07 07:52:04Z

16

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();
}

edited Jun 7, 2017 at 7:52

answered Feb 2, 2017 at 4:26

M J

3,0414 gold badges26 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Breith Over a year ago

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

dzsubek · Accepted Answer · 2016-01-12 13:09:27Z

5

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();

edited Jan 12, 2016 at 13:09

answered Jan 12, 2016 at 13:01

dzsubek

913 bronze badges

2 Comments

jjgarcía Over a year ago

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

dzsubek Over a year ago

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

Carlos Vergara · Accepted Answer · 2016-01-12 12:35:02Z

2

$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!

answered Jan 12, 2016 at 12:35

Carlos Vergara

3,6325 gold badges35 silver badges56 bronze badges

1 Comment

jjgarcía Over a year ago

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.

scoolnico · Accepted Answer · 2016-01-12 12:55:00Z

0

Your repository method is totally correct.

But, an array cannot be null. If it's null, then it's not an array: it's null.

The solution could be to change the type of the column excepcionMenu.

answered Jan 12, 2016 at 12:55

scoolnico

3,14518 silver badges25 bronze badges

1 Comment

jjgarcía Over a year ago

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

Collectives™ on Stack Overflow

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

4 Answers 4

1 Comment

2 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related