Symfony – Doctrine filter date if end date is null

I have tricky one, I am trying for days to fix the issue.

I have Reports and Subscriptions.

I have specified one date for report.. (Report entity has reportDate filed)

Idea is to return one result for that specific report day.

Then in a query builder, I am triggering Subscriptions table that has startDate and endDate filed. Idea is to find table row in Subscriptions that exists in a date range of reportDate.

So, I am filtering trough Subscriptions to find one that is valid on a given day.

In my form if I input 2020-11-01 to 2020-11-10 and it filters in my custom foreach array and return excepted results.

Problem: The endDate is optional and can have NULL value. What I want to accomplish there is that if endDate IS NULL to filter all results from startDate to the future. Right now when I iterate trough all Subscriptions then result is always 0 value.

Method that handles it:

public function getSubscription($reportId, DateTime $reportDate)
{
    $from = new DateTime($reportDate->format("Y-m-d")." 00:00:00");
    $to   = new DateTime($reportDate->format("Y-m-d")." 23:59:59");

    return $this->createQueryBuilder("e")
        ->where('e.reportId =:reportId')
        ->andWhere('e.startDate <= :from')
        ->andWhere('e.endDate >= :to')
        ->setParameter('reportId', $reportId)
        ->setParameter('from', $from)
        ->setParameter('to', $to)
        ->getQuery()
        ->getOneOrNullResult();
}

also tried to add:

->andWhere('e.endDate >= :to AND (e.endDate IS NULL OR e.endDate >= :to)')

result was the same.

Answer

You’re very close. Your problem is this: e.endDate >= anything is false whenever e.endDate is null.

Try this, simplifying the thing you mentioned in your question.

->andWhere('(e.endDate IS NULL OR e.endDate >= :to)')

Advertisement

Answer