我的Eloquent没有选择正确的时间数据,请帮助我理解。

huangapple go评论66阅读模式
英文:

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没有选择正确的时间数据,请帮助我理解。

我已经尝试将列内容从字符串转换为时间,但仍然不起作用,同时我也尝试使用一些查询构建器,但仍然与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

Picture for context
我的Eloquent没有选择正确的时间数据,请帮助我理解。

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.

huangapple
  • 本文由 发表于 2023年2月24日 15:04:18
  • 转载请务必保留本文链接:https://go.coder-hub.com/75553486.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定