datajoe/postgresql-distinct-on
Adds PostgreSQL DISTINCT ON support to Laravel's query builder and Eloquent, so you can fetch the first row per group with a clean, chainable API. Ideal for latest-per-user or per-category queries without raw SQL or subqueries.
Install
composer require datajoe/postgresql-distinct-on
The package targets Laravel 5–8, so service-provider registration depends on your version:
php artisan package:discover output.config/app.php under providers.Where to look first
README.md — confirm the exact surface area. Most likely you get either a query-builder macro (e.g. ->distinctOn([...])) or a trait/scope meant to be added to Eloquent models. Method names matter; don't guess.composer.json — check the supported Laravel constraint so you know whether it will even install on your framework version.src/ — the implementation is almost certainly small (a macro + a service provider). Read it once; you'll want to know exactly what SQL it emits before trusting it in production.First use case — "latest row per group"
The canonical reason to reach for DISTINCT ON is fetching the most recent row per group without window functions or N+1 queries:
// Latest order per customer
$latest = DB::table('orders')
->distinctOn('customer_id')
->orderBy('customer_id')
->orderByDesc('created_at')
->get();
Compare the emitted SQL via ->toSql() against the equivalent:
SELECT DISTINCT ON (customer_id) *
FROM orders
ORDER BY customer_id, created_at DESC;
That "does the generated SQL match what I'd hand-write" check is the single most valuable five minutes you'll spend with this package.
1. Wrap it in a model scope
Don't sprinkle distinctOn() through controllers. Encapsulate the ordering invariant (which is easy to get wrong — see Gotchas) in one place:
public function scopeLatestPerCustomer($query)
{
return $query
->distinctOn('customer_id')
->orderBy('customer_id')
->orderByDesc('created_at');
}
// Usage
Order::latestPerCustomer()->where('status', 'paid')->get();
2. Latest message per conversation (inbox pattern)
The classic day-to-day use case: an inbox or activity feed showing the last event per thread. The pattern is identical — distinctOn(thread_id), order by thread_id then created_at DESC, then paginate. This replaces what would otherwise be a correlated subquery or a ROW_NUMBER() OVER (...) window function.
3. Combine with Eloquent and relations
Because it's a query-builder modifier, it composes with with() eager loading normally — the DISTINCT ON applies to the parent result set, and relations are hydrated per-row as usual:
Order::latestPerCustomer()
->with('customer', 'lineItems')
->get();
If the result surprises you (duplicate parents, missing relations), that's usually an ORDER BY problem, not an eager-loading problem.
4. Multi-column grouping with a chosen winner
DISTINCT ON accepts multiple columns, which the package should expose as an array. This is useful for "one row per (tenant, category)" style reporting where you still need the freshest version:
->distinctOn(['tenant_id', 'category_id'])
->orderBy('tenant_id')
->orderBy('category_id')
->orderByDesc('updated_at')
5. Fallback path when the package is unavailable
Because this package has essentially zero adoption, keep a safety net. A tiny custom macro in an AppServiceProvider is a 10-line replacement:
Builder::macro('distinctOn', function ($columns) {
$columns = (array) $columns;
return $this->distinct()->selectRaw(
'DISTINCT ON ('.implode(', ', $columns).') *'
);
});
If you find yourself fighting the package's API, swapping to this macro is cheap and gives you full control over the generated SQL.
The ORDER BY contract is the #1 source of bugs. PostgreSQL requires that the DISTINCT ON expressions appear as the leading ORDER BY terms. If you write ->distinctOn('customer_id')->orderByDesc('created_at'), Postgres will raise an error ("SELECT DISTINCT ON expressions must match initial ORDER BY expressions"). Always order by the DISTINCT ON columns first, then your tiebreaker.
"Which row wins?" is decided entirely by ORDER BY. The first row per group under the ordering is the one returned. There is no created_at DESC default, no dedup heuristic, no fallback. If you omit the tiebreaker, you'll get an arbitrary row and it will look correct in development and wrong in production.
It is PostgreSQL-only, non-standard SQL. You cannot run these queries against SQLite or MySQL. Two consequences:
Pagination is subtle. ->paginate() wraps your query in a COUNT(*) subquery; with DISTINCT ON the count reflects post-distinct rows, which is what you want — but confirm it, because an incorrectly built wrapper can count pre-distinct rows. Run one paginated query and compare total() against the actual row count.
Compatibility floor. The package caps at Laravel 8. On Laravel 9+ it may still install (macros are stable) but is unsupported; watch for composer conflicts during upgrades. This is a good moment to decide whether to vendor the macro from the previous section instead of depending on an unmaintained package.
Debugging workflow. When results look wrong, drop the Eloquent layer immediately:
$q = Order::latestPerCustomer()->where('status', 'paid');
dump($q->toSql(), $q->getBindings());
Paste that into psql and inspect. Nine times out of ten the generated SQL is fine and the problem is your ORDER BY, or you've applied distinctOn() after a groupBy() / aggregate that changed the row shape.
Interaction with select(). If you narrow the column list after distinctOn(), confirm the package's selectRaw('DISTINCT ON (...) *') still works or is overridden correctly. Safer: call select() before distinctOn(), or verify the output SQL — some implementations clobber the *.
Extension point. The service provider is the place to register or override macros. If you need a variant (distinctOnRaw, or one that appends the ORDER BY invariant automatically), register it there rather than forking the package — you keep composer update working.
Adoption reality check. Zero stars and zero dependents means no community scrutiny. Read the src/ before putting it in a hot path, pin the version in composer.json, and have the inline-macro fallback ready. The feature it enables is genuinely valuable — DISTINCT ON is the cleanest way to express "latest per group" in Postgres — but the wrapper is small enough that owning it yourself is a legitimate choice.
How can I help you explore Laravel packages today?