or run

npx @tessl/cli init

Version

Tile

Overview

Evals

Files

docs

database-io.md database-profiles.md index.md plain-sql.md queries.md table-definitions.md type-mappings.md

tile.json

Query Language

Type-safe query composition with monadic operations, joins, aggregations, and filtering using Slick's lifted embedding DSL.

Capabilities

Query Construction

Build type-safe database queries using the Query class and its operations.

/**
 * Core query representation with monadic operations
 * @param E Element type (what you're selecting from)
 * @param U Unpacked type (the Scala type you get)
 * @param C Collection type (usually Seq)
 */
abstract class Query[+E, U, C[_]] extends Rep[C[U]] {
  /** Apply a filter predicate (WHERE clause) */
  def filter(f: E => Rep[Boolean]): Query[E, U, C]
  
  /** Transform each element (SELECT clause) */
  def map[F, G, T](f: E => F)(implicit shape: Shape[_ <: FlatShapeLevel, F, G, T]): Query[G, T, C]
  
  /** Monadic composition for complex queries */
  def flatMap[F, G, T](f: E => Query[F, T, Seq])(implicit shape: Shape[_ <: FlatShapeLevel, F, G, T]): Query[G, T, C]
  
  /** Sort the results (ORDER BY clause) */
  def sortBy[T](f: E => T)(implicit ord: slick.lifted.Ordering[T]): Query[E, U, C]
  
  /** Group results by a key (GROUP BY clause) */
  def groupBy[K](f: E => K): Query[(K, Query[E, U, C]), (K, C[U]), C]
  
  /** Limit number of results */
  def take(num: Int): Query[E, U, C]
  
  /** Skip a number of results */
  def drop(num: Int): Query[E, U, C]
  
  /** Remove duplicate results */
  def distinct: Query[E, U, C]
  
  /** Check if any results exist */
  def exists: Rep[Boolean]
  
  /** Count the number of results */
  def length: Rep[Int]
  
  /** Check if the query is empty */
  def isEmpty: Rep[Boolean]
  
  /** Check if the query is non-empty */
  def nonEmpty: Rep[Boolean]
  
  /** Apply filter conditionally based on Option value */
  def filterOpt[V, T](optValue: Option[V])(f: (E, V) => T)(implicit wt: CanBeQueryCondition[T]): Query[E, U, C]
  
  /** Apply filter conditionally based on boolean condition */
  def filterIf[T](p: Boolean)(f: E => T)(implicit wt: CanBeQueryCondition[T]): Query[E, U, C]
  
  /** Apply filterNot conditionally based on Option value */
  def filterNotOpt[V, T](optValue: Option[V])(f: (E, V) => T)(implicit wt: CanBeQueryCondition[T]): Query[E, U, C]
  
  /** Apply filterNot conditionally based on boolean condition */
  def filterNotIf[T](p: Boolean)(f: E => T)(implicit wt: CanBeQueryCondition[T]): Query[E, U, C]
}

Usage Examples:

// Basic filtering and mapping
val expensiveCoffees = coffees.filter(_.price > 3.0)
val coffeeNames = coffees.map(_.name)
val coffeePricesInCents = coffees.map(c => (c.name, c.price * 100))

// Sorting
val coffeesByName = coffees.sortBy(_.name)
val coffeesByPriceDesc = coffees.sortBy(_.price.desc)

// Combining operations
val cheapCoffeeNames = coffees
  .filter(_.price < 2.0)
  .sortBy(_.name)
  .map(_.name)

// Limiting results
val first5Coffees = coffees.take(5)
val skip10AndTake5 = coffees.drop(10).take(5)

// Existence checks
val hasExpensiveCoffees = coffees.filter(_.price > 5.0).exists
val coffeeCount = coffees.length

// Conditional filtering
val optionalCategory: Option[String] = Some("espresso")
val coffeesByOptionalCategory = coffees.filterOpt(optionalCategory) { (coffee, category) =>
  coffee.category === category
}

val includeExpensive = true
val filteredCoffees = coffees.filterIf(includeExpensive)(_.price > 4.0)

// Conditional negative filtering
val excludeCategory: Option[String] = Some("decaf")
val nonDecafCoffees = coffees.filterNotOpt(excludeCategory) { (coffee, category) =>
  coffee.category === category
}

Join Operations

Combine data from multiple tables using various join types.

/**
 * Inner join between two queries
 */
def join[E2, U2, D[_]](q2: Query[E2, U2, D]): Query[(E, E2), (U, U2), C]

/**
 * Left outer join between two queries  
 */
def joinLeft[E2, U2, D[_]](q2: Query[E2, U2, D]): Query[(E, Rep[Option[E2]]), (U, Option[U2]), C]

/**
 * Right outer join between two queries
 */
def joinRight[E2, U2, D[_]](q2: Query[E2, U2, D]): Query[(Rep[Option[E]], E2), (Option[U], U2), C]

/**
 * Full outer join between two queries
 */
def joinFull[E2, U2, D[_]](q2: Query[E2, U2, D]): Query[(Rep[Option[E]], Rep[Option[E2]]), (Option[U], Option[U2]), C]

/**
 * Cross join (cartesian product) between two queries
 */
def zip[E2, U2, D[_]](q2: Query[E2, U2, D]): Query[(E, E2), (U, U2), C]

Usage Examples:

// Setup table queries
val users = TableQuery[Users]
val orders = TableQuery[Orders]
val products = TableQuery[Products]

// Inner join with explicit condition
val usersWithOrders = for {
  user <- users
  order <- orders if user.id === order.userId
} yield (user.name, order.id)

// Using join method
val userOrderJoin = users.join(orders).on(_.id === _.userId)

// Left join to include users without orders
val allUsersWithOptionalOrders = users.joinLeft(orders).on(_.id === _.userId)

// Complex join with multiple tables
val orderDetails = for {
  order <- orders
  user <- users if order.userId === user.id
  product <- products if order.productId === product.id
} yield (user.name, product.name, order.quantity, product.price)

// Join with filtering
val recentOrdersWithUsers = for {
  order <- orders if order.orderDate > Date.valueOf("2023-01-01")
  user <- users if order.userId === user.id
} yield (user.name, order.orderDate)

Aggregation Functions

Perform aggregate calculations on query results.

/**
 * Count all rows
 */
def length: Rep[Int]

/**
 * Maximum value of a column
 */
def max[B >: U](implicit ord: slick.lifted.Ordering[Option[B]]): Rep[Option[B]]

/**
 * Minimum value of a column  
 */
def min[B >: U](implicit ord: slick.lifted.Ordering[Option[B]]): Rep[Option[B]]

/**
 * Sum of numeric column values
 */
def sum[B >: U](implicit num: Numeric[B]): Rep[Option[B]]

/**
 * Average of numeric column values
 */
def avg[B >: U](implicit num: Numeric[B]): Rep[Option[B]]

Additional aggregate functions available on columns:

/**
 * Count non-null values in a column
 */
def count: Rep[Int]

/**
 * Count distinct values in a column
 */
def countDistinct: Rep[Int]

Usage Examples:

// Basic aggregations
val totalCoffees = coffees.length
val maxPrice = coffees.map(_.price).max
val minPrice = coffees.map(_.price).min
val avgPrice = coffees.map(_.price).avg
val totalValue = coffees.map(_.price).sum

// Group by with aggregations
val pricesByCategory = coffees
  .groupBy(_.category)
  .map { case (category, group) =>
    (category, group.map(_.price).avg, group.length)
  }

// Count distinct
val distinctCategories = coffees.map(_.category).countDistinct

// Complex aggregation query
val userOrderStats = orders
  .groupBy(_.userId)
  .map { case (userId, group) =>
    (userId, group.length, group.map(_.quantity).sum, group.map(_.quantity).avg)
  }

Set Operations

Combine query results using set operations.

/**
 * Union of two queries (removes duplicates)
 */
def union[O >: E, R, D[_]](other: Query[O, R, D]): Query[O, R, C]

/**
 * Union all of two queries (keeps duplicates)  
 */
def unionAll[O >: E, R, D[_]](other: Query[O, R, D]): Query[O, R, C]

/**
 * Intersection of two queries
 */
def intersect[O >: E, R, D[_]](other: Query[O, R, D]): Query[O, R, C]

/**
 * Difference between two queries (elements in first but not second)
 */
def diff[O >: E, R, D[_]](other: Query[O, R, D]): Query[O, R, C]

Usage Examples:

// Union queries
val cheapOrExpensive = coffees.filter(_.price < 2.0).union(coffees.filter(_.price > 4.0))

// All coffee names and tea names
val allBeverageNames = coffees.map(_.name).unionAll(teas.map(_.name))

// Coffee categories that are also tea categories
val commonCategories = coffees.map(_.category).intersect(teas.map(_.category))

// Coffee names that aren't tea names
val coffeeOnlyNames = coffees.map(_.name).diff(teas.map(_.name))

Column Operations

Operations available on individual columns and expressions.

/**
 * Comparison operations
 */
def ===[P2](e: P2): Rep[Boolean]  // Equality
def =!=[P2](e: P2): Rep[Boolean]  // Inequality  
def <[P2](e: P2): Rep[Boolean]    // Less than
def <=[P2](e: P2): Rep[Boolean]   // Less than or equal
def >[P2](e: P2): Rep[Boolean]    // Greater than
def >=[P2](e: P2): Rep[Boolean]   // Greater than or equal

/**
 * Set membership operations
 */
def in[P2](e: Query[P2, _, Seq]): Rep[Boolean]
def inSet[P2](set: Traversable[P2]): Rep[Boolean]
def between[P2, P3](start: P2, end: P3): Rep[Boolean]

/**
 * Null handling
 */
def ifNull[B](alt: B): Rep[B]
def isDefined: Rep[Boolean]    // For Option columns
def isEmpty: Rep[Boolean]      // For Option columns

/**
 * Boolean operations
 */
def &&(e: Rep[Boolean]): Rep[Boolean]  // Logical AND
def ||(e: Rep[Boolean]): Rep[Boolean]  // Logical OR  
def unary_!(): Rep[Boolean]            // Logical NOT

/**
 * Arithmetic operations (for numeric types)
 */
def +[P2, R](e: P2): Rep[R]   // Addition
def -[P2, R](e: P2): Rep[R]   // Subtraction
def *[P2, R](e: P2): Rep[R]   // Multiplication
def /[P2, R](e: P2): Rep[R]   // Division
def %[P2, R](e: P2): Rep[R]   // Modulo

/**
 * String operations
 */
def ++[P2](e: P2): Rep[String]           // String concatenation
def like(pattern: Rep[String]): Rep[Boolean]  // Pattern matching
def startsWith(prefix: Rep[String]): Rep[Boolean]
def endsWith(suffix: Rep[String]): Rep[Boolean]

Usage Examples:

// Comparison operations
val expensiveCoffees = coffees.filter(_.price > 3.0)
val specificCoffee = coffees.filter(_.name === "Latte")
val priceRange = coffees.filter(_.price.between(2.0, 4.0))

// Set membership
val popularCoffees = coffees.filter(_.name.inSet(Set("Latte", "Cappuccino", "Espresso")))

// String operations  
val latteVariants = coffees.filter(_.name.like("%Latte%"))
val coffeeDescriptions = coffees.map(c => c.name ++ " - $" ++ c.price.asColumnOf[String])

// Null handling
val activeUsers = users.filter(_.deletedAt.isEmpty)
val usersWithDefaultRole = users.map(u => (u.name, u.role.ifNull("user")))

// Boolean logic
val affordablePopularCoffees = coffees.filter(c => c.price < 3.0 && c.name.like("%Latte%"))

// Arithmetic
val pricesWithTax = coffees.map(c => (c.name, c.price * 1.08))
val discountedPrices = coffees.map(c => c.price - (c.price * 0.1))

Subqueries

Use subqueries for complex filtering and selection.

/**
 * Check if any rows exist in a subquery
 */
def exists: Rep[Boolean]

/**
 * Filter based on subquery results
 */
def in[P2](subquery: Query[P2, _, Seq]): Rep[Boolean]

/**
 * Use subquery in SELECT clause
 */
def map[F](f: E => F): Query[F, _, C]

Usage Examples:

// Exists subquery
val usersWithOrders = users.filter(user => 
  orders.filter(_.userId === user.id).exists
)

// In subquery
val usersWhoOrderedExpensiveItems = users.filter(_.id.in(
  orders.filter(_.total > 100.0).map(_.userId)
))

// Correlated subquery
val usersWithOrderCount = users.map { user =>
  (user.name, orders.filter(_.userId === user.id).length)
}

// Scalar subquery
val avgOrderValue = orders.map(_.total).avg
val aboveAverageOrders = orders.filter(_.total > avgOrderValue)

For Comprehensions

Use Scala's for comprehension syntax for complex queries.

Usage Examples:

// Basic for comprehension
val coffeeNamesAndPrices = for {
  coffee <- coffees
  if coffee.price > 2.0
} yield (coffee.name, coffee.price)

// Multiple table join
val userOrderDetails = for {
  user <- users
  order <- orders if order.userId === user.id
  if order.total > 50.0
} yield (user.name, order.id, order.total)

// Complex filtering and joining
val recentOrderSummary = for {
  order <- orders if order.orderDate > Date.valueOf("2023-01-01")
  user <- users if order.userId === user.id
  product <- products if order.productId === product.id
} yield (user.name, product.name, order.quantity, order.total)

// Nested conditions
val premiumUserExpensiveOrders = for {
  user <- users if user.accountType === "premium"
  order <- orders if order.userId === user.id && order.total > 100.0
} yield (user.name, order.total)

Types

abstract class Query[+E, U, C[_]] extends Rep[C[U]]
trait Rep[T]

// Query result types
type SimpleQuery[E, U] = Query[E, U, Seq]
type UnpackQuery[E] = Query[E, E, Seq]

// Shape types for query mapping
trait Shape[Level <: ShapeLevel, -Mixed_, Unpacked_, Packed_]
trait ProvenShape[U] extends Shape[FlatShapeLevel, U, U, U]

// Ordering for sortBy operations
trait Ordering[T]
object Ordering {
  def apply[T](implicit ord: scala.math.Ordering[T]): Ordering[T]
  implicit def columnOrdering[T](implicit tm: TypedType[T], ord: scala.math.Ordering[T]): Ordering[Rep[T]]
}

Version

Tile

Files

queries.md.css-3qkkll{font-size:var(--chakra-font-sizes-sm);font-weight:var(--chakra-font-weights-normal);color:var(--chakra-colors-gray-300);}docs/

Query Language

Capabilities

Query Construction

Join Operations

Aggregation Functions

Set Operations

Column Operations

Subqueries

For Comprehensions

Types

queries.mddocs/