sagrmore/laravel-sql-window

A lightweight Laravel-native SQL window function expression builder.

Maintainers

Package info

github.com/sagrmore/laravel-sql-window

pkg:composer/sagrmore/laravel-sql-window

Transparency log

Statistics

Installs: 0

Dependents: 0

Suggesters: 0

Stars: 0

Open Issues: 0

Security

Advisories: 0

Aikido package health analysis

v1.0.0 2026-08-06 05:17 UTC

Requires

Requires (Dev)

MIT 9870ed7746b580996d440433c2ff54ab1142ef19

sqlmysqlpostgresqllaravelwindow-functions

Published on Packagist.org on 2026-08-06 05:51 UTC

This package is auto-updated.

Last update: 2026-08-06 05:51:31 UTC

README

A lightweight Laravel-native SQL window function expression builder.

Laravel already makes it easy to build queries — but window functions usually mean dropping into raw SQL. This package gives you a fluent, immutable API that works inside select() like any other Laravel expression.

Why?

Before

use Illuminate\Support\Facades\DB;

DB::table('employees')
    ->select('name')
    ->selectRaw(
        'ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank'
    )
    ->get();

After

use Illuminate\Support\Facades\DB;
use Sagrmore\LaravelSqlWindow\Window;

DB::table('employees')
    ->select([
        'name',
        Window::rowNumber()
            ->partitionBy('department')
            ->orderByDesc('salary')
            ->as('rank'),
    ])
    ->get();

Same query. No raw window SQL.

Why not selectRaw()?

selectRaw() works. This package is for when you want window functions to feel like the rest of Laravel:

  • Fluent API that mirrors query builder conventions (orderBy, orderByDesc)
  • Immutable expressions — every modifier returns a new instance
  • Laravel-native integration via the Expression contract (drops into select())
  • Identifier wrapping through the active connection grammar
  • Clearer, easier-to-maintain queries as window logic grows

It does not replace Laravel’s query builder. It only builds the window expression.

Requirements

  • PHP 8.3+
  • Laravel 11 or 12 (illuminate/database ^11|^12)
  • A database that supports ANSI window functions: MySQL 8+ or PostgreSQL

Installation

composer require sagrmore/laravel-sql-window

No service provider. No config file. Require the package and use the Window class.

Usage

All factories return an immutable expression that implements Laravel’s Expression contract and works inside select().

Ranking

Window::rowNumber()->partitionBy('department')->orderByDesc('salary')->as('rank');
Window::rank()->orderBy('score');
Window::denseRank()->partitionBy('team')->orderByDesc('points');

Offset

Window::lag('salary')->orderBy('hired_at');
Window::lag('salary', 2, 0)->orderBy('hired_at');
Window::lead('salary', 1)->orderBy('hired_at');

For lag() / lead(), a null default omits the SQL default argument (e.g. LAG("salary", 1)). It does not emit LAG(..., NULL).

Aggregates

Window::sum('amount')->partitionBy('account_id');
Window::avg('score')->partitionBy('class_id')->orderBy('created_at');
Window::count()->partitionBy('department');
Window::count('id')->partitionBy('department');
Window::min('price')->partitionBy('category');
Window::max('price')->partitionBy('category');

Modifiers

Method Purpose
partitionBy(...$columns) Set PARTITION BY columns
orderBy(...$columns) Append ORDER BY … ASC
orderByDesc(...$columns) Append ORDER BY … DESC
as($alias) Select alias

Prefer ->as('alias') when placing the expression in select([...]). Do not also alias via selectExpression() or a string array key, or the SQL may be double-aliased.

Generated SQL uses the active connection grammar’s wrap() for identifiers.

Example output (MySQL-style quoting):

ROW_NUMBER() OVER (
    PARTITION BY `department`
    ORDER BY `salary` DESC
) as `rank`

Real-world examples

Rank employees by salary within each department

DB::table('employees')
    ->select([
        'id',
        'name',
        'department',
        'salary',
        Window::rowNumber()
            ->partitionBy('department')
            ->orderByDesc('salary')
            ->as('rank'),
    ])
    ->get();

Running total of payments per account

DB::table('payments')
    ->select([
        'account_id',
        'paid_at',
        'amount',
        Window::sum('amount')
            ->partitionBy('account_id')
            ->orderBy('paid_at')
            ->as('running_total'),
    ])
    ->get();

Rank customers by revenue

DB::table('orders')
    ->select([
        'customer_id',
        Window::sum('total')
            ->partitionBy('customer_id')
            ->as('revenue'),
        Window::rank()
            ->orderByDesc('total')
            ->as('revenue_rank'),
    ])
    ->get();

Top N records per group

Use a window rank in a subquery, then filter:

$ranked = DB::table('products')
    ->select([
        'id',
        'category_id',
        'name',
        'sales',
        Window::rowNumber()
            ->partitionBy('category_id')
            ->orderByDesc('sales')
            ->as('rank'),
    ]);

DB::query()
    ->fromSub($ranked, 'ranked_products')
    ->where('rank', '<=', 3)
    ->get();

Scope (v1.0)

Supported

Factory SQL
rowNumber() ROW_NUMBER()
rank() RANK()
denseRank() DENSE_RANK()
lag() LAG()
lead() LEAD()
sum() SUM()
avg() AVG()
count() COUNT()
min() MIN()
max() MAX()

Modifiers: partitionBy(), orderBy(), orderByDesc(), as().

Intentionally not included

These are deliberate design decisions for v1.0 — not unfinished work:

  • Frame clauses (ROWS BETWEEN …)
  • Named windows (WINDOW w AS (…))
  • SQL parser / AST
  • Query builder replacement, macros, or grammar overrides
  • Service provider / container bindings

Performance

This package only generates SQL expression strings.

  • No service provider is registered
  • No runtime services are resolved from the container
  • Laravel’s query builder is not replaced or wrapped
  • Overhead is effectively the cost of building a small immutable object and concatenating SQL

There is nothing to “boot” at application start beyond Composer autoloading.

FAQ

Why not use selectRaw()?
You can. Use this package when you want fluent, readable window expressions that stay consistent with Laravel’s query builder style and wrap identifiers through the connection grammar.

Does this replace Laravel’s query builder?
No. It only produces expression objects for use inside existing builder methods like select().

Which databases are supported?
Documented targets are MySQL 8+ and PostgreSQL. The package emits ANSI-style window SQL; identifier quoting follows your active Laravel grammar.

Does it support every SQL window function?
No. v1.0 covers the functions listed in Scope. Additional functions may be considered in later releases.

Why are some SQL features intentionally omitted?
To keep the package small, predictable, and easy to maintain. Frame clauses, named windows, and dialect-specific behavior are out of scope for v1.0 by design.

Versioning

This package follows Semantic Versioning. The public API in v1.0.0 is stable; new features will be additive.

See CHANGELOG.md for release notes.

Documentation

Full developer guide (installation, function reference, recipes, FAQ):

Copy-paste examples and real-world recipes live in docs/recipes/ (there is no separate examples/ directory).

Contributing

See CONTRIBUTING.md. Please also read our Code of Conduct and Security Policy.

Development

composer test      # Pest
composer analyse   # PHPStan
composer format    # Laravel Pint

License

MIT