Question 191515 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Run sql raw queries with Laravel 10

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?

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search