1

In Doctrine QueryBuilder, I want to find all records where a date field does not have a real date. I have found, though, that the date '0000-00-00' is NULL, and it's also NOT NULL. In other words, 

$qb->select('t')
    ->from(myTable, 't')
    ->andWhere ... some condition...
    ->andWhere($qb->expr()->isNull('t.myDate'));

and

$qb->select('t')
    ->from(myTable, 't')
    ->andWhere ... some condition...
    ->andWhere($qb->expr()->isNotNull('t.myDate'));

both include records where the value in myDate is '0000-00-00'.

I've considered doing something like,

$qb->select('t')
    ->from(myTable, 't')
    ->andWhere ... some condition...
    ->andWhere($qb->expr()->eq('t.myDate','?1'))
    ->setParameter(1, '0000-00-00');

but 1) that only picks up the dates that are '0000-00-00' and misses the dates that are actually null; and 2) I understand that '0000-00-00' doesn't always equal '0000-00-00'; that even though my column is a DATE type, the $qb->expr()->eq( ... ) syntax might have to include TIME characters (00:00:00).

It would be cool if Doctrine had something like notWhere:

$qb->select('t')
    ->from(myTable, 't')
    ->andWhere ... some condition...
    ->butDefinitelyNotWhere($qb->expr()->isNull('t.myDate'));

because instead of finding all of the records where the date is NOT NULL, it finds what I'm looking for: all of the records that do not belong to the set where the date "is" NULL.

jcropp
  • 1,236
  • 2
  • 10
  • 29
  • Have you tried adding a `->andWhere($qb->expr()->neq('t.myDate', ':nullDate'))->setParameter('nullDate', '0000-00-00')` ? – rkeet May 17 '18 at 08:35
  • I said in the question that I had considered that option, but that it misses the dates that are actually null (empty). – jcropp May 17 '18 at 16:43

1 Answers1

0

I did not try, but maybe with something like:

 ->andWhere($qb->expr()->isNotNull('WEEK(t.myDate)'));
Angel Marin
  • 61
  • 3
  • gives `[Syntax Error] line 0, col 79: Error: Expected known function, got 'WEEK'` – jcropp May 17 '18 at 06:48
  • Same error. Writing a query with `WEEK('myDate') is not null` in mySql works there, but I can't get Doctrine to produce a working query. – jcropp May 17 '18 at 07:35
  • To make `WEEK (...)` work you need a [User defined function](https://www.doctrine-project.org/projects/doctrine-orm/en/2.6/cookbook/dql-user-defined-functions.html#date-diff) – rkeet May 17 '18 at 08:33