Advanced Features

Abstraction Patterns

DRYing up queries that share common joins

DRYing up queries that share common joins

Category: Advanced Features

When to Use This Pattern

Use Bundle + Destructure + Extend when:

Multiple queries share 2 or more identical joins
Queries differ only in additional joins, columns, or filters
You're copy-pasting join blocks between queries

Do NOT use this pattern when:

Each query has completely different joins
You only need single-relationship navigation (use composeFrom instead — see SQL Fragments)

The Problem: Repeated Joins

If your codebase has this smell, you need this pattern:

// Query 1
val i = from(Table<Inventory>())
val w = from(i.warehouse())
val p = from(i.product())

// Query 2 - same 3 lines, then adds more
val i = from(Table<Inventory>())
val w = from(i.warehouse())   // Copy-pasted
val p = from(i.product())     // Copy-pasted
val s = from(p.supplier())

// Query 3 - same pattern again
val i = from(Table<Inventory>())
val w = from(i.warehouse())   // Copy-pasted
val p = from(i.product())     // Copy-pasted
val s = from(p.supplier())    // Copy-pasted
// ... different filter/projection

Three queries, 9 lines of repeated join code. This is exactly what Bundle + Destructure solves.

SQL Equivalent

Three queries that share Order + Customer + OrderItem joins, but each adds different tables:

-- Query 1: Base
FROM "Order" o
INNER JOIN Customer c ON c.id = o.customer_id
INNER JOIN OrderItem oi ON oi.order_id = o.id
...

-- Query 2: Base + Product
FROM "Order" o
INNER JOIN Customer c ON c.id = o.customer_id      -- repeated
INNER JOIN OrderItem oi ON oi.order_id = o.id      -- repeated
INNER JOIN Product p ON p.id = oi.product_id       -- added
...

-- Query 3: Base + Product + Shipment
FROM "Order" o
INNER JOIN Customer c ON c.id = o.customer_id      -- repeated
INNER JOIN OrderItem oi ON oi.order_id = o.id      -- repeated
INNER JOIN Product p ON p.id = oi.product_id       -- repeated
LEFT JOIN Shipment s ON s.order_id = o.id          -- added
...

The Solution: Bundle + Destructure + Extend

// 1. Composite type for common joins
@Serializable
data class OrderBase(val o: Order, val c: Customer, val oi: OrderItem)

// 2. Bundle common joins into one fragment
@SqlFragment
fun ordersWithCustomerAndItems(since: String): SqlQuery<OrderBase> = sql.select {
    val o = from(Table<Order>())
    val c = join(Table<Customer>()) { c -> c.id == o.customerId }
    val oi = join(Table<OrderItem>()) { oi -> oi.orderId == o.id }
    where { o.orderDate >= since }
    OrderBase(o, c, oi)
}

// 3. Extension fragments for optional joins
@SqlFragment
fun OrderItem.product() = sql {
    composeFrom.join(Table<Product>()) { p -> p.id == this@product.productId }
}

@SqlFragment
fun Order.shipment() = sql {
    composeFrom.joinLeft(Table<Shipment>()) { s -> s.orderId == this@shipment.id }
}

// 4. Compose by destructuring + extending
val query1 = sql.select {
    val (o, c, oi) = from(ordersWithCustomerAndItems("2024-01-01"))
    // use o, c, oi
    sortBy(o.orderDate to Ord.Desc)
    OrderSummary(o.id, c.name, oi.quantity, o.totalAmount)
}

val query2 = sql.select {
    val (o, c, oi) = from(ordersWithCustomerAndItems("2024-01-01"))
    val p = from(oi.product())  // +1 line
    // use o, c, oi, p
    sortBy(o.orderDate to Ord.Desc)
    OrderWithProduct(o.id, c.name, oi.quantity, p.name, o.totalAmount)
}

val query3 = sql.select {
    val (o, c, oi) = from(ordersWithCustomerAndItems("2024-01-01"))
    val p = from(oi.product())
    val s = from(o.shipment())  // +1 line (s is nullable)
    // use o, c, oi, p, s?
    sortBy(o.orderDate to Ord.Desc)
    OrderWithShipment(o.id, c.name, p.name, s?.carrier, s?.trackingNumber)
}

Key Points

Destructuring

When a fragment returns SqlQuery<Composite>, destructure directly:

val (o, c, oi) = from(ordersWithCustomerAndItems("2024-01-01"))

Order matches the data class field order.

Extending from Destructured Entities

Use composeFrom extensions on individual entities after destructuring:

val (o, c, oi) = from(baseFragment())
val p = from(oi.product())  // extend from oi
val s = from(o.shipment())  // extend from o

Left Joins Are Nullable

composeFrom.joinLeft() returns a nullable type:

val s = from(o.shipment())  // s is Shipment?
groupBy(..., s?.carrier, s?.status)

When to Use

Situation	Pattern
Multiple queries share 2+ joins	Bundle into composite + destructure
Queries add different optional joins	`composeFrom` extensions on destructured entities
Single relationship (A → B)	Simple `composeFrom` extension (see SQL Fragments)