George Irimiciuc George Irimiciuc - 7 months ago 12
PHP Question

Doctrine not counting null values at comparison

I have such a query:

$thism = new \DateTime('first day of this month midnight');

$parameters = array('thism' => $thism, 'user' => $user,'status'=>1);

return $this->createQueryBuilder('q')
->select('count(q)')
->where('q.createdAt > :thism')
->andWhere('q.user = :user')
->andWhere('q.completed != :status')
->setParameters($parameters)
->getQuery()
->getSingleScalarResult();


This should count all my incomplete objects from this month. The problem is with
q.completed != :status
. This will not count objects that have
completed=NULL
. Completed is a boolean value

class Quote
{

/**
* @var boolean
*
* @ORM\Column(name="completed", type="boolean", nullable=true)
*/
private $completed;
}


Have I done something wrong, or why doesn't Doctrine count NULL as being different than TRUE?

I've tried replacing
'status'=>1
with
1,true,TRUE
, both with and without quotes and still no effect.

Answer

Null in MySQL is not the same as true or false. More info on that on MySQL official site.

Your query in translation to SQL will be:

SELECT count(1) 
FROM <some table>
WHERE completed != 1
AND createdAt > '01042016 00:00'
AND user = 'some_user'

If you want to query for null values too in SQL you should have a query such as:

SELECT count(1) 
FROM <some table>
WHERE 
   ( completed != 1 or completed is NULL)
AND createdAt > '01042016 00:00'
AND user = 'some_user'

If you need this column by default to have some value - you can define it in MySQL by using default 0 (or any other value) in your CREATE TABLE statement like that:

CREATE TABLE my_table(
   ...

   completed tinyint(1) default 0
   ....
)

If you do that - your query should work with no change.