staudenmeir/eloquent-eager-limit-x-laravel-cte
A Laravel package that combines eager limit loading with common table expressions, letting you limit eager-loaded relationships per parent model using CTEs. Useful for fetching only the latest or top N related records for each parent in a single query.
1. Clear the database floor before writing any code. The package cannot deliver either of its two behaviours without window functions and CTEs: MySQL 8.0+, PostgreSQL, SQLite 3.25+, or SQL Server. Check the actual production engine, not your local Docker image:
php artisan tinker --execute="dump(DB::selectOne('select version()'));"
sqlite3 --version # locally and in CI
A MySQL 5.7 or MariaDB < 10.2 estate is a hard stop — that's a hosting roadmap conversation, not a Composer problem.
2. Pin the exact version. Last tag is 1.1.1 (Feb 2023), single maintainer, zero dependents. Don't let ^1.1 float:
composer require staudenmeir/eloquent-eager-limit-x-laravel-cte:1.1.1
3. Add the traits to the models that need them. The bridge resolves the trait collision between the two upstream packages, so both traits go on the same model — you should not need insteadof/as aliasing:
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
use Staudenmeir\EloquentEagerLimit\HasEagerLimit;
use Staudenmeir\LaravelCte\Eloquent\HasCte;
class Category extends Model
{
use HasCte;
use HasEagerLimit;
}
Do this via one internal trait (App\Support\Concerns\HasCteAndEagerLimit) so that no model file or call site references the vendor namespace directly. It makes removal or a fork a one-file change.
4. Look at the upstream READMEs first, not this one. The bridge ships essentially no documentation of its own — it's a compatibility shim. All the usage detail lives in vendor/staudenmeir/eloquent-eager-limit/README.md and vendor/staudenmeir/laravel-cte/README.md, plus the two src/ trees. Read those before you read your own code.
5. First use case: "latest N per parent." This is the highest-value thing to spike, because it's the one you cannot express natively — ->with(['orders' => fn ($q) => $q->limit(3)]) limits globally to 3 rows, which is almost never what the product means.
$customers = Customer::query()
->with(['orders' => fn ($q) => $q->orderByDesc('placed_at')->limit(5)])
->paginate(25);
Before you build on it, dump the SQL (DB::listen, or ->toSql() on the eager query) and confirm you see ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY placed_at DESC) with an outer WHERE row_num <= 5. If you don't see that, you are shipping the global-limit bug.
Top-N per parent, the core pattern. Always pair limit() with an explicit orderBy() inside the closure — otherwise "top 5" is whatever order the storage engine returns, which changes between runs.
Post::query()
->with([
'comments' => fn ($q) => $q->latest()->limit(3),
])
->paginate(20);
Nested eager limits. Each level emits its own partitioned query. This is where the package earns its keep on feed/dashboard screens, but each added level is another window function over the related table — budget accordingly.
Post::with([
'comments' => fn ($q) => $q->latest()->limit(3),
'comments.author',
'comments.replies' => fn ($q) => $q->oldest()->limit(2),
])->get();
Recursive CTEs for hierarchies. Use the CTE builder for the ancestor/descendant walk, then eager-load limited children per node. Category trees, org charts, threaded comment roots, referral chains all follow this shape:
$tree = Category::query()
->withExpression('tree', function ($query) {
$query->from('categories')
->whereNull('parent_id')
->select('id', 'parent_id', 'name')
->unionAll(
DB::table('categories')
->join('tree', 'tree.id', '=', 'categories.parent_id')
->select('categories.id', 'categories.parent_id', 'categories.name')
);
})
->from('tree')
->with(['children' => fn ($q) => $q->limit(10)])
->get();
Method names come from the upstream CTE package and have changed across its releases — verify against vendor/staudenmeir/laravel-cte/README.md for the installed version rather than trusting a snippet.
Isolate behind a read-model/repository class. Keep vendor traits out of controllers, resources, and jobs. One App\Queries\CustomerOrderFeed class that returns a collection is enough; the rest of the app depends on that class, and swapping to a summary table or a vendored fork touches one file.
Lock the SQL shape with a snapshot test. This is your only real defence against a Laravel minor bump silently changing compilation:
public function test_eager_limit_sql_shape(): void
{
$sql = Customer::with(['orders' => fn ($q) => $q->limit(5)])->toSql();
$this->assertStringContainsString('row_number() over', strtolower($sql));
$this->assertStringContainsString('partition by', strtolower($sql));
}
Run it on the same database engine as production — grammars differ meaningfully between MySQL, Postgres, and SQLite.
Chunk large parent sets. Window functions sort the whole related table before filtering. chunkById() on the parent, with eager limits per chunk, keeps working-set size bounded and memory flat.
Index the partition key and the order column together — (customer_id, placed_at) for the example above. Without it, every page view sorts the full related table.
->limit(3) inside with() compiles to a plain LIMIT 3 and the first parent gets all three rows while the rest get none. No error, just wrong UI. Assert the SQL once and it can't sneak back in.PARTITION BY produces plausible wrong data, not an exception. This is the failure mode to fear. If a relation is keyed through a pivot (BelongsToMany, HasManyThrough), check the partition key is the pivot's foreign key — add the trait to the related model as well, and verify with real data, not a fixture with one parent.whereIn (parent ids). If the limited relation needs the CTE, define it again on the relation's query. Forgetting this silently changes which rows get numbered.orderByDesc('score')->limit(5) with duplicate scores gives non-deterministic membership between requests. Add ->orderByDesc('id').withTrashed() inside the closure — the limit and the scope are not independent.toSql() shows no bindings. Use DB::listen(), ->toRawSql() (Laravel 10.15+), or the query log when the SQL "looks right" but returns wrong rows.latestOfMany() / ofMany() or a subquery whereIn solves "the latest one per parent" with no dependency at all. Check that first; it's the cheapest possible outcome.withCount('comments') counts the full set, not the limited subset, so a paginator on comments will disagree with the 3 you rendered. Compute "has more" separately (row_num = 4 existence check) or just render a "view all" link.sqlite3 --version in the CI image clears 3.25 — an old base image fails at query time, not at boot.app/Support/ and maintain in a day. Decide who owns that before you merge, not after.How can I help you explore Laravel packages today?