DateConstraint : Nullable Issue

The Date Constraint is not working properly with nullable value.

    DateConstraint::make('application_submitted_at')->nullable()

    DateConstraint::make('application_submitted_at')->nullable()

This provides the two more extra options : 'Is Blank' and 'Is Filled'. If I understand correctly, 'Is Blank' option is for null values & Is Filled is for non-null values. When I try to apply 'Is Blank' - the filter works correctly to identify the null values. But when I try is 'Is Filled' - the filter does not work. I logged the query and saw the query being run :

select count(*) as aggregate from `applications` where ((`applications`.`application_submitted_at` is not null and not `applications`.`application_submitted_at` = ?))

select count(*) as aggregate from `applications` where ((`applications`.`application_submitted_at` is not null and not `applications`.`application_submitted_at` = ?))

The bindings are

[]

[]

I check that the bindings value is string (blank value, i.e., ''). I tried to run this with my mysql server (frontend phpmyadmin), I tried with ? replaced by '', i.e.,

select count(*) as aggregate from `applications` where ((`applications`.`application_submitted_at` is not null and not `applications`.`application_submitted_at` = ''))

select count(*) as aggregate from `applications` where ((`applications`.`application_submitted_at` is not null and not `applications`.`application_submitted_at` = ''))

and it throws an error

#1525 - Incorrect DATETIME value: ''

#1525 - Incorrect DATETIME value: ''

So, I tried to run the query without the last part, i.e.,

select count(*) as aggregate from `applications` where ((`applications`.`application_submitted_at` is not null ))

select count(*) as aggregate from `applications` where ((`applications`.`application_submitted_at` is not null ))

and it runs correctly. I guess the nullable() method is trying to get either NULL values or blank strings. However, for DATETIME values it can't take a blank string value. Probably I will need to write a new Operator to overcome this. Any help is most appreciated.

1 Reply

Anish•3mo ago

Solved it by using custom operator.

DateConstraint::make('application_submitted_at')
     ->pushOperators([
         Operator::make('submiited')
             ->label(fn (bool $isInverse): string => $isInverse ? 'Is NULL' : 'Is Not NULL')
             ->summary(fn (bool $isInverse): string => 'Application submitted at ' . ($isInverse ? ' is NULL' : 'is Not NULL') )

             ->baseQuery(
                 fn (Builder $query, bool $isInverse) =>
                     $query->{$isInverse ? 'whereNull' : 'whereNotNull'}('application_submitted_at')
             )

    ])

DateConstraint::make('application_submitted_at')
     ->pushOperators([
         Operator::make('submiited')
             ->label(fn (bool $isInverse): string => $isInverse ? 'Is NULL' : 'Is Not NULL')
             ->summary(fn (bool $isInverse): string => 'Application submitted at ' . ($isInverse ? ' is NULL' : 'is Not NULL') )

             ->baseQuery(
                 fn (Builder $query, bool $isInverse) =>
                     $query->{$isInverse ? 'whereNull' : 'whereNotNull'}('application_submitted_at')
             )

    ])

Gaming

Programming

DateConstraint : Nullable Issue