英文:
selectRaw for MONTH(created_at) doesn't work running a test
问题
我在Laravel 10 User模型中有一个函数，用于计算每月的计算平均值并在视图上显示。这对我的应用程序很有效，但当我运行视图的assertStatus测试时，会显示错误。
public function numCalculationsAvgMonth(): string
{
$data = NumberOfCalculations::where('user_id', $this->id)
->selectRaw('
count(id) as data,
YEAR(created_at) as year,
MONTH(created_at) as month
')
->groupby('year', 'month')
->get()
->avg('data');
return decimals($data, 2);
}
测试的结果如下：
SQLSTATE[HY000]: General error: 1 no such function: YEAR (Connection: sqlite, SQL: select
count(id) as data,
YEAR(created_at) as year,
MONTH(created_at) as month
from "number_of_calculations" where "user_id" = 1 group by "year", "month")
我需要在我的测试案例中使用一个trait吗？
英文:
I have a function in Laravel 10 User model that calculates an AVG of calculations monthly and this on a view. It works fine for my application, but when I run a test with assertStatus of this view, an error is shown.
public function numCalculationsAvgMonth(): string
{
$data = NumberOfCalculations::where('user_id', $this->id)
->selectRaw('
count(id) as data,
YEAR(created_at) as year,
MONTH(created_at) as month
')
->groupby('year', 'month')
->get()
->avg('data');
return decimals($data, 2);
}
The result of my test is the next:
SQLSTATE[HY000]: General error: 1 no such function: YEAR (Connection: sqlite, SQL: select
count(id) as data,
YEAR(created_at) as year,
MONTH(created_at) as month
from "number_of_calculations" where "user_id" = 1 group by "year", "month")
I need to use a trait in my TestCase?
答案1
得分: 0
错误信息 "no such function: YEAR" 表示测试环境不支持所使用的 selectRaw() 方法中的 YEAR() 函数。解决方法是使用 SQLite 支持的 strftime() 函数，可以用于从日期中提取年份和月份。
这是一个可能使用 strftime() 并返回浮点数而非字符串的 numCalculationsAvgMonth() 函数的版本:
public function numCalculationsAvgMonth(): float
{
$data = NumberOfCalculations::where('user_id', $this->id)
->selectRaw('count(id) as data, strftime("%Y", created_at) as year, strftime("%m", created_at) as month')
->groupBy('year', 'month')
->get()
->avg('data');
return round($data, 2);
}
英文:
The error message "no such function: YEAR" indicates that the test environment simply does not support the YEAR() function used in the selectRaw() method. One solution is to use the strftime() function instead, which is supported by SQLite and can be used to extract the year and month from a date.
Here's a possible version of the numCalculationsAvgMonth() function that uses strftime() and returns a float instead of a string:
public function numCalculationsAvgMonth(): float
{
$data = NumberOfCalculations::where('user_id', $this->id)
->selectRaw('count(id) as data, strftime("%Y", created_at) as year, strftime("%m", created_at) as month')
->groupBy('year', 'month')
->get()
->avg('data');
return round($data, 2);
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论