0
# Query Language
1

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

4
## Capabilities
5

6
### Query Construction
7

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

10
```scala { .api }
11
/**
12
 * Core query representation with monadic operations
13
 * @param E Element type (what you're selecting from)
14
 * @param U Unpacked type (the Scala type you get)
15
 * @param C Collection type (usually Seq)
16
 */
17
abstract class Query[+E, U, C[_]] extends Rep[C[U]] {
18
  /** Apply a filter predicate (WHERE clause) */
19
  def filter(f: E => Rep[Boolean]): Query[E, U, C]
20
  
21
  /** Transform each element (SELECT clause) */
22
  def map[F, G, T](f: E => F)(implicit shape: Shape[_ <: FlatShapeLevel, F, G, T]): Query[G, T, C]
23
  
24
  /** Monadic composition for complex queries */
25
  def flatMap[F, G, T](f: E => Query[F, T, Seq])(implicit shape: Shape[_ <: FlatShapeLevel, F, G, T]): Query[G, T, C]
26
  
27
  /** Sort the results (ORDER BY clause) */
28
  def sortBy[T](f: E => T)(implicit ord: slick.lifted.Ordering[T]): Query[E, U, C]
29
  
30
  /** Group results by a key (GROUP BY clause) */
31
  def groupBy[K](f: E => K): Query[(K, Query[E, U, C]), (K, C[U]), C]
32
  
33
  /** Limit number of results */
34
  def take(num: Int): Query[E, U, C]
35
  
36
  /** Skip a number of results */
37
  def drop(num: Int): Query[E, U, C]
38
  
39
  /** Remove duplicate results */
40
  def distinct: Query[E, U, C]
41
  
42
  /** Check if any results exist */
43
  def exists: Rep[Boolean]
44
  
45
  /** Count the number of results */
46
  def length: Rep[Int]
47
  
48
  /** Check if the query is empty */
49
  def isEmpty: Rep[Boolean]
50
  
51
  /** Check if the query is non-empty */
52
  def nonEmpty: Rep[Boolean]
53
  
54
  /** Apply filter conditionally based on Option value */
55
  def filterOpt[V, T](optValue: Option[V])(f: (E, V) => T)(implicit wt: CanBeQueryCondition[T]): Query[E, U, C]
56
  
57
  /** Apply filter conditionally based on boolean condition */
58
  def filterIf[T](p: Boolean)(f: E => T)(implicit wt: CanBeQueryCondition[T]): Query[E, U, C]
59
  
60
  /** Apply filterNot conditionally based on Option value */
61
  def filterNotOpt[V, T](optValue: Option[V])(f: (E, V) => T)(implicit wt: CanBeQueryCondition[T]): Query[E, U, C]
62
  
63
  /** Apply filterNot conditionally based on boolean condition */
64
  def filterNotIf[T](p: Boolean)(f: E => T)(implicit wt: CanBeQueryCondition[T]): Query[E, U, C]
65
}
66
```
67

68
**Usage Examples:**
69

70
```scala
71
// Basic filtering and mapping
72
val expensiveCoffees = coffees.filter(_.price > 3.0)
73
val coffeeNames = coffees.map(_.name)
74
val coffeePricesInCents = coffees.map(c => (c.name, c.price * 100))
75

76
// Sorting
77
val coffeesByName = coffees.sortBy(_.name)
78
val coffeesByPriceDesc = coffees.sortBy(_.price.desc)
79

80
// Combining operations
81
val cheapCoffeeNames = coffees
82
  .filter(_.price < 2.0)
83
  .sortBy(_.name)
84
  .map(_.name)
85

86
// Limiting results
87
val first5Coffees = coffees.take(5)
88
val skip10AndTake5 = coffees.drop(10).take(5)
89

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

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

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

103
// Conditional negative filtering
104
val excludeCategory: Option[String] = Some("decaf")
105
val nonDecafCoffees = coffees.filterNotOpt(excludeCategory) { (coffee, category) =>
106
  coffee.category === category
107
}
108
```
109

110
### Join Operations
111

112
Combine data from multiple tables using various join types.
113

114
```scala { .api }
115
/**
116
 * Inner join between two queries
117
 */
118
def join[E2, U2, D[_]](q2: Query[E2, U2, D]): Query[(E, E2), (U, U2), C]
119

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

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

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

135
/**
136
 * Cross join (cartesian product) between two queries
137
 */
138
def zip[E2, U2, D[_]](q2: Query[E2, U2, D]): Query[(E, E2), (U, U2), C]
139
```
140

141
**Usage Examples:**
142

143
```scala
144
// Setup table queries
145
val users = TableQuery[Users]
146
val orders = TableQuery[Orders]
147
val products = TableQuery[Products]
148

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

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

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

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

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

175
### Aggregation Functions
176

177
Perform aggregate calculations on query results.
178

179
```scala { .api }
180
/**
181
 * Count all rows
182
 */
183
def length: Rep[Int]
184

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

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

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

200
/**
201
 * Average of numeric column values
202
 */
203
def avg[B >: U](implicit num: Numeric[B]): Rep[Option[B]]
204
```
205

206
Additional aggregate functions available on columns:
207

208
```scala { .api }
209
/**
210
 * Count non-null values in a column
211
 */
212
def count: Rep[Int]
213

214
/**
215
 * Count distinct values in a column
216
 */
217
def countDistinct: Rep[Int]
218
```
219

220
**Usage Examples:**
221

222
```scala
223
// Basic aggregations
224
val totalCoffees = coffees.length
225
val maxPrice = coffees.map(_.price).max
226
val minPrice = coffees.map(_.price).min
227
val avgPrice = coffees.map(_.price).avg
228
val totalValue = coffees.map(_.price).sum
229

230
// Group by with aggregations
231
val pricesByCategory = coffees
232
  .groupBy(_.category)
233
  .map { case (category, group) =>
234
    (category, group.map(_.price).avg, group.length)
235
  }
236

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

240
// Complex aggregation query
241
val userOrderStats = orders
242
  .groupBy(_.userId)
243
  .map { case (userId, group) =>
244
    (userId, group.length, group.map(_.quantity).sum, group.map(_.quantity).avg)
245
  }
246
```
247

248
### Set Operations
249

250
Combine query results using set operations.
251

252
```scala { .api }
253
/**
254
 * Union of two queries (removes duplicates)
255
 */
256
def union[O >: E, R, D[_]](other: Query[O, R, D]): Query[O, R, C]
257

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

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

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

274
**Usage Examples:**
275

276
```scala
277
// Union queries
278
val cheapOrExpensive = coffees.filter(_.price < 2.0).union(coffees.filter(_.price > 4.0))
279

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

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

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

290
### Column Operations
291

292
Operations available on individual columns and expressions.
293

294
```scala { .api }
295
/**
296
 * Comparison operations
297
 */
298
def ===[P2](e: P2): Rep[Boolean]  // Equality
299
def =!=[P2](e: P2): Rep[Boolean]  // Inequality  
300
def <[P2](e: P2): Rep[Boolean]    // Less than
301
def <=[P2](e: P2): Rep[Boolean]   // Less than or equal
302
def >[P2](e: P2): Rep[Boolean]    // Greater than
303
def >=[P2](e: P2): Rep[Boolean]   // Greater than or equal
304

305
/**
306
 * Set membership operations
307
 */
308
def in[P2](e: Query[P2, _, Seq]): Rep[Boolean]
309
def inSet[P2](set: Traversable[P2]): Rep[Boolean]
310
def between[P2, P3](start: P2, end: P3): Rep[Boolean]
311

312
/**
313
 * Null handling
314
 */
315
def ifNull[B](alt: B): Rep[B]
316
def isDefined: Rep[Boolean]    // For Option columns
317
def isEmpty: Rep[Boolean]      // For Option columns
318

319
/**
320
 * Boolean operations
321
 */
322
def &&(e: Rep[Boolean]): Rep[Boolean]  // Logical AND
323
def ||(e: Rep[Boolean]): Rep[Boolean]  // Logical OR  
324
def unary_!(): Rep[Boolean]            // Logical NOT
325

326
/**
327
 * Arithmetic operations (for numeric types)
328
 */
329
def +[P2, R](e: P2): Rep[R]   // Addition
330
def -[P2, R](e: P2): Rep[R]   // Subtraction
331
def *[P2, R](e: P2): Rep[R]   // Multiplication
332
def /[P2, R](e: P2): Rep[R]   // Division
333
def %[P2, R](e: P2): Rep[R]   // Modulo
334

335
/**
336
 * String operations
337
 */
338
def ++[P2](e: P2): Rep[String]           // String concatenation
339
def like(pattern: Rep[String]): Rep[Boolean]  // Pattern matching
340
def startsWith(prefix: Rep[String]): Rep[Boolean]
341
def endsWith(suffix: Rep[String]): Rep[Boolean]
342
```
343

344
**Usage Examples:**
345

346
```scala
347
// Comparison operations
348
val expensiveCoffees = coffees.filter(_.price > 3.0)
349
val specificCoffee = coffees.filter(_.name === "Latte")
350
val priceRange = coffees.filter(_.price.between(2.0, 4.0))
351

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

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

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

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

366
// Arithmetic
367
val pricesWithTax = coffees.map(c => (c.name, c.price * 1.08))
368
val discountedPrices = coffees.map(c => c.price - (c.price * 0.1))
369
```
370

371
### Subqueries
372

373
Use subqueries for complex filtering and selection.
374

375
```scala { .api }
376
/**
377
 * Check if any rows exist in a subquery
378
 */
379
def exists: Rep[Boolean]
380

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

386
/**
387
 * Use subquery in SELECT clause
388
 */
389
def map[F](f: E => F): Query[F, _, C]
390
```
391

392
**Usage Examples:**
393

394
```scala
395
// Exists subquery
396
val usersWithOrders = users.filter(user => 
397
  orders.filter(_.userId === user.id).exists
398
)
399

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

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

410
// Scalar subquery
411
val avgOrderValue = orders.map(_.total).avg
412
val aboveAverageOrders = orders.filter(_.total > avgOrderValue)
413
```
414

415
### For Comprehensions
416

417
Use Scala's for comprehension syntax for complex queries.
418

419
**Usage Examples:**
420

421
```scala
422
// Basic for comprehension
423
val coffeeNamesAndPrices = for {
424
  coffee <- coffees
425
  if coffee.price > 2.0
426
} yield (coffee.name, coffee.price)
427

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

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

442
// Nested conditions
443
val premiumUserExpensiveOrders = for {
444
  user <- users if user.accountType === "premium"
445
  order <- orders if order.userId === user.id && order.total > 100.0
446
} yield (user.name, order.total)
447
```
448

449
## Types
450

451
```scala { .api }
452
abstract class Query[+E, U, C[_]] extends Rep[C[U]]
453
trait Rep[T]
454

455
// Query result types
456
type SimpleQuery[E, U] = Query[E, U, Seq]
457
type UnpackQuery[E] = Query[E, E, Seq]
458

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

463
// Ordering for sortBy operations
464
trait Ordering[T]
465
object Ordering {
466
  def apply[T](implicit ord: scala.math.Ordering[T]): Ordering[T]
467
  implicit def columnOrdering[T](implicit tm: TypedType[T], ord: scala.math.Ordering[T]): Ordering[Rep[T]]
468
}
469
```