英文:
Matching rows by date on a Laravel 9 application using php-carbon objects
问题
以下是您要求的翻译内容:
问题背景
我正在构建一个Laravel应用程序,我在Booking Controller上有一个用于更新/插入预订的upsert方法。在upsert.blade.php上,我希望显示一个<select>
元素,其中包含可以将预订移动(或插入)的日期列表。
有一个名为'holidays'的表,只有一列:'day'(类型为datetime,精度为6)。该表中的每个条目表示系统将在该天放假,因此不能在此表中显示的日期进行预订或转移。
现在,我希望上述<select>
中的<option>
在对应于假期的日期时被禁用。
我尝试过的:
视图(upsert.blade.php)
<select>
<option value="" disabled selected>Select</option>
@foreach($days as $day)
<option value="{{ $day['value'] }}" @disabled($day['disabled'])>
{{ $day['display'] }}
</option>
@endforeach
</select>
控制器动作:
public function upsert()
{
$now = Carbon::now();
$last = Carbon::now()->addDays(30);
$holidays = DB::table('holidays');
$days = [];
// 用日期从$now到$last填充$days
while($now->lte($last))
{
array_push($days, [
'value' => $now->toDateString(),
'display' => $now->format('l j F Y'),
/*
* 如果匹配当前日期的假期大于1,则将日期标记为禁用
* 不起作用
*/
'disabled' => $holidays->whereDate('day', $now)->count()
]);
$now->addDay();
}
return view('upsert', [
'days' => $days,
]);
}
问题
标有'不起作用'的那行不按预期工作(我期望查询在循环中的当前日期有假期时返回1,从而将日期标记为禁用)。它只匹配循环中的第一天,如果它是假期,但它不会匹配任何其他日期。
注意:我已经将Holiday模型的'day'属性转换为'datetime',以便在访问时Laravel将该值转换为Carbon对象。
尝试解决问题
我尝试将
$holidays = DB::table('holidays');
替换为
$holidays = Holiday::all();
但这会引发以下异常:
方法Illuminate\Database\Eloquent\Collection::whereDate不存在。
所以我尝试重写查询(注意,'whereDate'被替换为'where'):
'disabled' => $holidays->where('day', $now->toDateString().' 00:00:00.000000')->count()
但这永远不会匹配。
解决方案
经过大约6个小时的尝试,阅读Laravel文档和与ChatGPT交谈,我无法找到为什么会发生这种情况的答案,所以我将有问题的行替换为
'disabled' => Holiday::whereDate('day', $now)->count()
这可以完成工作,但我认为由于需要与数据库进行多次(在我看来是不必要的)往返,对性能不利。
问题
有人能为此提供一些解释吗?
尽管我找到了解决方案,但我认为它无法扩展,而且我也没有从中学到任何东西,我仍然不知道为什么第一个查询只匹配第一天而不匹配其他日期。或者问题可能不在查询上,而在Carbon对象上?
如果您想要重现它,请按照此gist中的步骤操作:
https://gist.github.com/alvarezrrj/50cd3669914f52ce8a6188771fdeafcd
英文:
The background
I am building a Laravel application and I have an upsert method on a Booking Controller for updating/inserting bookings.
On upsert.blade.php I want to display a <select>
element with a list of days into which a booking can be moved (or inserted).
There is a 'holidays' table with only one column: 'day' (of type datetime, precision 6). Each entry on this table means the system will be on holidays for that day, so bookings cannot be made or transfered into days that appear on this table.
Now, I want the <option>
s in the above mentioned <select>
to be disabled when they correspond to a holiday.
What I tried:
The view (upsert.blade.php)
<select>
<option value="" disabled selected>Select</option>
@foreach($days as $day)
<option value="{{ $day['value'] }}" @disabled($day['disabled'])>
{{ $day['display'] }}
</option>
@endforeach
</select>
The controller action:
public function upsert()
{
$now = Carbon::now();
$last = Carbon::now()->addDays(30);
$holidays = DB::table('holidays');
$days = [];
// Populate $days with dates from $now until $last
while($now->lte($last))
{
array_push($days, [
'value' => $now->toDateString(),
'display' => $now->format('l j F Y'),
/*
* Mark day as disabled if holidays matching current
* day is greater than 1
* DOESN'T WORK
*/
'disabled' => $holidays->whereDate('day', $now)->count()
]);
$now->addDay();
}
return view('upsert', [
'days' => $days,
]);
}
The problem
The line labelled 'DOESN'T WORK' doesn't work as expected (I expect the query to return 1 if there is a holiday for the current day in the loop, thus marking the day as disabled). It only matches the first day of the loop if it's a holliday, but it won't match any other days.
Note: I have cast the 'day' property of the Holiday model to 'datetime' so Laravel casts the value to a Carbon object when accessing it.
Attempts to solve it
I tried replacing
$holidays = DB::table('holidays');
with
$holidays = Holiday::all();
but that throws the following exception
Method Illuminate\Database\Eloquent\Collection::whereDate does not exist.
So I tried rewriting the query to (note whereDate
was replaced by where
):
'disabled' => $holidays->where('day', $now->toDateString().' 00:00:00.000000')->count()
But this would never match
The solution
After around 6 hours of fiddling about with this line, reading Laravel documentation and talking to ChatGPT, I couldn't come up with an answert to why this is happening so I replaced the problematic line with
'disabled' => Holiday::whereDate('day', $now)->count()
Which does the job but I think is terrible for performance due to so many (in my opinion unecessary) round trips to the database.
The question
Could anyone shed some light on this?
Although I've found a solution, I don't think it would scale and I also didn't learn a thing from the experience, I still have no idea why the first query is only matching the first day and no other days. Or why the second one using where()
doesn't match any days at all when it is comparing strings and I am using the exact format the strings are stored in on the database.
Or maybe the problem is not on the query, but on the Carbon object?
If you want to reproduce it, follow steps on this gist:
https://gist.github.com/alvarezrrj/50cd3669914f52ce8a6188771fdeafcd
答案1
得分: 0
DB::table('holidays')
实例化了一个 Illuminate\Database\Query\Builder
对象。where
方法在原地修改了该对象。
所以如果你从1月1日到3日循环,并在每次循环中添加一个新的 where
条件,那将失败,因为现在你基本上在查询如下内容。显然,day
列无法匹配3个不同的日期。
SELECT * FROM holidays
WHERE DATE(day) = '2022-01-01'
AND DATE(day) = '2022-01-02'
AND DATE(day) = '2022-01-03'
这也是为什么它只在第一次循环中有效,因为那时只有一个 where 条件。
你需要将实例化放在 while
循环内部,以便在每次循环中重置它。这基本上就是你在解决方案中所做的事情。
关于性能,你尝试的方式不会节省任何数据库循环。每次调用 count()
时,都会访问数据库,无论是新的 $holidays
对象还是其他对象。
如果你关心性能,可以在单个查询中获取开始日期和结束日期之间的所有假日。
// 可能需要在 $now 和 $last 上调用 toDateString()
$holidays = Holiday::whereBetween('day', [$now, $last])
->get()
->pluck('id', 'day'); // 假设 day 是一个 DATE 列而不是 DATETIME 或 TIMESTAMP
// 这将为你提供一个包含如下基础数组的集合:
// ['2022-07-04' => 1, '2022-12-25' => 2]
while($now->lte($last))
{
array_push($days, [
// 现在你可以通过日期立即在数组中查找
'disabled' => isset($holidays[$now->toDateString()]),
]);
$now->addDay();
}
英文:
DB::table('holidays')
instantiates an Illuminate\Database\Query\Builder
object. The where
method modifies that object in place.
So if you're looping from January 1st-3rd and are adding a new where
condition on each loop, that's going to fail because now you are basically querying this. Obviously the day
column cannot match 3 different dates.
SELECT * FROM holidays
WHERE DATE(day) = '2022-01-01'
AND DATE(day) = '2022-01-02'
AND DATE(day) = '2022-01-03'
That's also why it only worked on the first loop for you, because at that point there is only 1 where condition.
You would need to move the instantiation inside the while
loop so that it gets reset on each loop. Which is basically what you did in your solution.
Re: performance, what you were trying to do would not have saved you any DB cycles anyway. Each time you call count()
you are hitting the database, regardless of whether it's a new $holidays
object or not.
If you're concerned about performance, one thing you could do is fetch all of the holidays between the start & end date in a single query.
// May need to call toDateString() on $now and $last
$holidays = Holiday::whereBetween('day', [$now, $last])
->get()
->pluck('id', 'day'); // Assuming day is a DATE column not DATETIME or TIMESTAMP
// This will give you a collection with an underlying array like this:
// ['2022-07-04' => 1, '2022-12-25' => 2]
while($now->lte($last))
{
array_push($days, [
// Now you can instantly look it up in the array by the date
'disabled' => isset($holidays[$now->toDateString()]),
]);
$now->addDay();
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论