Home>

Laravel 5.8 creates SQL using Eloquent.

Originally

SELECT
    hoge1_column
  , hoge2_column
  , hoge3_column
  , LEAST (COALESCE (MIN (hoge1_flg), 99), COALESCE (MIN (hoge2_flg), 99), COALESCE (MIN (hoge3_flg), 99)) AS hoge_status
FROM
  hoge
LEFT JOIN
  hoge1 ON hoge.fuga = hoge1.fuga
LEFT JOIN
  hoge2 ON hoge.fuga = hoge2.fuga
LEFT JOIN
  hoge3 ON hoge.fuga = hoge3.fuga
WHERE
  hoge_flg = 1
GROUP BY
  hoge1_column
HAVING
  hoge3_column = 1 OR (hoge2_column = 0 AND hoge_status = 1);


However, I created it with Eloquent as follows in order to put the conditions after having in scope.

self :: selectRaw ('hoge1_column, hoge2_column, hoge3_column')
    ->selectRaw ('LEAST (COALESCE (MIN (hoge1_flg), 99), COALESCE (MIN (hoge2_flg), 99), COALESCE (MIN (hoge3_flg), 99)) AS hoge_status')
    ->leftJoin ('hoge1', 'hoge.fuga', '=', 'hoge1.fuga')
    ->leftJoin ('hoge2', 'hoge.fuga', '=', 'hoge2.fuga')
    ->leftJoin ('hoge3', 'hoge.fuga', '=', 'hoge2.fuga')
    ->groupBy ('hoge1_column')
    ->get ();


To add a condition here

having ('hoge3_column', '=', '1')


Because adding Unknown column came out

where ('hoge3_column', '=', '1')


Changed because I saw it in stackovweflow to use and where.
It went well,
To use hoge_status

havingRaw ('hoge2_column = 0 AND hoge_status = 1')


An Unknown column appears.
The same error appears even if you change to whereRaw.

There are both hoge2 and hoge_status in the Model attribute of Collection acquired without adding the above conditions.

How do I write a where or having condition?

  • Answer # 1

    SELECT
        hoge1_column
      , hoge2_column
      , hoge3_column
      , LEAST (COALESCE (MIN (hoge1_flg), 99), COALESCE (MIN (hoge2_flg), 99), COALESCE (MIN (hoge3_flg), 99)) AS hoge_status
    FROM
      hoge
    LEFT JOIN
      hoge1 ON hoge.fuga = hoge1.fuga
    LEFT JOIN
      hoge2 ON hoge.fuga = hoge2.fuga
    LEFT JOIN
      hoge3 ON hoge.fuga = hoge3.fuga
    WHERE
      hoge_flg = 1
    GROUP BY
      hoge1_column


    To Eloquent's from

    hoge3_column = 1 OR hoge2_column = 0 AND hoge_status = 1


    I was able to get the intended result by putting in the where clause.
    However, I cannot say anything because it is not a smart method, but for the time being I was able to achieve my purpose.