英文:
My Eloquent doenst select the correct time data, pls help me understand
问题
我目前正在制作这个队列网站,所以我想选择最长的总排队时间。我可以选择每个类别的最长排队时间,但在我想检索最长的总排队时间时,我的模型似乎没有选择正确的数据。
控制器选择排队时间
选择总体最长排队时间的部分
$longestQ = queueModel::max('wait_time');
选择每个类别的最长排队时间的部分
$longestQ1 = queueModel::where('category', $categories[0]->QName)->whereDate('created_at', '=', now())->max('wait_time');
这是总体排队时间的结果
"7:27" // app\Http\Controllers\SpvController.php:606
这是一个类别中最长排队时间的结果,应该是总体最长
"36:59" // app\Http\Controllers\SpvController.php:929
能否有人以一个小白可以理解的方式解释这个问题。
如果您需要代码的更多部分,我会乐意提供。提前致谢。
我已经尝试将列内容从字符串转换为时间,但仍然不起作用,同时我也尝试使用一些查询构建器,但仍然与Eloquent给出的结果相同。
英文:
im currently making this queue web, so i want to select the longest overall queue time. im able to select the longest queue time per category but somehow when i want to retrieve the longest overall queue time, my model doesnt select the correct data.
Controller to select the queue time
This part to take overall longest queue time
$longestQ = queueModel::max('wait_time');
this part to take the longest queue time per category
$longestQ1 = queueModel::where('category',$categories[0]->QName)->whereDate('created_at','=',now())->max('wait_time');
this is the result of the overall queue time
"7:27" // app\Http\Controllers\SpvController.php:606
and this is the result of the longest queue time of 1 category, it should be the longest overall
"36:59" // app\Http\Controllers\SpvController.php:929
could anybody explain this the way a dummy could undderstand pls.
if you need more part of the code, i would be happy to provide. thanks in advance
i have tried to convert the column content from string to time but still doesnt work,
also i've tried using some query builder but still giving the same result as the eloquent
答案1
得分: 1
它将返回表中所有行中wait_time
的最大值,而不仅仅是当前日期的行。要选择当前日期的最长队列等待时间,更新您的查询以使用whereDate
来筛选当前日期:
$longestQ = queueModel::whereDate('created_at', '=', now())
->max('wait_time');
如果wait_time
以字符串形式存储,您可能需要使用属性转换将其转换为数值格式。
英文:
It will return the maximum value for wait_time
across all rows in the table, rather than just the rows for the current day. To select the longest overall queue time for the current day, update your query to filter by the current day using the whereDate
:
$longestQ = queueModel::whereDate('created_at', '=', now())
->max('wait_time');
If wait_time
is stored as a string, you may need to convert it to a numeric format using attribute casting.
答案2
得分: 1
如果你的wait_time列的类型是VarChar(即字符串),那么数据库将按字符串进行比较。它不知道这些字符串的含义,只是逐个字符查看它们。
给定以下列表:
- "00000000"
- "7:27"
- "5 years"
- "36:59"
最大值,即按字母顺序排在最后的是"7:27",因为以"7"开头的字符串将在以"0"、"3"或"5"开头的字符串之后。
为了避免这种情况,你需要采取以下两种方法之一:
- 在设计数据库时使用更合适的列类型。如果数据库知道你打算将它们视为分钟和秒,它可以进行适当的比较。它可以将值规范化为75:20到1:15:20,但如果你愿意,可以在输出时将其格式化回来。
- 格式化你的字符串值,以便它们在字典序中进行比较,例如,通过在左侧用零填充的固定宽度。给定字符串"007:27"和"036:59",最大值(即按排序顺序排在最后的)将是"036:59",正如你所希望的。
英文:
If your wait_time column is of type VarChar (i.e. string), then the database will compare its values as strings. It has no idea of the meaning of those strings, it just looks at them character by character.
Given the following list:
- "00000000"
- "7:27"
- "5 years"
- "36:59"
The maximum value, i.e. the one that comes last in alphabetical order, is "7:27", because any string starting "7" will come after any string starting "0", "3", or "5".
To avoid this, you need to do one of two things:
- Use a more appropriate column type in the design of your database. If the database knows that you intend these as minutes and seconds, it can compare them appropriately. It may normalise a value like 75:20 to 1:15:20, but you can format it back on the way out if you want.
- Format your string values so that they compare lexicographically, e.g. by having a fixed width padded on the left with zeros. Given the strings "007:27" and "036:59", the maximum (i.e. sorted last) will be "036:59", as you wanted.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论