.png-674b51867de31.jpg)
Laravel developers are used to Eloquent for DB operations but often need raw SQL queries to achieve their goals. In this tutorial, we gathered 20 open-source examples of Laravel projects that use MySQL-specific functions.
We grouped the functions into five sections:
Remember that these are MySQL functions, so if you plan to use another DB engine like SQLite or PostgreSQL, check their docs to see if the corresponding methods exist.
Below, you will see examples of Laravel code with raw queries. I will not comment on them, as the code is pretty readable and straightforward, but if you want to find out the context, the links to the repositories are included below all the code snippets.
Let's go!
$stats = $this->getBuilder()->select( $this->getBuilder()->raw('IFNULL(SUM(servers.memory), 0) as sum_memory, IFNULL(SUM(servers.disk), 0) as sum_disk'))->join('servers', 'servers.node_id', '=', 'nodes.id')->where('node_id', $node->id)->first();private function metrics( Server $server, Carbon $fromDate, Carbon $toDate, ?Expression $interval = null): Collection { return DB::table('metrics') ->where('server_id', $server->id) ->whereBetween('created_at', [$fromDate->format('Y-m-d H:i:s'), $toDate->format('Y-m-d H:i:s')]) ->select( [ DB::raw('created_at as date'), DB::raw('ROUND(AVG(load), 2) as load'), DB::raw('ROUND(AVG(memory_total), 2) as memory_total'), DB::raw('ROUND(AVG(memory_used), 2) as memory_used'), DB::raw('ROUND(AVG(memory_free), 2) as memory_free'), DB::raw('ROUND(AVG(disk_total), 2) as disk_total'), DB::raw('ROUND(AVG(disk_used), 2) as disk_used'), DB::raw('ROUND(AVG(disk_free), 2) as disk_free'), $interval, ], ) ->groupByRaw('date_interval') ->orderBy('date_interval') ->get() ->map(function ($item) { $item->date = Carbon::parse($item->date)->format('Y-m-d H:i'); return $item; });}https://github.com/vitodeploy/vito/blob/2.x/app/Actions/Monitoring/GetMetrics.php#L48
public static function getTopCountries(): Collection{ return Postcard::query() ->select(['country', DB::raw('COUNT(country) as postcard_count')]) ->groupBy('country') ->orderByDesc('postcard_count') ->take(3) ->get() ->map(function (Postcard $postcard) { return [ 'name' => $postcard->country, 'postcard_count' => $postcard->postcard_count, ]; });}https://github.com/spatie/spatie.be/blob/main/app/Models/Postcard.php#L62
public function scopeWithLastActivityAt(Builder $query){ $query->addSelect(['activities.created_at as last_activity_at']) ->leftJoinSub(function (\Illuminate\Database\Query\Builder $query) { $query->from('activities')->select('user_id') ->selectRaw('max(created_at) as created_at') ->groupBy('user_id'); }, 'activities', 'users.id', '=', 'activities.user_id');}https://github.com/BookStackApp/BookStack/blob/development/app/Users/Models/User.php#L301
$query = Tag::query() ->select([ 'name', ($searchTerm || $nameFilter) ? 'value' : DB::raw('COUNT(distinct value) as `values`'), DB::raw('COUNT(id) as usages'), DB::raw('SUM(IF(entity_type = \'page\', 1, 0)) as page_count'), DB::raw('SUM(IF(entity_type = \'chapter\', 1, 0)) as chapter_count'), DB::raw('SUM(IF(entity_type = \'book\', 1, 0)) as book_count'), DB::raw('SUM(IF(entity_type = \'bookshelf\', 1, 0)) as shelf_count'), ]) ->orderBy($sort, $listOptions->getOrder()) ->whereHas('entity');https://github.com/BookStackApp/BookStack/blob/development/app/Activity/TagRepo.php#L36
The same example as for the SUM() above, but this time used to emphasize the conditional IFNULL() function.
$stats = $this->getBuilder()->select( $this->getBuilder()->raw('IFNULL(SUM(servers.memory), 0) as sum_memory, IFNULL(SUM(servers.disk), 0) as sum_disk'))->join('servers', 'servers.node_id', '=', 'nodes.id')->where('node_id', $node->id)->first();Potentially think an easier example
$data = DB::table('purchases') ->select([ DB::raw(" CASE WHEN products.title = purchasables.title THEN purchasables.title ELSE CONCAT(products.title, ': ', purchasables.title) END as title "), DB::raw("date_format(purchases.created_at, '$intervalFormat') as day"), DB::raw('sum(quantity) as count'), ]) ->where('earnings', '>', '0') ->join('purchasables', 'purchasables.id', '=', 'purchases.purchasable_id') ->join('products', 'products.id', '=', 'purchasables.product_id') ->where('purchases.created_at', '>=', now()->subMonth()) ->groupByRaw("title, day") ->get();$query = Photo::query() ->where('user_id', '!=', 5292) // temp ->with([ 'user:id,name,username,show_username_maps,show_name_maps,settings', 'user.team:is_trusted', 'team:id,name', 'customTags:photo_id,tag', 'adminVerificationLog.admin' => function ($q) { $q->addSelect('id', 'show_name', 'show_username') ->addSelect(DB::raw('CASE WHEN show_name = 1 THEN name ELSE NULL END as name')) ->addSelect(DB::raw('CASE WHEN show_username = 1 THEN username ELSE NULL END as username')); } ]);$meta = Auth::user()->movies()->toBase() ->selectRaw("count(case when watched_status is not null then 1 end) as watched") ->selectRaw("count(case when watched_status is null then 1 end) as unWatched") ->selectRaw("count(case when downloaded_status is not null then 1 end) as downloaded") ->selectRaw("count(case when downloaded_status is null then 1 end) as notDownloaded") ->selectRaw("count(case when my_rating < 34 then 1 end) as lowPreferred") ->selectRaw("count(case when my_rating between 34 and 66 then 1 end) as mediumPreferred") ->selectRaw("count(case when my_rating > 66 then 1 end) as highPreferred") ->selectRaw("count(1) as 'all'") ->first();$this->comments = $this->item ->comments() ->withWhereHas('user:id,name,email') ->orderByRaw('COALESCE(parent_id, id), parent_id IS NOT NULL, id') ->when(!auth()->user()?->hasAdminAccess(), fn ($query) => $query->where('private', false)) ->get() ->mapToGroups(function ($comment) { return [(int)$comment->parent_id => $comment]; });https://github.com/ploi/roadmap/blob/main/app/Livewire/Item/Comments.php#L118
return $query->orderByRaw('COALESCE(actual_start_time, scheduled_start_time) DESC');https://github.com/christophrumpel/larastreamers/blob/main/app/Models/Stream.php#L146
if ($this->properties->period === 'today') { $spent = Spending::query() ->where('space_id', session('space_id')) ->whereRaw('DATE(happened_on) = ?', [date('Y-m-d')]) ->sum('amount');} if ($this->properties->period === 'this_week') { $monday = date('Y-m-d', strtotime('monday this week')); $sunday = date('Y-m-d', strtotime('sunday this week')); $spent = Spending::query() ->where('space_id', session('space_id')) ->whereRaw('DATE(happened_on) >= ? AND DATE(happened_ON) <= ?', [$monday, $sunday]) ->sum('amount');} if ($this->properties->period === 'this_month') { $spent = Spending::query() ->where('space_id', session('space_id')) ->whereRaw('YEAR(happened_on) = ? AND MONTH(happened_on) = ?', [date('Y'), date('n')]) ->sum('amount');}https://github.com/range-of-motion/budget/blob/master/app/Widgets/Spent.php#L24
->modifyQueryUsing(function (Builder $query) { $query->selectRaw('id, DATE_FORMAT(date, "%Y-%m-%d") as week_start') ->whereRaw('DAYOFWEEK(date) = 2') // Filter for Mondays only ->distinct() ->groupBy('week_start', 'date', 'id');})if (request()->input('expired_quotes.in') == 1) { $this->addFilter('expired_quotes', DB::raw('DATEDIFF(NOW(), '.$tablePrefix.'quotes.expired_at) >= '.$tablePrefix.'NOW()'));} else { $this->addFilter('expired_quotes', DB::raw('DATEDIFF(NOW(), '.$tablePrefix.'quotes.expired_at) < '.$tablePrefix.'NOW()'));}$query = Transaction::query() ->whereHas('brand', function ($query) use($brandId) { return $query->where('id', $brandId); }) ->select(DB::raw("date_format(created_at, '%Y-%m') as label, SUM(transactions.amount) as value")) ->groupBy(DB::raw("label")) ->orderBy("label"); if($rangeData) { $query->whereBetween('transactions.created_at', [$rangeData->start(), $rangeData->end()]);} return $query->get();https://github.com/hisabi-app/hisabi/blob/main/app/GraphQL/Queries/TotalPerBrandTrend.php#L45
$moviesHistory = Auth::user()->movies()->filter()->toBase() ->select(DB::raw("date_format(movies.created_at, '%m') as month, COUNT(*) as total")) ->whereBetween('movies.created_at', [ $startDate, $endDate, ]) ->groupBy('month') ->orderBy('month') ->get();DB::table('roles')->update([ 'name' => DB::raw("lower(replace(`display_name`, ' ', '-'))"),]);The same example as used above for CASE WHEN ELSE just this time emphasizing the CONCAT() function.
$data = DB::table('purchases') ->select([ DB::raw(" CASE WHEN products.title = purchasables.title THEN purchasables.title ELSE CONCAT(products.title, ': ', purchasables.title) END as title "), DB::raw("date_format(purchases.created_at, '$intervalFormat') as day"), DB::raw('sum(quantity) as count'), ]) ->where('earnings', '>', '0') ->join('purchasables', 'purchasables.id', '=', 'purchases.purchasable_id') ->join('products', 'products.id', '=', 'purchasables.product_id') ->where('purchases.created_at', '>=', now()->subMonth()) ->groupByRaw("title, day") ->get();$this->ingredients = Ingredient::where('name', 'like', '%' . $this->query . '%') ->orderByRaw('LOCATE(\'' . $this->query . '\', name)') ->take(4) ->get() ->toArray();https://github.com/JustinByrne/Mealing/blob/main/app/Http/Livewire/Recipes/Create.php#L119
$accounts = $this->accountService->getAccountBalances($startDate, $endDate) ->orderByRaw('LENGTH(code), code') ->get();https://github.com/andrewdwallo/erpsaas/blob/3.x/app/Services/ReportService.php#L50
return $query->when($vacancy->emigrate, function($query) use ($vacancy) { return $query->orderByRaw('FIELD(`users`.`country`, ?) DESC', [$vacancy->country]);});https://github.com/caneara/lumeno/blob/main/app/Search/Sorters/EmigrationSorter.php#L18
if ($this->letter == '.') { $query->whereRaw($this->letterIndexColumn() . ' REGEXP \'^[^a-zA-Z]*$\'');} else { $query->whereLike($this->letterIndexColumn(), $this->letter . '%');}DB::table('eggs')->update([ 'config_startup' => DB::raw('JSON_REMOVE(config_startup, \'$.userInteraction\')'),]);DB::table('eggs')->update([ 'config_startup' => DB::raw('JSON_SET(config_startup, \'$.userInteraction\', JSON_ARRAY())'),]);$results = Purchase::query() ->whereHas('receipt', function (Builder $query): void { $query->where('amount', '!=', 0); }) ->select([DB::raw('COUNT(*) as count'), DB::raw("JSON_UNQUOTE(JSON_EXTRACT(paddle_webhook_payload, '$.payment_method')) as payment_method")]) ->where('purchases.created_at', '>=', $startDate) ->groupBy('payment_method') ->orderByDesc('count') ->get();https://github.com/spatie/spatie.be/blob/main/app/Filament/Widgets/PaymentMethodsWidget.php#L30
.png-674b51902982e.jpg)
