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:
- Math Functions
- Functions for Conditions
- Functions for Date/Time
- Functions for Strings
- Functions for JSON
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!
I. Math Functions
1. SUM()
$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();2. AVG() with ROUND()
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
3. COUNT()
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
4. MAX()
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
II. Functions for Conditions
5. IF()
$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
6. IFNULL()
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();7. CASE WHEN ELSE
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();8. COALESCE()
$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
III. Functions for Date/Time
9. DATE() / YEAR() / MONTH()
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
10. DAYOFWEEK()
->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');})11. DATEDIFF()
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()'));}12. DATE_FORMAT()
$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();IV. Functions for Strings
13. lower(replace())
DB::table('roles')->update([ 'name' => DB::raw("lower(replace(`display_name`, ' ', '-'))"),]);14. CONCAT()
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();15. LOCATE()
$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
16. LENGTH()
$accounts = $this->accountService->getAccountBalances($startDate, $endDate) ->orderByRaw('LENGTH(code), code') ->get();https://github.com/andrewdwallo/erpsaas/blob/3.x/app/Services/ReportService.php#L50
17. FIELD()
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
18. REGEXP()
if ($this->letter == '.') { $query->whereRaw($this->letterIndexColumn() . ' REGEXP \'^[^a-zA-Z]*$\'');} else { $query->whereLike($this->letterIndexColumn(), $this->letter . '%');}V. Functions for JSON
19. JSON_REMOVE() with JSON_SET()
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())'),]);20. JSON_UNQUOTE() with JSON_EXTRACT()
$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
![[NEW] Flutter 3 Mobile App with Laravel 12 API](../storage/929/conversions/900-600-laravel-(51)-front_grid.jpg)
-front_grid.jpg)
-front_grid.jpg)
No comments yet…