For some reason, Eloquent puts bindings used in selectRaw to all where clauses.

DB::query()
            ->selectRaw(
                "tr.user_id,
                SUM(
                    CASE WHEN tr.updated_at >= :date
                ) as inc_today,
                ",
                [
                    ':date' => 'test'
                ]
            )
            ->from('transactions as tr')
            ->where('tr.type', '=', 'type1')
            ->where('tr.status', '=', 'success')
            ->groupBy(['tr.user_id'])
            ->orderBy('income', $orderBy)
            ->paginate(
                perPage: $perPage,
                page: $page
            );

SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters (SQL: select count(*) as aggregate from (select tr.user_id,\n SUM(\n CASE WHEN tr.updated_at >= :date\n ) as inc_today,\n from transactions as tr where tr.type = test and tr.status = type1 group by tr.user_id) as aggregate_table)

It puts test to tr.type instead of putting it to selectRaw

I expect that Eloquent will put my binding for selectRaw directly to selectRaw and not to where clauses

CodePudding user response：

The error refers to mixed named and positional parameters - Laravel by default uses ? for the placeholders in the where clauses. So your prepared query really looks like

 select count(*) as aggregate from 
     (select tr.user_id,
       SUM(  CASE WHEN tr.updated_at >= :date ) as inc_today,
 from transactions as tr 
 where tr.type = ? and tr.status = ? group by tr.user_id) as aggregate_table

The database doesn't like mixing the uses. Use the question mark instead of the named parameter for your selectRaw clause

DB::query()
        ->selectRaw(
            "tr.user_id,
            SUM(
                CASE WHEN tr.updated_at >= ?
            ) as inc_today
            ",
            [
                'test'
            ]
        )
        ->from('transactions as tr')
        ->where('tr.type', '=', 'type1')
        ->where('tr.status', '=', 'success')
        ->groupBy(['tr.user_id'])
        ->orderBy('income', $orderBy)
        ->paginate(
            perPage: $perPage,
            page: $page
        );

I'd also suggest removing that trailing comma in your selectRaw clause.