SQL Fragment Functions

SQL fragment functions allow you to use Kotlin functions inside of sql blocks. Writing a SQL fragment function is as simple as adding the @SqlFragment annotation to a function that returns a SqlQuery<T> or SqlExpression<T> instance. Recall that in the introduction we saw a SQL fragment function that calculated the P/E ratio of a stock:

  @SqlFragment
fun peRatioWeighted(stock: Stock, weight: Double): Double = sql.expression {
    (stock.price / stock.earnings) * weight
  }

Once this function is defined you can use it inside an sql block like this:

sql {
  Table<Stock>().map { stock -> peRatioWeighted(stock, stock.marketCap / totalWeight) }
}

Note that SQL fragment functions can call other SQL fragment functions, for example:

@SqlFragment
fun peRationSimple(stock: Stock): Double = sql.expression {
    stock.price / stock.earnings
  }

@SqlFragment
fun peRatioWeighted(stock: Stock, weight: Double): Double = sql.expression {
  peRationSimple(stock) * weight
}
sql {
  Table<Stock>().map { stock -> peRatioWeighted(stock, stock.marketCap / totalWeight) }
}

Also note that SQL fragment functions can make use of the context-receiver position. For example, let's make the marketCap field into a function:

@SqlFragment
fun Stock.marketCap() = sql.expression {
    price * sharesOutstanding
  }
val q = sql {
  val totalWeight = Table<Stock>().map { it.marketCap().use }.sum() // A local variable used in the query!
  Table<Stock>().map { stock -> peRatioWeighted(stock, stock.marketCap().use / totalWeight) }
}
println(q.buildFor.Postgres().value)
// SELECT (stock.price / stock.earnings) * ((this.price * this.sharesOutstanding) / (SELECT sum(this.price * this.sharesOutstanding) FROM Stock it)) AS value FROM Stock stock

Since Sql Fragment Functions guarantee that the code inside of them leads to a compile-time generated query they cannot be used arbitrarily. They can only contain a single capture, capture.select, or capture.expression block. They cannot have any other kind of control logic (e.g. if, when, for, etc.) inside of them. If you want a more flexible mechanism for writing queries see the Dynamic Queries section.

Fragment Composition Patterns

ExoQuery supports powerful compositional patterns where fragments can be nested and chained. This enables building complex queries from simple, reusable building blocks.

Pattern 1: Composite Types with Nested Entities

When joining multiple tables, wrap them in a composite data class to preserve access to individual entity fields:

// Composite type holds joined entities
@Serializable
data class InvSubPlan(val i: Invoice, val s: Subscription, val p: Plan)

// Base join fragment returns the composite
@SqlFragment
fun invoiceWithPlan(): SqlQuery<InvSubPlan> = sql.select {
    val i = from(Table<Invoice>())
    val s = join(Table<Subscription>()) { it.id == i.subscriptionId }
    val p = join(Table<Plan>()) { it.id == s.planId }
    InvSubPlan(i, s, p)
}

Pattern 2: Chainable Filter Fragments

Create filter fragments that accept and return SqlQuery<CompositeType>:

@SqlFragment
fun paidOnly(base: SqlQuery<InvSubPlan>): SqlQuery<InvSubPlan> = sql {
    base.filter { it.i.status == "paid" }  // Access nested entity via it.i
}

@SqlFragment
fun activeSubsOnly(base: SqlQuery<InvSubPlan>): SqlQuery<InvSubPlan> = sql {
    base.filter { it.s.status == "active" }  // Access different nested entity
}

Pattern 3: Composing Fragments Together

Chain fragments by nesting function calls:

// Single filter
val simpleQuery = sql.select {
    val row = from(paidOnly(invoiceWithPlan()))
    // row.i, row.s, row.p are all accessible
    Pair(row.i.id, row.p.planName)
}

// Chained filters - both conditions are combined with AND
val complexQuery = sql.select {
    val row = from(activeSubsOnly(paidOnly(invoiceWithPlan())))
    // Filters: i.status = 'paid' AND s.status = 'active'
    Pair(row.i.id, row.p.planName)
}

Pattern 4: Expression Fragments with .use

For computed values, return SqlExpression<T> and extract with .use:

@SqlFragment
fun monthOf(date: String): SqlExpression<String> = sql.expression {
    free("strftime('%Y-%m', ${date})").asPure<String>()
}

val q = sql.select {
    val row = from(paidOnly(invoiceWithPlan()))
    val month = monthOf(row.i.paidAt).use  // .use extracts String
    groupBy(month, row.p.planName)
    Result(month, row.p.planName, count())
}

Pattern 5: Subquery Fragments for Joins

Fragments returning SqlQuery<T> can be used with joinLeft:

@SqlFragment
fun refundTotals(): SqlQuery<RefundSum> = sql.select {
    val r = from(Table<Refund>())
    where { r.status == "completed" }
    groupBy(r.invoiceId)
    RefundSum(r.invoiceId, sum(r.amountCents))
}

val q = sql.select {
    val row = from(paidOnly(invoiceWithPlan()))
    val r = joinLeft(refundTotals()) { it.invoiceId == row.i.id }
    // r is nullable (RefundSum?)
    ...
}

Complete Example: Composable Billing Query

// Entities
@Serializable data class Invoice(val id: Long, val subscriptionId: Long, val amountCents: Long, val status: String, val paidAt: String)
@Serializable data class Subscription(val id: Long, val planId: Long, val accountId: Long, val status: String)
@Serializable data class Plan(val id: Long, val planName: String)
@Serializable data class Account(val id: Long, val countryCode: String)

// Composite types (progressively extended)
@Serializable data class InvSubPlan(val i: Invoice, val s: Subscription, val p: Plan)
@Serializable data class InvSubPlanAcct(val i: Invoice, val s: Subscription, val p: Plan, val a: Account)

// Base join fragments
@SqlFragment
fun invoiceWithPlan(): SqlQuery<InvSubPlan> = sql.select {
    val i = from(Table<Invoice>())
    val s = join(Table<Subscription>()) { it.id == i.subscriptionId }
    val p = join(Table<Plan>()) { it.id == s.planId }
    InvSubPlan(i, s, p)
}

@SqlFragment
fun invoiceWithPlanAndAccount(): SqlQuery<InvSubPlanAcct> = sql.select {
    val i = from(Table<Invoice>())
    val s = join(Table<Subscription>()) { it.id == i.subscriptionId }
    val p = join(Table<Plan>()) { it.id == s.planId }
    val a = join(Table<Account>()) { it.id == s.accountId }
    InvSubPlanAcct(i, s, p, a)
}

// Reusable filter fragments
@SqlFragment
fun paidOnly(base: SqlQuery<InvSubPlanAcct>): SqlQuery<InvSubPlanAcct> = sql {
    base.filter { it.i.status == "paid" }
}

@SqlFragment
fun activeSubsOnly(base: SqlQuery<InvSubPlanAcct>): SqlQuery<InvSubPlanAcct> = sql {
    base.filter { it.s.status == "active" }
}

// Expression fragment
@SqlFragment
fun monthOf(date: String): SqlExpression<String> = sql.expression {
    free("strftime('%Y-%m', ${date})").asPure<String>()
}

// Composed query: chain base + two filters + grouping
val revenueByPlanAndCountry = sql.select {
    val row = from(activeSubsOnly(paidOnly(invoiceWithPlanAndAccount())))
    val month = monthOf(row.i.paidAt).use
    groupBy(month, row.p.planName, row.a.countryCode)
    sortBy(month to Ord.Asc, row.p.planName to Ord.Asc)
    RevenueResult(
        month,
        row.p.planName,
        row.a.countryCode,
        countDistinct(row.i.subscriptionId),
        sum(row.i.amountCents).toDouble() / 100.0
    )
}
// Generated WHERE: i_status = 'paid' AND s_status = 'active'

What Doesn't Work

Fragments returning raw T with .use inside:

// ❌ DOESN'T COMPILE
@SqlFragment
fun isPaid(i: Invoice): Boolean = sql.expression { i.status == "paid" }.use

// Error: Cannot infer type for type parameter 'R'

Solution: Return SqlExpression<Boolean> and call .use at the call site, or use the filter fragment pattern shown above.

---

Extension Function Fragments

SQL fragments can be written as Kotlin extension functions, enabling fluent method chaining instead of nested function calls.

Basic Syntax

// Instead of:
@SqlFragment
fun paidOnly(base: SqlQuery<InvSubPlan>): SqlQuery<InvSubPlan> = sql {
    base.filter { it.i.status == "paid" }
}
// Usage: paidOnly(invoicesWithPlan())

// Use extension function:
@SqlFragment
fun SqlQuery<InvSubPlan>.paidOnly(): SqlQuery<InvSubPlan> = sql {
    this@paidOnly.filter { it.i.status == "paid" }
}
// Usage: invoicesWithPlan().paidOnly()

CRITICAL: The this@fragmentName Requirement

You MUST use this@fragmentName to reference the receiver inside the sql { } block.

// ❌ WRONG - will not compile or will behave unexpectedly
@SqlFragment
fun SqlQuery<Person>.adults(): SqlQuery<Person> = sql {
    this.filter { it.age >= 18 }  // 'this' is ambiguous inside sql { }
}

// ✓ CORRECT - use qualified this
@SqlFragment
fun SqlQuery<Person>.adults(): SqlQuery<Person> = sql {
    this@adults.filter { it.age >= 18 }
}

Why: Inside the sql { } block, this refers to the SQL DSL context, not the extension receiver. The qualified this@functionName syntax explicitly references the extension receiver.

CRITICAL: Use Explicit Lambda Parameter Names

When defining composeFrom joins, always use explicit lambda parameter names instead of it. ExoQuery uses the lambda parameter name as the SQL table alias.

// ❌ WRONG - all joins get alias "it", causing SQL ambiguity errors
@SqlFragment fun Invoice.subscription() = sql {
    composeFrom.join(Table<Subscription>()) { it.id == this@subscription.subscriptionId }
}

@SqlFragment fun Subscription.plan() = sql {
    composeFrom.join(Table<Plan>()) { it.id == this@plan.planId }
}

@SqlFragment fun Subscription.account() = sql {
    composeFrom.join(Table<Account>()) { it.id == this@account.accountId }
}

// Generated SQL (BROKEN):
// FROM Invoice i
//   INNER JOIN Subscription it ON it.id = i.subscription_id
//   INNER JOIN Plan it ON it.id = it.plan_id        ← ambiguous!
//   INNER JOIN Account it ON it.id = it.account_id  ← ambiguous!
// Error: "ambiguous column name: it.id"

// ✓ CORRECT - each join gets a distinct alias
@SqlFragment fun Invoice.subscription() = sql {
    composeFrom.join(Table<Subscription>()) { sub -> sub.id == this@subscription.subscriptionId }
}

@SqlFragment fun Subscription.plan() = sql {
    composeFrom.join(Table<Plan>()) { pln -> pln.id == this@plan.planId }
}

@SqlFragment fun Subscription.account() = sql {
    composeFrom.join(Table<Account>()) { acct -> acct.id == this@account.accountId }
}

// Generated SQL (CORRECT):
// FROM Invoice i
//   INNER JOIN Subscription sub ON sub.id = i.subscription_id
//   INNER JOIN Plan pln ON pln.id = sub.plan_id
//   INNER JOIN Account acct ON acct.id = sub.account_id

Why this happens: ExoQuery derives the SQL alias from the Kotlin lambda parameter name. When you use it (Kotlin's implicit parameter), every join gets the same alias it, making multi-join queries ambiguous.

Best practice: Use short, descriptive names that match your domain:

Entity	Suggested Parameter Name
User	usr or u
Order	ord or o
Subscription	sub or s
Plan	pln or p
Account	acct or a
Invoice	inv or i
Refund	ref or r

This also applies to joinLeft:

// ✓ CORRECT
val r = joinLeft(refundTotals()) { ref -> ref.invoiceId == i.id }

// ❌ WRONG - will conflict if other joins use "it"
val r = joinLeft(refundTotals()) { it.invoiceId == i.id }

Extension Functions on Value Types

You can also create extension fragments on primitive/value types:

// String extension for date formatting
@SqlFragment
fun String.monthOf(): SqlExpression<String> = sql.expression {
    free("strftime('%Y-%m', ${this@monthOf})").asPure<String>()
}

// Nullable Long extension for COALESCE
@SqlFragment
fun Long?.coalesce(default: Long): SqlExpression<Long> = sql.expression {
    free("COALESCE(${this@coalesce}, ${default})").asPure<Long>()
}

// Usage
val q = sql.select {
    val i = from(Table<Invoice>())
    val month = i.paidAt.monthOf().use  // String.monthOf()
    val amount = i.refundAmount.coalesce(0L).use  // Long?.coalesce()
    ...
}

Parameterized Extension Fragments

Extension fragments can take parameters. Do NOT use param() inside fragments — arguments are already captured:

// ❌ WRONG - param() not allowed for fragment arguments
@SqlFragment
fun SqlQuery<InvSubPlanAcct>.inCountry(code: String): SqlQuery<InvSubPlanAcct> = sql {
    this@inCountry.filter { it.a.countryCode == param(code) }  // Error!
}

// ✓ CORRECT - use argument directly
@SqlFragment
fun SqlQuery<InvSubPlanAcct>.inCountry(code: String): SqlQuery<InvSubPlanAcct> = sql {
    this@inCountry.filter { it.a.countryCode == code }
}

Why: The param() function brings external runtime variables into SQL. Fragment arguments are already part of the captured expression — they're not "external" to the fragment.

JVM Type Erasure Limitation

Due to JVM type erasure, you cannot have two extension functions with the same name on different generic instantiations of SqlQuery<T>:

// ❌ WILL NOT COMPILE - JVM signature clash
@SqlFragment
fun SqlQuery<TypeA>.paidOnly(): SqlQuery<TypeA> = ...

@SqlFragment
fun SqlQuery<TypeB>.paidOnly(): SqlQuery<TypeB> = ...
// Error: Platform declaration clash: same JVM signature

Solution: Use different function names:

// ✓ CORRECT - different names
@SqlFragment
fun SqlQuery<InvSubPlan>.paidOnly(): SqlQuery<InvSubPlan> = sql {
    this@paidOnly.filter { it.i.status == "paid" }
}

@SqlFragment
fun SqlQuery<InvSubPlanAcct>.paid(): SqlQuery<InvSubPlanAcct> = sql {
    this@paid.filter { it.i.status == "paid" }
}

Fluent Chaining Example

Extension fragments enable readable, composable query building:

// Define chainable filters
@SqlFragment
fun SqlQuery<InvSubPlanAcct>.paid(): SqlQuery<InvSubPlanAcct> = sql {
    this@paid.filter { it.i.status == "paid" }
}

@SqlFragment
fun SqlQuery<InvSubPlanAcct>.active(): SqlQuery<InvSubPlanAcct> = sql {
    this@active.filter { it.s.status == "active" }
}

@SqlFragment
fun SqlQuery<InvSubPlanAcct>.inCountry(code: String): SqlQuery<InvSubPlanAcct> = sql {
    this@inCountry.filter { it.a.countryCode == code }
}

@SqlFragment
fun SqlQuery<InvSubPlanAcct>.onPlan(name: String): SqlQuery<InvSubPlanAcct> = sql {
    this@onPlan.filter { it.p.planName == name }
}

// Compose fluently
val q = sql.select {
    val row = from(
        invoicesWithPlanAndAccount()
            .paid()
            .active()
            .inCountry("US")
            .onPlan("Pro")
    )
    // All filters combined: WHERE i_status = 'paid' AND s_status = 'active'
    //                         AND a_country_code = 'US' AND p_plan_name = 'Pro'
    ...
}

Quick Reference

Pattern	Syntax	Notes
Query filter extension	fun SqlQuery<T>.name(): SqlQuery<T>	Use this@name inside
Parameterized filter	fun SqlQuery<T>.name(arg: X): SqlQuery<T>	No param() needed
Value type expression	fun String.name(): SqlExpression<T>	Use this@name inside
Nullable expression	fun Long?.name(default: Long): SqlExpression<Long>	Handles null receiver

Common Errors

"Cannot use the variable x inside a param(...) function because it is an argument of the captured-function"

• You used param(arg) inside a fragment. Remove param() and use arg directly.

"Platform declaration clash: same JVM signature"

• Two extension functions have the same name on different SqlQuery<T> types. Rename one.

Filter doesn't apply / wrong behavior

• You used this instead of this@fragmentName. Always qualify the receiver.

---

The composeFrom Pattern

What is composeFrom?

composeFrom is a DSL function available inside any sql { } block. It is part of the SQL DSL context, NOT a property on entity types.

// ❌ WRONG - composeFrom is not a property on Invoice
@SqlFragment
fun subscriptionFor(invoice: Invoice) = sql {
    invoice.composeFrom.join(...)  // Error: Unresolved reference 'composeFrom'
}

// ✓ CORRECT - composeFrom is a DSL function, reference entity in the ON clause
@SqlFragment
fun subscriptionFor(invoice: Invoice) = sql {
    composeFrom.join(Table<Subscription>()) { s -> s.id == invoice.subscriptionId }
}

Two Equivalent Styles

You can define composeFrom fragments as either extension functions or regular functions. Both produce identical SQL.

Extension function style:

@SqlFragment
fun Invoice.subscription() = sql {
    composeFrom.join(Table<Subscription>()) { s -> s.id == this@subscription.subscriptionId }
}

// Usage:
val s = from(i.subscription())

Regular function style:

@SqlFragment
fun subscriptionFor(invoice: Invoice) = sql {
    composeFrom.join(Table<Subscription>()) { s -> s.id == invoice.subscriptionId }
}

// Usage:
val s = from(subscriptionFor(i))

Aspect	Extension Style	Regular Function Style
Syntax	i.subscription()	subscriptionFor(i)
Entity reference	this@fragmentName.field	paramName.field
Extra parameters	i.subscriptionWhere("active")	subscriptionFor(i, "active")
Chaining	i.subscription().plan()	Nested calls

Choose based on preference. Extension style enables fluent chaining; regular function style reads more naturally with multiple parameters.

Basic Syntax

@SqlFragment
fun User.withOrders() = sql {
    composeFrom.join(Table<Order>()) { order -> order.userId == this@withOrders.id }
}

Key points:

• composeFrom.join() returns SqlQuery<T> (the joined entity type)
• Use from() to bind the result into your select context
• Must use this@fragmentName to reference the receiver entity

Using from() to Bind Results

The from() function binds any SqlQuery<T> into the select context. This works uniformly for:

• Table<T>() — base tables
• composeFrom.join() — relationship joins
• Any other SqlQuery<T>

val q = sql.select {
    val i = from(Table<Invoice>())           // Base table
    val s = from(i.withSubscription())       // composeFrom join
    val p = from(s.withPlan())               // Chain from s
    ...
}

Defining Join Relationships

Define each relationship as an extension on the "from" entity:

@Serializable data class Invoice(val id: Long, val subscriptionId: Long, ...)
@Serializable data class Subscription(val id: Long, val planId: Long, val accountId: Long, ...)
@Serializable data class Plan(val id: Long, val planName: String)
@Serializable data class Account(val id: Long, val countryCode: String)

// Invoice -> Subscription
@SqlFragment
fun Invoice.withSubscription() = sql {
    composeFrom.join(Table<Subscription>()) { s -> s.id == this@withSubscription.subscriptionId }
}

// Subscription -> Plan
@SqlFragment
fun Subscription.withPlan() = sql {
    composeFrom.join(Table<Plan>()) { p -> p.id == this@withPlan.planId }
}

// Subscription -> Account
@SqlFragment
fun Subscription.withAccount() = sql {
    composeFrom.join(Table<Account>()) { a -> a.id == this@withAccount.accountId }
}

Branching from the Same Entity

You can join multiple tables from the same source entity:

val q = sql.select {
    val i = from(Table<Invoice>())
    val s = from(i.withSubscription())
    val p = from(s.withPlan())      // s -> Plan
    val a = from(s.withAccount())   // s -> Account (same source)
    ...
}

Generated SQL:

FROM Invoice i
  INNER JOIN Subscription s ON s.id = i.subscription_id
  INNER JOIN Plan p ON p.id = s.plan_id
  INNER JOIN Account a ON a.id = s.account_id

Parameterized Joins

You can bake filters directly into join fragments by adding parameters. This is useful for reusable relationship definitions with built-in constraints.

Extension style with parameter:

@SqlFragment
fun Invoice.subscriptionWhere(status: String) = sql {
    composeFrom.join(Table<Subscription>().filter { it.status == status }) { s -> s.id == this@subscriptionWhere.subscriptionId }
}

// Usage:
val s = from(i.subscriptionWhere("active"))

Regular function style with parameter:

@SqlFragment
fun subscriptionFor(invoice: Invoice, status: String) = sql {
    composeFrom.join(Table<Subscription>().filter { it.status == status }) { s -> s.id == invoice.subscriptionId }
}

// Usage:
val s = from(subscriptionFor(i, "active"))

Multiple parameterized joins:

@SqlFragment
fun Invoice.subscriptionWhere(status: String) = sql {
    composeFrom.join(Table<Subscription>().filter { it.status == status }) { s -> s.id == this@subscriptionWhere.subscriptionId }
}

@SqlFragment
fun Subscription.planNamed(name: String) = sql {
    composeFrom.join(Table<Plan>().filter { it.planName == name }) { p -> p.id == this@planNamed.planId }
}

@SqlFragment
fun Subscription.accountIn(country: String) = sql {
    composeFrom.join(Table<Account>().filter { it.countryCode == country }) { a -> a.id == this@accountIn.accountId }
}

// All three parameterized joins together:
val q = sql.select {
    val i = from(Table<Invoice>())
    val s = from(i.subscriptionWhere("active"))
    val p = from(s.planNamed("Pro"))
    val a = from(s.accountIn("US"))
    where { i.status == "paid" }
    ...
}

// Generated SQL:
// FROM Invoice i
//   INNER JOIN (SELECT ... FROM Subscription s WHERE s.status = 'active') AS s ON s.id = i.subscription_id
//   INNER JOIN (SELECT ... FROM Plan p WHERE p.plan_name = 'Pro') AS p ON p.id = s.plan_id
//   INNER JOIN (SELECT ... FROM Account a WHERE a.country_code = 'US') AS a ON a.id = s.account_id
// WHERE i.status = 'paid'

Note: Parameterized joins create filtered subqueries. Use plain composeFrom.join(Table<T>()) for flat joins, and put filters in where { } if you prefer that style.

Why Use composeFrom Over Composite Types?

Composite type approach (using @Serializable data class InvSubPlan(...)):

@SqlFragment
fun invoiceWithPlan(): SqlQuery<InvSubPlan> = sql.select {
    val i = from(Table<Invoice>())
    val s = join(Table<Subscription>()) { ... }
    val p = join(Table<Plan>()) { ... }
    InvSubPlan(i, s, p)
}

// Generates nested subqueries:
// FROM (SELECT ... FROM (SELECT ... FROM Invoice i ...) AS it ...) AS it

composeFrom approach:

val q = sql.select {
    val i = from(Table<Invoice>())
    val s = from(i.withSubscription())
    val p = from(s.withPlan())
    ...
}

// Generates flat JOINs:
// FROM Invoice i INNER JOIN Subscription s ON ... INNER JOIN Plan p ON ...

Aspect	Composite Types	composeFrom
SQL output	Nested subqueries	Flat JOINs
Boilerplate	Requires wrapper data classes	None
Field access	row.i.status, row.s.planId	i.status, s.planId
Flexibility	Filter fragments on composite	Direct entity access

Complete Example

// Expression fragment
@SqlFragment
fun String.monthOf(): SqlExpression<String> = sql.expression {
    free("strftime('%Y-%m', ${this@monthOf})").asPure<String>()
}

// Join fragments
@SqlFragment
fun Invoice.withSubscription() = sql {
    composeFrom.join(Table<Subscription>()) { s -> s.id == this@withSubscription.subscriptionId }
}

@SqlFragment
fun Subscription.withPlan() = sql {
    composeFrom.join(Table<Plan>()) { p -> p.id == this@withPlan.planId }
}

@SqlFragment
fun Subscription.withAccount() = sql {
    composeFrom.join(Table<Account>()) { a -> a.id == this@withAccount.accountId }
}

// Query using all fragments
val revenueByPlanAndCountry = sql.select {
    val i = from(Table<Invoice>())
    val s = from(i.withSubscription())
    val p = from(s.withPlan())
    val a = from(s.withAccount())
    where { i.status == "paid" && s.status == "active" }
    val month = i.paidAt.monthOf().use
    groupBy(month, p.planName, a.countryCode)
    sortBy(month to Ord.Asc)
    Result(month, p.planName, a.countryCode, sum(i.amountCents))
}

// Generated SQL:
// SELECT strftime('%Y-%m', i.paid_at) AS month, p.plan_name, a.country_code, sum(i.amount_cents)
// FROM Invoice i
//   INNER JOIN Subscription s ON s.id = i.subscription_id
//   INNER JOIN Plan p ON p.id = s.plan_id
//   INNER JOIN Account a ON a.id = s.account_id
// WHERE i.status = 'paid' AND s.status = 'active'
// GROUP BY strftime('%Y-%m', i.paid_at), p.plan_name, a.country_code
// ORDER BY strftime('%Y-%m', i.paid_at) ASC

CRITICAL: The this@fragmentName Requirement

Just like extension function fragments, you must use this@fragmentName inside the sql { } block:

// ❌ WRONG
@SqlFragment
fun Invoice.withSubscription() = sql {
    composeFrom.join(Table<Subscription>()) { s -> s.id == this.subscriptionId }
}

// ✓ CORRECT
@SqlFragment
fun Invoice.withSubscription() = sql {
    composeFrom.join(Table<Subscription>()) { s -> s.id == this@withSubscription.subscriptionId }
}

Quick Reference

Pattern	Returns	Bind with
Table<T>()	SqlQuery<T>	from()
composeFrom.join(Table<T>()) { ... }	SqlQuery<T>	from()
composeFrom.join(Table<T>().filter { ... }) { ... }	SqlQuery<T>	from()
composeFrom.joinLeft(Table<T>()) { ... }	SqlQuery<T?>	from()

Function styles:

Style	Entity Reference	Example
Extension	this@fragmentName.field	fun Invoice.subscription() = sql { composeFrom.join(...) { s -> s.id == this@subscription.subscriptionId } }
Regular	paramName.field	fun subscriptionFor(inv: Invoice) = sql { composeFrom.join(...) { s -> s.id == inv.subscriptionId } }

When NOT to Use composeFrom Alone

If you find yourself writing the same composeFrom chain in multiple queries:

// Are you doing this repeatedly?
val i = from(Table<Invoice>())
val s = from(i.subscription())
val p = from(s.plan())

Stop. You should bundle these into a composite type instead. See Abstraction Patterns for the Bundle + Destructure + Extend pattern.

Rule of thumb:

• composeFrom = define relationships
• Bundle + Destructure = reuse shared joins across queries

See Also

• Abstraction Patterns - Bundle + Destructure for shared multi-join bases
• Choosing the Right Abstraction Pattern - Decision guide for selecting patterns