Eloquent whereBetween with DatePeriod

[Cellard]

Laravel Version

12.*

PHP Version

8.*

Database Driver & Version

No matter

Description

As we all know, whereBetween method may accept CarbonPeriod as $values argument. Lets look inside:

    public function whereBetween($column, iterable $values, $boolean = 'and', $not = false)
    {
        $type = 'between';

        if ($values instanceof CarbonPeriod) {
            $values = [$values->getStartDate(), $values->getEndDate()];
        }

        $this->wheres[] = compact('type', 'column', 'values', 'boolean', 'not');

        $this->addBinding(array_slice($this->cleanBindings(Arr::flatten($values)), 0, 2), 'where');

        return $this;
    }

But CarbonPeriod may not have end date! We can construct DatePeriod two ways: passing end-date or passing number of intervals:

$period1 = new \DatePeriod(now(), new \DateInterval('P2M'), now()->addMonths(2));
$period2 = new \DatePeriod(now(), new \DateInterval('P2M'), 2);

$period1 has end-date, but $period2 hasn't.

So I think that in such case method should calculate end-date itself.

Next problem is how between works. Mysql documentation says that between is equivalent to the expression (min <= expr AND expr <= max). Now look at DatePeriod documentation. The fourth argument is an $options, it may accept DatePeriod::EXCLUDE_START_DATE and DatePeriod::INCLUDE_END_DATE.

If DatePeriod excludes start-date, the query should be (min < expr AND expr <= max).
If DatePeriod excludes end-date, the query should be (min <= expr AND expr < max).
If DatePeriod excludes both dates, the query should be (min < expr AND expr < max).

So at the moment Eloquent is not absolutely correct.

And the last but not the least: why this method accepts CarbonPeriod and not accepts base DatePeriod?

I think, that whereBetween

• should accept DatePeriod
• should evaluate end-date when required
• should respect EXCLUDE_START_DATE and INCLUDE_END_DATE options

Steps To Reproduce

No steps required

[github-actions]

Thank you for reporting this issue!

As Laravel is an open source project, we rely on the community to help us diagnose and fix issues as it is not possible to research and fix every issue reported to us via GitHub.

If possible, please make a pull request fixing the issue you have described, along with corresponding tests. All pull requests are promptly reviewed by the Laravel team.

Thank you!

[kbs1027]

@Cellard
hello, I’d like to work on this issue.
I’ll prepare a PR that adds DatePeriod support while keeping backward compatibility for existing whereBetween behavior.

[Cellard]

@kbs1027

This is absolutely unnecessary to check

$values instanceof \DatePeriod || $values instanceof CarbonPeriod

as CarbonPeriod extends \DatePeriod

Moreover, your fix doesnt respect DatePeriod::EXCLUDE_START_DATE and DatePeriod::INCLUDE_END_DATE options.

[Cellard]

@kbs1027, my version is:

    /**
     * Add a where between date period statement to the query.
     * 
     * @param  \Illuminate\Contracts\Database\Query\Expression|string  $column
     * @param  \DatePeriod  $period
     * @param  string  $boolean
     * @param  bool  $not
     *
     * @return $this
     */
    public function whereBetweenDatePeriod($column, \DatePeriod $period, $boolean = 'and', $not = false)
    {
        $startDate = $period->getStartDate();
        $endDate = $period->getEndDate();

        if ($endDate === null) {
            $dates = iterator_to_array($period);

            $endDate = empty($dates)
                ? $startDate
                : end($dates);
        }

        if ($not) {
            // Not means excluding
            $min = $period->include_start_date ? '<' : '<=';
            $max = $period->include_end_date ? '>' : '>=';
        } else {
            $min = $period->include_start_date ? '>=' : '>';
            $max = $period->include_end_date ? '<=' : '<';
        }
        
        return $this->where(fn(Builder $builder) => $builder
            ->where($column, $min, $startDate)
            ->where($column, $max, $endDate),
            boolean: $boolean,
        );
    }

    public function whereBetween($column, iterable $values, $boolean = 'and', $not = false)
    {
        if ($values instanceof \DatePeriod) {
            return $this->whereBetweenDatePeriod($column, $values, $boolean, $not);
        }

        ....etc()
    }