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