英文:
Run sql raw queries with Laravel 10
问题
我在控制器中有一个小查询要运行，在MySQL Workbench中运行良好
SELECT
subprocess_id,
MAX(CASE WHEN step_no = '1' THEN s_name END) AS first_step,
MAX(CASE WHEN step_no = '2' THEN s_name END) AS second_step,
MAX(CASE WHEN step_no = '3' THEN s_name END) AS third_step,
MAX(CASE WHEN step_no = '4' THEN s_name END) AS fourth_step,
MAX(CASE WHEN step_no = '5' THEN s_name END) AS fifth_step,
MAX(CASE WHEN step_no = '6' THEN s_name END) AS sixth_step
FROM
worksteps
WHERE subprocess_id = 8010
GROUP BY subprocess_id;
尝试将其翻译成Laravel，但没有成功
DB::table("worksteps")
->select("subprocess_id", DB::raw("MAX(CASE WHEN step_no = '1' THEN s_name END) AS first_step"), DB::raw("MAX(CASE WHEN step_no = '2' THEN s_name END) AS second_step"), DB::raw("MAX(CASE WHEN step_no = '3' THEN s_name END) AS third_step"), DB::raw("MAX(CASE WHEN step_no = '4' THEN s_name END) AS fourth_step"), DB::raw("MAX(CASE WHEN step_no = '5' THEN s_name END) AS fifth_step"), DB::raw("MAX(CASE WHEN step_no = '6' THEN s_name END) AS sixth_step"))
->where("subprocess_id", "=", 8010)
->groupBy("subprocess_id")
->get();
出现以下错误
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'max...
我没有一个名为max的列，我在查询中使用MAX()来避免在一个主ID下存在两个相同的步骤ID时出错。
如何在我的控制器show函数中执行这个查询？
英文:
I have a small query to run in my controller, it works fine in MySQL Workbench
SELECT
subprocess_id,
MAX(CASE WHEN step_no = '1' THEN s_name END) AS first_step,
MAX(CASE WHEN step_no = '2' THEN s_name END) AS second_step,
MAX(CASE WHEN step_no = '3' THEN s_name END) AS thrid_step,
MAX(CASE WHEN step_no = '4' THEN s_name END) AS fourth_step,
MAX(CASE WHEN step_no = '5' THEN s_name END) AS fifth_step,
MAX(CASE WHEN step_no = '6' THEN s_name END) AS sixth_step
FROM
worksteps
where subprocess_id=8010
GROUP BY
subprocess_id;
Trying to translate it to Laravel with no success
DB::table("worksteps")
->select("subprocess_id", "max (case when step_no = '1' then s_name end) as `first_step`", "max (case when step_no = '2' then s_name end) as `second_step`", "max (case when step_no = '3' then s_name end) as `thrid_step`", "max (case when step_no = '4' then s_name end) as `fourth_step`", "max (case when step_no = '5' then s_name end) as `fifth_step`", "max (case when step_no = '6' then s_name end) as `sixth_step`")
->where("subprocess_id", "=", 8010)
->groupBy("subprocess_id")
->get();
Getting the error
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'max...
I don't have a max column, I used MAX() in the query to avoid error in case there are 2 same steps IDs against one main id.
How could I execute this in my controller show function?
答案1
得分: 0
删除最大函数后的空格。
改为：
max(case
要使空格起作用，您需要使用 --sql-mode=IGNORE_SPACE。请参阅文档。
英文:
Remove the space after the max function.
Instead of:
max (case
use:
max(case
To make the space work you would need to use --sql-mode=IGNORE_SPACE. See the docmentation.
答案2
得分: 0
尝试：
DB::table("worksteps")
->select(
"subprocess_id",
DB::raw("MAX(CASE WHEN step_no = '1' THEN s_name END) AS first_step"),
DB::raw("MAX(CASE WHEN step_no = '2' THEN s_name END) AS second_step"),
DB::raw("MAX(CASE WHEN step_no = '3' THEN s_name END) AS third_step"),
DB::raw("MAX(CASE WHEN step_no = '4' THEN s_name END) AS fourth_step"),
DB::raw("MAX(CASE WHEN step_no = '5' THEN s_name END) AS fifth_step"),
DB::raw("MAX(CASE WHEN step_no = '6' THEN s_name END) AS sixth_step")
)
->where("subprocess_id", 8010)
->groupBy("subprocess_id")
->get();
请注意，这是PHP代码的一部分，用于从数据库中检索特定条件下的数据。如果您需要关于此代码的更多信息，请提出具体问题。
英文:
Try:
DB::table("worksteps")
->select(
"subprocess_id",
DB::raw("MAX(CASE WHEN step_no = '1' THEN s_name END) AS first_step"),
DB::raw("MAX(CASE WHEN step_no = '2' THEN s_name END) AS second_step"),
DB::raw("MAX(CASE WHEN step_no = '3' THEN s_name END) AS third_step"),
DB::raw("MAX(CASE WHEN step_no = '4' THEN s_name END) AS fourth_step"),
DB::raw("MAX(CASE WHEN step_no = '5' THEN s_name END) AS fifth_step"),
DB::raw("MAX(CASE WHEN step_no = '6' THEN s_name END) AS sixth_step")
)
->where("subprocess_id", 8010)
->groupBy("subprocess_id")
->get();
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论