PHP

laravel select raw where

 User::query()
                ->where('users.ban', '!=', 1)
                ->where('users.rights', '=', 1)
                ->leftJoin('users as referal', 'users.id', '=', 'referal.ref_id')
                ->whereNotNull('referal.id')
                ->select([
                    'users.id',
                    'users.name',
                    'users.telegram_id',
                    DB::raw('count(referal.id) as total_referal'),
                    DB::raw("(SELECT COUNT(ac_r.id) FROM users as ac_r WHERE ac_r.ref_id=users.id AND ac_r.ref_bonus = 1) as active_referal"),
                    DB::raw("(SELECT COUNT(ac_r.id) FROM users as ac_r WHERE ac_r.ref_id=users.id AND ac_r.ref_bonus != 1) as no_active_referal"),
                    DB::raw("((SELECT COUNT(ac_r.id) FROM users as ac_r WHERE ac_r.ref_id=users.id AND ac_r.ref_bonus = 1) * ".User::REFERAL_BONUS.") as total_money_referal"),
                ])
                ->groupBy('users.id')
   ->orderByDesc('total_referal')->paginate(100);

eloquent where raw

$query->whereRaw("(
        LOWER(name)             like '%". strtolower($search) ."%' OR
        LOWER(sku)              like '%". strtolower($search) ."%' OR
        LOWER(codigo)           like '%". strtolower($search) ."%' OR
        LOWER(descricao_curta)  like '%". strtolower($search) ."%'
    )");

eloquent where raw

/*
whereRaw / orWhereRaw
The whereRaw and orWhereRaw methods can 
be used to inject a raw "where" clause into your query. 
These methods accept an optional array of bindings as their second argument:
*/
$orders = DB::table('orders')
                ->whereRaw('price > IF(state = "TX", ?, 100)', [200])
                ->get();