20 MySQL Functions Examples from Laravel Projects

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:

  1. Math Functions
  2. Functions for Conditions
  3. Functions for Date/Time
  4. Functions for Strings
  5. 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();

https://github.com/pterodactyl/panel/blob/1.0-develop/app/Repositories/Eloquent/NodeRepository.php#L57

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...

The full tutorial [10 mins, 1966 words] is only for Premium Members

Login Or Become a Premium Member for $129/year or $29/month
What else you will get:
  • 68 courses (1183 lessons, total 43 h 18 min)
  • 90 long-form tutorials (one new every week)
  • access to project repositories
  • access to private Discord

Recent New Courses