List top customers by revenue with a lens

Resources show one row per model. Sometimes you need a different shape of the same data, such as customers ranked by how much they have spent. That is what a lens is for: a custom query with its own columns, attached to an existing resource.

Generate the lens

php artisan nova:lens TopCustomers

The lens class

<?php

namespace App\Nova\Lenses;

use Illuminate\Contracts\Database\Eloquent\Builder;
use Illuminate\Contracts\Pagination\Paginator;
use Illuminate\Support\Facades\DB;
use Laravel\Nova\Fields\ID;
use Laravel\Nova\Fields\Number;
use Laravel\Nova\Fields\Text;
use Laravel\Nova\Http\Requests\LensRequest;
use Laravel\Nova\Http\Requests\NovaRequest;
use Laravel\Nova\Lenses\Lens;

class TopCustomers extends Lens
{
    public static function query(LensRequest $request, Builder $query): Builder|Paginator
    {
        return $request->withOrdering($request->withFilters(
            $query->select([
                'customers.id',
                'customers.name',
                'customers.email',
                DB::raw('count(orders.id) as orders_count'),
                DB::raw('sum(orders.total) as revenue'),
            ])
                ->join('orders', 'customers.id', '=', 'orders.customer_id')
                ->where('orders.status', '!=', 'cancelled')
                ->groupBy('customers.id', 'customers.name', 'customers.email')
                ->withCasts(['revenue' => 'float', 'orders_count' => 'integer'])
        ), fn ($query) => $query->orderBy('revenue', 'desc'));
    }

    public function fields(NovaRequest $request): array
    {
        return [
            ID::make('ID', 'id'),
            Text::make('Name', 'name'),
            Text::make('Email', 'email'),
            Number::make('Orders', 'orders_count')->sortable(),
            Number::make('Revenue', 'revenue', fn ($value) => '€'.number_format($value, 2))->sortable(),
        ];
    }

    public function filters(NovaRequest $request): array
    {
        return [];
    }

    public function uriKey(): string
    {
        return 'top-customers';
    }
}

The details that matter

• Always select the primary key. The Nova documentation requires the resource's ID in the lens query; without it, features such as "Select All Matching" and the delete menu are not available.
• Group by every non-aggregated column. MySQL 5.7+ and PostgreSQL accept customers.name when grouping by the primary key, but MariaDB and other databases in strict mode do not detect that dependency. Listing all selected columns keeps the query portable.
• withOrdering() takes a default. The closure only applies when the user has not sorted the table. The Orders and Revenue fields are sortable(), and once the user sorts by one of them, that ordering replaces the default.
• withFilters() applies the lens filters. Filters you return from the lens's filters() method receive the grouped query, so they should filter on qualified column names such as orders.created_at.
• Cast aggregates. Database drivers return sum() as a string. withCasts() turns it into a number before the display callback formats it.

Register the lens on the resource

use Laravel\Nova\Http\Requests\NovaRequest;

public function lenses(NovaRequest $request): array
{
    return [
        new Lenses\TopCustomers,
    ];
}

The lens now appears in the lens menu of the customers index under the URL segment top-customers.

Reference

• Defining lenses