Laravel Cte Laravel Package

Product Decisions This Supports

• Roadmap sequencing for hierarchy-dependent features. Org charts, category/menu trees, threaded comments, referral chains, permission inheritance, bill-of-materials, and multi-step reporting rollups are all on the roadmap but typically get deferred because the naive Laravel implementation is app-side recursion or N+1 queries. This package moves them from "needs a bespoke traversal utility" to "a query we write this sprint," which is the single biggest lever here. If two or more of those features are in the next two quarters, the ROI case is essentially pre-made.
• Build vs. buy for graph/tree traversal. The alternative isn't another vendor — it's 2–6 weeks of engineering to build and then indefinitely own a recursive-query helper, plus the ongoing cost of keeping it correct across databases. This is a free, MIT-licensed, single-purpose dependency with no service to run. Decision: use the package, spend the saved engineering time on the user-facing feature.
• Database portability as a product commitment. Multi-database support (MySQL, MariaDB, PostgreSQL, SQLite, SQL Server, Oracle, SingleStore) means hierarchy features don't become a reason we can't support a customer's or region's database choice, and don't become a migration blocker if we move later. That is a product flexibility argument, not just a technical one — worth stating explicitly if enterprise or on-prem deployments are in play.
• Performance/p95 decisions on existing screens. Any page currently doing application-side recursion can be re-platformed onto a single recursive CTE without a data model change. That's a latency and database-load win we can bank against existing feature work rather than as a new initiative.
• Scoping data-writing features. CTE support on INSERT/UPDATE/DELETE unlocks backfills, dedupe/merge flows, and bulk re-parenting as one statement instead of a PHP loop. This meaningfully shrinks the effort estimate for data-migration and data-consistency stories, and reduces the risk of partial-state bugs.
• De-risking infinite-recursion incidents. Native cycle detection (MariaDB 10.5.2+, PostgreSQL 14+) is a support-ticket and incident class we can design out rather than monitor. If we've ever had a runaway query page or a hung worker from a bad tree, this is a direct mitigation.
• Buying optionality on advanced SQL. Materialized/non-materialized expressions let us tune CTE evaluation per query on PostgreSQL/SQLite without a new dependency later, so we aren't re-litigating vendor selection mid-feature.

When to Consider This Package

Adopt when:

• At least one near-term feature requires hierarchical, graph, or recursive traversal (org structure, categories, comment threads, referrals, permission trees, BOM).
• We are on a supported Laravel version (5.5 through 13.x, with version pinning documented per major) and a supported database version — note the honest floors: MySQL 8.0+, MariaDB 10.2+, PostgreSQL 9.4+, SQLite 3.8.3+, SQL Server 2008+, Oracle 9.2+. Anything older is out.
• The team is already comfortable with SQL CTEs. This package exposes SQL syntax; it does not hide it. If the team isn't, budget a short learning spike rather than treating this as a drop-in abstraction.
• We want a low-ceremony, no-runtime, no-schema-change dependency we can remove by rewriting a handful of queries if we ever need to. Exit cost is genuinely small.
• We anticipate needing recursive traversal in more than one place — a single one-off query is not worth the dependency conversation, but three is.

Look elsewhere or defer when:

• The data is small and shallow (a few hundred rows, 2–3 levels). A simple parent_id adjacency list with an eager-loaded relation or a cached nested set is fine; don't add a dependency to solve a problem you don't have.
• We need a full ORM-level hierarchy abstraction with built-in traversal methods, move/copy/reorder helpers, and model-level APIs. This package is a query-layer primitive; staudenmeir/laravel-adjacency-list (same author) is the higher-level option for recursive relationships, and adopting both is a coherent path — but pick deliberately rather than by accident.
• Vendor risk tolerance is low for a single-maintainer dependency. This is effectively a one-author project with a long, disciplined release history and strong CI (PHPStan level 10, code coverage). It is well-run, but "well-run single maintainer" is a real category with a real succession question. Mitigation: MIT license plus a narrow query-builder surface means a fork or in-house patch is cheap. Do not let this become load-bearing without that fallback documented.
• We're on a database or version below the supported floors, or we're on Firebird (support existed and was dropped in v1.13 alongside Laravel 13). Confirm our actual production database and version before committing.
• We use heavily customized query builders, Lumen, or Oracle — all supported, but Lumen and Oracle require manual builder instantiation. That's an extra integration step with its own test surface; scope it into the estimate rather than discovering it during rollout.
• The requirement is real analytics/OLAP workload with large fact tables. CTEs are a query-construction convenience, not an analytics engine; if we're heading toward warehouse-scale reporting, that's a platform decision well above this package.

Sequencing note: adopt behind a small proof — one feature, one PR, measured against the current implementation. Because the surface is narrow and the license permissive, the cost of that trial is a day or two, and the result is a credible estimate for the rest of the roadmap rather than an opinion.

How to Pitch It (Stakeholders)

For executives — one paragraph. We have a cluster of planned features — org charts, category trees, threaded discussions, referral and permission hierarchies, reporting rollups — that all depend on the same underlying capability: walking recursive relationships in the database. Today each of those either gets deferred or gets built with custom code that we then own forever. A free, MIT-licensed, single-line-install library closes that gap and lets us build these features on the database's native capability instead of bespoke code. It adds no infrastructure, no vendor contract, and no data migration. The practical effect is that a set of roadmap items moves from "expensive and repeatedly postponed" to "normal feature work," and the cost of reversing the decision later is a handful of query rewrites. The main thing we're accepting is reliance on a small open-source project with one primary maintainer — mitigated by the permissive license and the fact that we could patch or replace it cheaply.

For engineering leads. This is a query-layer extension to Laravel's existing query builder and Eloquent — withExpression(), withRecursiveExpression(), materialized/non-materialized variants, cycle detection, and CTE support on INSERT/UPDATE/DELETE. Install is composer require, no services, no schema changes, no workers. It's version-pinned to Laravel major (1.13 for Laravel 13, down to 1.0 for 5.5–5.7), so the compatibility story is explicit and testable. Quality signals are unusually strong for a package this size: PHPStan level 10, tracked code coverage, CI across the supported database matrix, and a release cadence that has tracked every Laravel major. Two caveats to plan around: it exposes SQL CTE syntax rather than hiding it, so it's a tool for engineers comfortable with SQL; and Lumen and Oracle need manual builder instantiation, which adds integration and test surface. Firebird support was dropped in v1.13 — worth confirming that's not in our fleet. Recommend a contained pilot on one hierarchy feature with a before/after query count and p95 comparison; the result gives us a defensible estimate for the rest of the roadmap and a documented fallback (fork the MIT code) if maintainership ever becomes a concern.

For the product team. The pitch to internal stakeholders is time-to-market on tree-shaped features and the removal of a recurring class of performance and incident risk. Two things to say out loud: first, this makes hierarchy features cheap, which means we should expect demand for them to rise — plan capacity accordingly rather than treating it as free. Second, it does not make bad hierarchy models fast; if a screen is slow because of an unbounded tree or a missing index, a CTE will faithfully reproduce that problem. Pair adoption with a short review of how we model parent-child data before we scale it.