I am having an issue filtering a MySql query through HAVING on a computed column based on a MySql variable.

I am having an issue in Laravel Elquent where a query I am running is producing no results.

I have found the offending part of the query and have dumbed down the query as much as I can as a test.

Executing this code, in Laravel, yields the result “0”:

    dd(DB::table('timesheets')->select([
        'timesheets.*',
        DB::raw('(@test := 1) AS test1'),
        DB::raw('@test AS test2'),
    ])->havingRaw('test2 = 1')->count());

But if I output the SQL:

    dd(DB::table('timesheets')->select([
        'timesheets.*',
        DB::raw('(@test := 1) AS test1'),
        DB::raw('@test AS test2'),
    ])->havingRaw('test2 = 1')->toSql());

select timesheets.*, (@test := 1) AS test1, @test AS test2 from timesheets having test2 = 1

And run this query directly against my MySql instance using tableplus, I get the results I expect.

If I alter the query to be:

    dd(DB::table('timesheets')->select([
        'timesheets.*',
        DB::raw('1 AS test1'),
        DB::raw('1 AS test2'),
    ])->havingRaw('test2 = 1')->count());

Then I get the result “66503” which is correct.

It seems that there is something within Laravel that doesn’t like the variables within the select statements, used in conjunction with MySql’s HAVING, but my query relies heavily on this.

I’d be grateful for any help as I am tearing my hair out with this one!