Incorrect date format for week interval - SQLite < v3.46 #81
Description
Hi!
I spend a large amount of time to figure out why a Trend that worked perfectly in the web server (running MySQL) was failing in a ci test running SQLite.
User::factory([
'created_at' => now()->subWeeks(2),
])->count(30)->create();
User::factory([
'created_at' => now()->subWeek(),
])->count(45)->create();
Trend::model(User::class)
->between(now()->suWeeks(2)->startOWeek(), now()->subWeek()->endOfWeek())
->perWeek()
->count();I finally figured out using some dumping that the SQLiteAdapter was not using a ISO 8601 date format, causing the troubles:
The trend placeholders generated by CarbonInterval are out of sync from one week newer than the database returns.
public function mapValuesToDates(Collection $values): Collection
{
dump($values);
$values = $values->map(fn ($value) => new TrendValue(
date: $value->{$this->dateAlias},
aggregate: $value->aggregate,
));
$placeholders = $this->getDatePeriod()->map(
fn (CarbonInterface $date) => new TrendValue(
date: $date->format($this->getCarbonDateFormat()),
aggregate: 0,
)
);
dump($placeholders);
return $values
->merge($placeholders)
->unique('date')
->sort()
->flatten();
}
In the end, because the package merge the SQL results with the placeholders, we end up with a 3 weeks interval count.