SQL 在本地主机上运行，但在部署到 heroku 时抛出 SQLSTATE[42883] 错误

发布于 2025-01-14 15:30:54 字数 1384 浏览 0 评论 0原文

我有一个 laravel/vuejs 项目，刚刚部署到 heroku。除了在生产中引发 SQLSTATE[42883] 错误的查询之外，在本地和生产中一切都运行良好。 sql 查询检索每周创建的模型的计数：

public function getProductWeeklyData(){
    $products= Product::selectRaw('COUNT(*) AS product_count')
            ->selectRaw('FROM_DAYS(TO_DAYS(created_at) -MOD(TO_DAYS(created_at) -1, 7)) AS week_starting')
            ->groupBy('week_starting')
            ->orderBy('week_starting')
            ->take(10)->get();
    $products->each->setAppends([]);

    return response()->json($products, 200);
}

这在本地主机上工作并返回一个像这样的数组：

0: {product_count: 7, week_starting: "2021-10-31"}
1: {product_count: 12, week_starting: "2021-11-07"}
2: {product_count: 15, week_starting: "2021-11-14"}

这正是我想要的。但是，在部署到 heroku 并将数据库连接更改为 PostgreSQL 后，查询失败并开始返回此错误。

"message": "SQLSTATE[42883]: 未定义函数: 7 错误: 函数 to_days(无时区的时间戳) 不存在\n第 1 行: 选择 COUNT() AS Product_count,FROM_DAYS(TO_DAYS(created_at. ..\n ^\n提示：没有函数与给定的名称和参数类型匹配。您可能需要添加显式类型转换（SQL：select）。 COUNT() AS Product_count, FROM_DAYS(TO_DAYS(created_at) -MOD(TO_DAYS(created_at) -1, 7)) AS week_starting 从“products”组开始，按“week_starting”排序，按“week_starting”asc limit 10)”

我尝试按照错误中的建议铸造日期

...(created_at::text, 'YYYY-MM-DD') 但仍然出现无效查询的错误。我如何让这个查询在生产环境中与 PostgreSQL 一起使用？

原文

i have a laravel/vuejs project i just deployed to heroku. Everything works well both on local and production except for a query that is throwing up SQLSTATE[42883] error on production. The sql query retrieves a count of models created every week:

public function getProductWeeklyData(){
    $products= Product::selectRaw('COUNT(*) AS product_count')
            ->selectRaw('FROM_DAYS(TO_DAYS(created_at) -MOD(TO_DAYS(created_at) -1, 7)) AS week_starting')
            ->groupBy('week_starting')
            ->orderBy('week_starting')
            ->take(10)->get();
    $products->each->setAppends([]);

    return response()->json($products, 200);
}

This works on localhost and return an array like so:

0: {product_count: 7, week_starting: "2021-10-31"}
1: {product_count: 12, week_starting: "2021-11-07"}
2: {product_count: 15, week_starting: "2021-11-14"}

which was exactly what i wanted.
However, after deploying to heroku and changing the database connection to PostgreSQL, the query fails and start returning this error.

"message": "SQLSTATE[42883]: Undefined function: 7 ERROR: function to_days(timestamp without time zone) does not exist\nLINE 1: select COUNT() AS product_count,FROM_DAYS(TO_DAYS(created_at...\n ^\nHINT: No function matches the given name and argument types. You might need to add explicit type casts. (SQL: select COUNT() AS product_count, FROM_DAYS(TO_DAYS(created_at) -MOD(TO_DAYS(created_at) -1, 7)) AS week_starting from "products" group by "week_starting" order by "week_starting" asc limit 10)"

i tried casting the date like was suggested in the error by doing this