Pascal CESCATO

Posted on Nov 17

Using CTEs When Your ORM Says No (The Lazy Developer's Survival Guide)

#sql #architecture #performance #database

You just spent two articles learning about CTEs. You're excited. You've seen the power. You understand the elegance. You're ready to refactor that nightmare query that's been haunting your dreams.

You open your ORM documentation...

404: CTE support not found.

"Wait, WHAT? Every database has supported CTEs since 2005-2018, but my ORM doesn't?"

Welcome to the final boss fight: making CTEs work in real projects despite ORM limitations.

This is Part 3 of 3 - the practical guide to actually using what you've learned.

TL;DR

Most popular ORMs don't support CTEs natively (Doctrine, Hibernate, Eloquent)
Three rebels do support them: SQLAlchemy Core, jOOQ, Django 4.2+
For hostile ORMs: Repository pattern, database views, hybrid approach
Security is non-negotiable: Always use parameterized queries
Decision tree: When to use CTEs vs subqueries vs views vs plain ORM
Pick the laziest solution that actually works

This series:

Part 1: Basic CTEs - Readability & maintenance
Part 2: Recursive CTEs - Hierarchical data traversal
Part 3 (this): Making CTEs work despite ORM limitations

Quick glossary:

Parameterized query: SQL with placeholders (:param or ?) instead of string concatenation - prevents SQL injection
Repository pattern: Isolate database queries in dedicated classes
Database view: Saved query that acts like a table
Materialized view: Pre-computed view stored on disk (faster but requires refresh)

The Great Divide

Here's the developer's dilemma:

CTEs are powerful. You know this now. They make complex queries readable and performant.

Your ORM pretends they don't exist. Most ORMs were built when major databases didn't support CTEs universally. Fair excuse in 2005. Weak excuse in 2025.

You're stuck choosing between:

ORM comfort (object mapping, relationships, query builders)
SQL power (CTEs, window functions, advanced features)

Why not both? Turns out you can have both - if you know where to look and how to play the game.

The Harsh Reality: Most ORMs Don't Care

Let's be brutally honest about what the major ORMs offer for CTEs:

ORM	Language	CTE Support	Notes
❌ Doctrine	PHP	None	No `.with()` in QueryBuilder
❌ Hibernate	Java	None	Nothing in HQL or Criteria API
❌ Eloquent	Laravel/PHP	Third-party	Requires staudenmeir/laravel-cte
❌ Active Record	Rails/Ruby	Limited	Some gems, nothing native
✅ SQLAlchemy Core	Python	Native	Excellent `.cte()` support
✅ jOOQ	Java	Native	Full support including recursive
✅ Django ORM	Python	Native (4.2+)	Added April 2023

Why this neglect?

ORMs prioritize:

Portability - Work across multiple database versions
Simplicity - Abstract SQL away from developers
Backward compatibility - Support older databases

CTEs were seen as "too advanced" or "database-specific" (even though they've been SQL standard since 1999 and universally supported since 2018).

Result: If you want CTEs with most ORMs, you're writing raw SQL. You lose some ORM magic, but gain the full power of modern SQL.

The Rebels: ORMs That Actually Get It

Not all ORMs gave up on SQL's evolution. Three stand out:

SQLAlchemy Core (Python): The Pythonic Way

SQLAlchemy Core offers elegant CTE support through the .cte() method. You build CTEs using the same fluent API, keeping everything in Python without raw SQL strings.

Example (from Part 1):

from sqlalchemy import select, table, column

# Define tables
orders = table('orders', column('user_id'), column('order_date'))
users = table('users', column('id'), column('name'))

# Create CTE
recent_orders = (
    select(orders.c.user_id)
    .where(orders.c.order_date > '2025-01-01')
    .distinct()
    .cte('recent_orders')
)

# Main query using the CTE
query = (
    select(users)
    .join(recent_orders, users.c.id == recent_orders.c.user_id)
)

Why it works:

✅ Clean, readable Python code
✅ Type-safe with modern Python type hints
✅ Fully integrated into SQLAlchemy ecosystem
✅ Works with PostgreSQL, MySQL 8+, SQL Server, Oracle

Lazy win: Write complex queries without leaving Python land.

jOOQ (Java): SQL in a Type-Safe Dress

jOOQ provides a DSL that's remarkably close to SQL while maintaining Java's type safety. Its .with() method handles CTEs beautifully, including recursive ones.

Example:

CommonTableExpression<Record1<Integer>> recentOrders = name("recent_orders")
    .fields("user_id")
    .as(create.select(field("user_id", Integer.class))
        .from("orders")
        .where(field("order_date").gt("2025-01-01"))
        .distinct());

ResultQuery<?> query = create
    .with(recentOrders)
    .select()
    .from(table("users"))
    .join(table("recent_orders"))
    .on(field("users.id").eq(field("recent_orders.user_id")));

Why it works:

✅ Type-safe - compiler catches errors
✅ SQL-native syntax
✅ Supports recursive CTEs
✅ Excellent documentation

Lazy win: SQL power with compile-time safety.

Django ORM (Python): The Late Bloomer

Django 4.2 (April 2023, LTS) finally added native CTE support through the .cte() method. Better late than never!

Example:

from django.contrib.auth.models import User
from myapp.models import Order

# Create CTE
recent_orders_cte = (
    Order.objects
    .filter(order_date__gt='2025-01-01')
    .values('user')
    .distinct()
    .cte('recent_orders')
)

# Use in main query
users_with_recent_orders = (
    User.objects
    .filter(id__in=recent_orders_cte.values('user'))
)

Why it works:

✅ Feels natural within Django conventions
✅ Full ORM integration
✅ No third-party packages needed (Django 4.2+)

Lazy win: If you're on Django 4.2+, CTEs are already there. Use them.

If you're using one of these three ORMs, you're lucky. Use their native CTE support and enjoy life.

For everyone else, read on.

Survival Strategies for Hostile ORMs

Your ORM doesn't support CTEs. What now? You have options.

Strategy 1: The Repository Pattern (The Cleanest)

Isolate your CTE queries in repository classes. Keep your architecture clean, your CTEs organized, and your security tight.

Example (Doctrine/PHP):

// src/Repository/UserRepository.php
class UserRepository extends ServiceEntityRepository
{
    public function getUsersWithRecentOrders(\DateTime $since): array
    {
        $sql = <<<SQL
            WITH recent_orders AS (
                SELECT DISTINCT user_id FROM orders
                WHERE order_date > :since
            )
            SELECT u.* FROM users u
            JOIN recent_orders ro ON u.id = ro.user_id
        SQL;

        $rsm = new ResultSetMappingBuilder($this->getEntityManager());
        $rsm->addRootEntityFromClassMetadata(User::class, 'u');

        return $this->getEntityManager()
            ->createNativeQuery($sql, $rsm)
            ->setParameter('since', $since)  // 🎯 PARAMETERIZED
            ->getResult();
    }
}

Benefits:

✅ Encapsulated - CTE logic in one place
✅ Testable - Easy to unit test
✅ Reusable - Call from multiple controllers/services
✅ Secure - Parameters properly bound
✅ Clean separation - Complex SQL doesn't pollute business logic

The lazy win: Write once, reuse forever. Change the query in one place, it updates everywhere.

Similar pattern in other ORMs:

Hibernate (Java):

// EntityManager with native query
@Repository
public class UserRepository {
    @PersistenceContext
    private EntityManager entityManager;

    public List<User> getUsersWithRecentOrders(LocalDate since) {
        String sql = """
            WITH recent_orders AS (
                SELECT DISTINCT user_id FROM orders
                WHERE order_date > :since
            )
            SELECT u.* FROM users u
            JOIN recent_orders ro ON u.id = ro.user_id
            """;

        return entityManager.createNativeQuery(sql, User.class)
            .setParameter("since", since)  // Parameterized
            .getResultList();
    }
}

Eloquent (Laravel):

// Using third-party package: staudenmeir/laravel-cte
use Staudenmeir\LaravelCte\Query\Builder;

class UserRepository {
    public function getUsersWithRecentOrders($since) {
        return User::query()
            ->withExpression('recent_orders', function ($query) use ($since) {
                $query->select('user_id')
                    ->from('orders')
                    ->where('order_date', '>', $since)
                    ->distinct();
            })
            ->join('recent_orders', 'users.id', '=', 'recent_orders.user_id')
            ->get();
    }
}

// Or raw SQL in repository
public function getUsersWithRecentOrdersRaw($since) {
    $sql = "
        WITH recent_orders AS (
            SELECT DISTINCT user_id FROM orders
            WHERE order_date > ?
        )
        SELECT users.* FROM users
        JOIN recent_orders ON users.id = recent_orders.user_id
    ";

    return User::fromQuery($sql, [$since]);  // Parameterized
}

Strategy 2: Database Views (The Pragmatic)

Create views for frequently used CTE patterns. Let the database do the work, ORM just reads the results.

Example:

-- Migration or DB setup script
CREATE VIEW recent_order_users AS
WITH recent_orders AS (
    SELECT DISTINCT user_id FROM orders
    WHERE order_date > CURRENT_DATE - INTERVAL '30 days'
)
SELECT u.* FROM users u
JOIN recent_orders ro ON u.id = ro.user_id;

Then in your ORM:

// Doctrine - map the view like a regular entity
#[Entity]
#[Table(name: 'recent_order_users')]
#[ReadOnly]
class RecentOrderUser {
    // Map columns just like a regular entity
    #[Id]
    #[Column(type: 'integer')]
    private int $id;

    #[Column(type: 'string')]
    private string $email;

    // ... etc
}

// Use it like any entity
$users = $entityManager->getRepository(RecentOrderUser::class)->findAll();

When to use views:

✅ Stable aggregations - Quarterly sales, annual reports, historical data

✅ Frequently reused logic - Same query needed in multiple places

✅ Complex calculations - Heavy joins, window functions

✅ Base for BI tools - Tableau, Metabase, PowerBI can query views directly

✅ Centralized business rules - Logic lives in database, not scattered across code

Bonus: Materialized Views

For expensive calculations on large datasets:

-- PostgreSQL materialized view
CREATE MATERIALIZED VIEW quarterly_sales AS
WITH sales_data AS (
    SELECT 
        DATE_TRUNC('quarter', sale_date) as quarter,
        region,
        SUM(amount) as total
    FROM sales
    WHERE sale_date >= '2020-01-01'
    GROUP BY quarter, region
)
SELECT * FROM sales_data;

-- Refresh periodically (cron job, trigger, manual)
REFRESH MATERIALIZED VIEW quarterly_sales;

Trade-off: Data isn't real-time, but queries are blazing fast. Perfect for dashboards and reports.

The lazy win: Database does the work, ORM just reads. Minimal code, maximum performance.

Strategy 3: Hybrid Approach (The Realistic)

Use the right tool for each job. Don't force everything through one approach.

Pattern:

CTEs for complex analytics - Reporting, dashboards, data exploration
ORM for CRUD operations - Create user, update order, delete post
Keep both in your toolkit - Neither is "wrong," they solve different problems

Example architecture:

// Complex reporting with CTEs (Repository)
class AnalyticsService {
    public function getQuarterlySalesReport(int $year): array {
        return $this->analyticsRepository->getQuarterlySales($year);
        // Uses CTE internally
    }
}

// Simple operations with ORM
class UserService {
    public function updateLastLogin(User $user): void {
        $user->setLastLogin(new \DateTime());
        $this->entityManager->flush();
        // Plain ORM, no CTEs needed
    }
}

The lazy win: Right tool for the right job. No dogma, just pragmatism.

Security: The Non-Negotiable

⚠️ CRITICAL SECURITY WARNING

When writing raw SQL (including CTEs), always use parameterized queries.

No exceptions. Ever. Your job, your reputation, and your users' data depend on it.

❌ DANGEROUS (SQL Injection Vulnerability)

// DON'T DO THIS - EVER
$userId = $_GET['id'];
$sql = "WITH cte AS (...) SELECT * FROM users WHERE id = " . $userId;
$results = $connection->executeQuery($sql);

Why this is terrible:

User sends id=1 OR 1=1 → Returns all users
User sends id=1; DROP TABLE users;-- → Deletes your table
User sends id=1 UNION SELECT password FROM admin → Steals credentials

Real-world impact:

Database compromised
User data stolen
Company reputation destroyed
Potential legal consequences (GDPR, etc.)
You become a cautionary tale on HackerNews

✅ SAFE (Parameterized Query)

// DO THIS - ALWAYS
$sql = "WITH cte AS (...) SELECT * FROM users WHERE id = :id";
$results = $connection->executeQuery($sql, ['id' => $userInput]);

How it works:

:id is a placeholder
Database driver escapes and validates the value
SQL injection becomes impossible
User input is treated as data, never as code

Security Checklist

Before deploying any query with CTEs, verify:

✅ Use parameterized queries - :param or ? placeholders

✅ Never concatenate strings with user input

✅ Validate input types before queries (integers are integers, dates are dates)

✅ Use ORM's parameter binding when available

✅ Review all raw SQL in code reviews

✅ Test with malicious input - Try SQL injection in your tests

✅ Principle of least privilege - Database user should have minimal permissions

Examples in Different Languages

Python (psycopg2):

# Safe
cursor.execute(
    "WITH cte AS (...) SELECT * FROM users WHERE id = %s",
    [user_id]
)

Java (JDBC):

// Safe
PreparedStatement stmt = conn.prepareStatement(
    "WITH cte AS (...) SELECT * FROM users WHERE id = ?"
);
stmt.setInt(1, userId);

Node.js (pg):

// Safe
await client.query(
    "WITH cte AS (...) SELECT * FROM users WHERE id = $1",
    [userId]
);

The lazy approach to security: Use parameters everywhere, sleep well at night, don't become a cautionary tale on HackerNews.

Performance: Advanced Techniques

In Part 1, we said "trust the optimizer." That's still true 99% of the time. But for the 1% where you have evidence the optimizer is wrong...

Forcing Materialization (PostgreSQL 12+)

Sometimes you want to force the database to materialize a CTE (calculate once, store temporarily):

-- Force materialization
WITH quarterly_sales AS MATERIALIZED (
    SELECT 
        DATE_TRUNC('quarter', sale_date) AS quarter,
        SUM(amount) as total
    FROM sales
    WHERE date >= '2024-01-01'
    GROUP BY quarter
)
SELECT * FROM quarterly_sales;

When to force materialization:

✅ CTE result is small (hundreds/thousands of rows)
✅ CTE is expensive to calculate (complex joins, aggregations)
✅ CTE is referenced multiple times in main query
✅ You've measured with EXPLAIN and confirmed it's faster

Forcing Inlining

Or force the optimizer to inline the CTE (treat it like a subquery):

-- Force inlining
WITH recent_data AS NOT MATERIALIZED (
    SELECT * FROM logs WHERE date > NOW() - INTERVAL '1 day'
)
SELECT * FROM recent_data WHERE level = 'ERROR';

When to force inlining:

✅ CTE result is huge (millions of rows)
✅ Main query has selective filters (WHERE clauses that reduce rows significantly)
✅ Database can push down predicates for better optimization

The Pro Move: Measure, Don't Guess

-- Test with different approaches
EXPLAIN (ANALYZE, BUFFERS)
WITH sales_summary AS MATERIALIZED (  -- or NOT MATERIALIZED
    -- Your CTE here
)
SELECT * FROM sales_summary WHERE total > 100000;

Look for:

Execution time (actual vs estimated)
Rows processed at each step
Buffer hits (cache efficiency)
Memory usage

Rule: Only force materialization/inlining if you have evidence from EXPLAIN. Don't guess.

The lazy approach: Trust the optimizer by default. Measure when performance matters. Optimize only what's proven slow.

Bonus: CTEs for Data Modification (PostgreSQL 12+)

CTEs aren't just for SELECT. PostgreSQL supports data modification in CTEs - powerful for complex multi-step operations.

Archive and Delete in One Transaction

WITH archived AS (
    INSERT INTO orders_archive
    SELECT * FROM orders
    WHERE order_date < '2023-01-01'
    RETURNING id
)
DELETE FROM orders
WHERE id IN (SELECT id FROM archived);

What just happened:

Insert old orders into archive table
RETURNING id gives us the archived IDs
Delete those IDs from main table
All in one atomic transaction

Benefits:

✅ Single transaction - can't fail halfway
✅ No race conditions
✅ Cleaner than multiple statements

Update with Audit Trail

WITH updated_prices AS (
    UPDATE products
    SET price = price * 1.1
    WHERE category_id = 5
    RETURNING id, name, price
)
INSERT INTO price_audit (product_id, product_name, new_price, changed_at)
SELECT id, name, price, NOW()
FROM updated_prices;

Use case: Update product prices and automatically log the change.

Limitations:

⚠️ PostgreSQL-specific (not in MySQL, limited in SQL Server)
⚠️ Can be complex to debug
⚠️ Use with caution in production

The lazy win: Complex data operations without application logic.

Real-World Example: E-commerce Analytics Service

Let's put it all together with a realistic service class that combines multiple strategies.

# services/analytics.py
class AnalyticsService:

    def get_category_performance_with_hierarchy(self, period_start):
        """
        Complex CTE for hierarchical category analysis.
        Uses recursive CTE + aggregations.
        """
        cte_query = """
        WITH RECURSIVE category_tree AS (
            -- Anchor: Root categories
            SELECT id, name, parent_id, 0 as level
            FROM categories 
            WHERE parent_id IS NULL

            UNION ALL

            -- Recursive: Subcategories
            SELECT c.id, c.name, c.parent_id, ct.level + 1
            FROM categories c
            JOIN category_tree ct ON c.parent_id = ct.id
        ),
        category_sales AS (
            -- Aggregate sales for each category
            SELECT 
                ct.id, 
                ct.name, 
                ct.level,
                COALESCE(SUM(oi.quantity * oi.price), 0) as revenue,
                COUNT(DISTINCT o.id) as order_count
            FROM category_tree ct
            LEFT JOIN products p ON p.category_id = ct.id
            LEFT JOIN order_items oi ON oi.product_id = p.id
            LEFT JOIN orders o ON o.id = oi.order_id
            WHERE o.created_at >= %s
            GROUP BY ct.id, ct.name, ct.level
        )
        SELECT * FROM category_sales 
        ORDER BY level, revenue DESC
        """

        # Parameterized query - SECURE
        return self.execute_raw_query(cte_query, [period_start])

    def get_simple_order_stats(self, user_id):
        """
        Simple aggregation - use ORM.
        No need for CTEs here.
        """
        return Order.objects.filter(user_id=user_id).aggregate(
            total_orders=Count('id'),
            total_spent=Sum('total_amount'),
            avg_order_value=Avg('total_amount')
        )

    def get_top_customers(self):
        """
        Frequently used query - uses database view.
        View contains complex CTE logic.
        """
        return TopCustomer.objects.all()[:100]  # Reads from view

Architecture patterns:

✅ Complex analytics → CTEs in dedicated methods
✅ Simple queries → Plain ORM
✅ Frequently reused → Database views
✅ Clear separation → Easy to test and maintain
✅ Security → All parameters properly bound

Decision Tree: When to Use What

Use CTEs When:

✅ Complex analytical queries

✅ Hierarchical data traversal (recursive)

✅ Heavy aggregations with multiple references

✅ Performance is critical and you can optimize per database

✅ One-time or ad-hoc complex queries

Use Database Views When:

✅ Frequently reused logic

✅ Stable aggregations (historical data)

✅ Centralizing business rules

✅ Base for multiple queries or BI tools

✅ Can be materialized for performance

Use ORM Subqueries When:

✅ Simple one-level nesting

✅ Portability is crucial

✅ Team prefers staying in ORM

✅ Query is straightforward enough

Stick with Plain ORM When:

✅ Standard CRUD operations

✅ Relationship management

✅ Transaction handling

✅ Simple queries with basic filters

✅ Team is most productive this way

Visual decision flowchart:

Need complex query?
  → No → Use plain ORM
  → Yes → Is it hierarchical/recursive?
      → Yes → Recursive CTE (no alternative)
      → No → Will you reuse this logic often?
          → Yes → Database view
          → No → Is it analytical/reporting?
              → Yes → CTE in repository
              → No → ORM subquery is probably fine

The lazy principle: Pick the solution that requires the least ongoing maintenance.

Beyond CTEs: When You Need More

CTEs are powerful and will handle millions of rows efficiently. But when your scale explodes - billions of events, real-time analytics, multi-region writes - you'll need architectural changes beyond query optimization.

Partitioning & Sharding

When a single table becomes unwieldy (100M+ rows):

-- PostgreSQL declarative partitioning
CREATE TABLE sales (
    id BIGINT,
    sale_date DATE,
    amount DECIMAL,
    region VARCHAR(50)
) PARTITION BY RANGE (sale_date);

-- Create partitions
CREATE TABLE sales_2024_q1 PARTITION OF sales
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE sales_2024_q2 PARTITION OF sales
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

Benefits:

Queries only scan relevant partitions
Maintenance operations (vacuum, backup) per partition
Old partitions can be dropped instantly
Indexes are smaller and faster

Materialized Views for Heavy Lifting

Pre-compute expensive aggregations:

CREATE MATERIALIZED VIEW quarterly_sales AS
SELECT 
    DATE_TRUNC('quarter', sale_date) as quarter,
    region,
    SUM(amount) as total,
    COUNT(*) as order_count,
    AVG(amount) as avg_order
FROM sales
GROUP BY quarter, region;

-- Refresh periodically (cron job, trigger)
REFRESH MATERIALIZED VIEW quarterly_sales;

-- Or concurrent refresh (PostgreSQL)
REFRESH MATERIALIZED VIEW CONCURRENTLY quarterly_sales;

Trade-off: Data isn't real-time (refresh delay), but queries are instant.

Columnar Storage & Specialized Databases

When OLTP databases struggle with analytics on billions of rows:

ClickHouse:

Blazing fast aggregations (10-100x faster than PostgreSQL)
Handles billions of rows easily
Perfect for: clickstreams, logs, events, time-series

BigQuery:

Serverless, scales to petabytes
Pay per query, no infrastructure management
Perfect for: ad-hoc analysis, data warehousing

DuckDB:

Embedded analytics engine (like SQLite but for analytics)
Perfect for: edge computing, local analysis, ETL

Example use case: Real-time clickstream analysis on 100M events/day. PostgreSQL CTEs would struggle; ClickHouse handles it in milliseconds.

Hybrid Architectures

Combine the best of multiple worlds:

Pattern:

OLTP for writes (PostgreSQL, MySQL) - transactional consistency
OLAP for analytics (ClickHouse, BigQuery) - fast aggregations
Stream processing (Kafka, Flink) - real-time pipelines
Replication - Sync data from OLTP to OLAP

Or use hybrid databases:

TiDB - Distributed SQL, OLTP + OLAP in one system
CockroachDB - Distributed PostgreSQL, geo-replication
MyScale - Vector + time-series + analytics

When to Consider These:

✅ Tables > 100M rows and growing

✅ Query times > 5 seconds despite optimization

✅ Real-time requirements (sub-second analytics)

✅ Multi-region, high-availability needs

✅ Analytical workloads competing with transactional ones

Learn more: I've written about optimizing high-availability stacks for read/write workloads with practical architectures for massive scale.

The lazy principle: Start with CTEs. When they're not enough, upgrade your architecture, not your willpower. Don't optimize prematurely, but know when it's time to level up.

The Future: ORMs Are Catching Up

The tide is turning. More ORMs are recognizing that hiding SQL's power isn't always the best strategy:

✅ Django 4.2 added native CTE support

✅ SQLAlchemy has always been ahead of the curve

✅ Prisma is exploring advanced SQL features

⚠️ Community pressure mounting on Doctrine, Hibernate, Eloquent

My prediction: Within 2-3 years, most major ORMs will have some form of CTE support. The databases have had it for decades. Developers want it. The pressure is building.

Until then: Use the strategies in this article.

Series Wrap-Up

We've covered a lot of ground in this series. Let's recap:

Part 1: Basic CTEs

Named subqueries for readability and maintainability
Performance gains through optimizer intelligence
Primary benefit: clarity (performance is a bonus)
All modern databases support them

Part 2: Recursive CTEs

Elegant solution for hierarchical data
One query instead of N+1 queries or loops
Safety features: depth limiters, cycle detection
Perfect for: org charts, categories, bill of materials

Part 3: Making It Work

Most ORMs don't support CTEs (yet)
Three that do: SQLAlchemy, jOOQ, Django 4.2+
Strategies: Repository pattern, views, hybrid approach
Security non-negotiable: parameterized queries always
Decision tree for choosing the right tool

Key Takeaways

You don't have to choose between ORMs and CTEs. Use each tool for what it does best:

ORMs for CRUD, relationships, standard operations
CTEs for complex queries where SQL shines
Master both, write better code

Performance matters, but clarity matters more. A query that runs 2x faster but takes 10x longer to maintain is a bad trade-off. Optimize for human time first, machine time second.

Security is non-negotiable. Parameterized queries. Always. No exceptions. Ever.

As I said in Part 1, referencing my article on database design: I PRODUCE code, I don't vomit it. CTEs are part of producing quality code.

Your Action Plan

Identify complex queries in your codebase (nested subqueries, N+1 problems)
Pick one to refactor into a CTE (start small)
Use EXPLAIN to verify improvement (measure, don't guess)
Implement using strategies from this article (repository pattern, view, or native ORM)
Share with your team (knowledge multiplier effect)