Query interface

Code examples throughout this guide will refer to one or more of the following models:

package models

case class Client(name: String, age: Int) extends ActiveRecord {
  lazy val orders = hasMany[Order]
  lazy val roles = hasAndBelongToMany[Role]
} 

object Client extends ActiveRecordCompanion[Client]

case class Order(price: Int) extends ActiveRecord {
  val clientId: Option[Long] = None
  val categoryId: Option[Long] = None
  lazy val client = belongsTo[Client]
  lazy val category = belongsTo[Category]
}

object Order extends ActiveRecordCompanion[Order]

case class Role(name: String) extends ActiveRecord {
  lazy val clients = hasAndBelongToMany[Role]
}

object Role extends ActiveRecordCompanion[Role]

case class Category(name: String) extends ActiveRecord

object Category extends ActiveRecordCompanion[Category] {
  lazy val orders = hasMany[Order]
}

object Tables extends ActiveRecordTables {
  val clients = table[Client]
  val orders = table[Order]
  val roles = table[Role]
}

Single object finder

`find`

Using Model.find(primaryKey), you can retrieve the object Option[Model] corresponding to the specified primary key that matches any supplied options. For example:

val client = Client.find(10)
// => Some(Client) or None

`findBy`

Using Model.findBy((key, value)*) finds the first record by multiple fieldnames and values. For example:

val john = Client.findBy("name", "john") 
// => Some(Client("john")) or None

val john25 = Client.findBy("name" -> "john", "age" -> 25)
// => Some(Client("john", 25)) or None

Find or build a new object

`findByOrCreate`

Using Model.findByOrCreate(model, fields*) returns either the record that already exists or the new record. For example:

val client = Client.findByOrCreate(Client("john", 25), "name", "age")
// => found Client("john", 25) or created Client("john", 25)

Using `Iterable` methods

Defined implicit conversion from companion model Model to scala.collection.Iterable. For example:

val client1 = Client.head
// => First client or java.util.NoSuchElementException

val client2 = Client.headOption
// => First Option[Client] or None

val client3 = Client.lastOption
// => Last Option[Client] or None

val (adults, children) = Client.partition(_.age >= 20)
// => parts of clients

Multiple objects finder

`where`

To retrieve records from the database, you can use the where method with the retrieval conditions. For example:

Clients.where(_.name === "john").toList
Clients.where(_.name === "john").headOption
Clients.where(c => c.name === "john" and c.age le 25).toList
Clients.where(c => c.name === "john" and c.age.~ > 25).toList
Clients.where(_.name === "john").where(_.age.~ > 25).toList
Clients.where(_.name like "john%").toList
Clients.where(_.name regex ".*oh.*").toList

Boolean operators:

not, isNull, isNotNull, between, ===, <, lt, >, gt, <=, lte, <=, gte, <>, exists, notExists, in, notIn

Arithmetic operators:

plus, +, minus, -, times, *, div, /

String operators:

|| (concatenation), lower, upper, like, regex

Note : To avoid the ambiguity in the numeric fields, you must call .~ method before using comparison operators like < or >.

`not`

Client.not(_.name == "foobar").toList
Client.where(_.age.~ > 24).not(_.name == "foobar").toList

Dynamic where clause construction

Using inhibitWhen from Squeryl.

// returns all clients if name == None
def clients(name: Option[String]): List[Client] = {
  Clients.where(_.name === name.inhibitWhen(name == None)).toList
}

// using .? operator
def clients(name: Option[String]): List[Client] = {
  Clients.where(_.name === name.?).toList
}

`findAllBy`

Using Model.findByAll((key, value)*) finds records by multiple fieldnames and values. For example:

val clients = Clients.findAllBy("name" -> "john", "age" -> 25)
// => found clients

Ordering

`orderBy`

To retrieve records from the database in a specific order, you can use the orderBy method:

// simple order (ORDER BY user_name):
Client.orderBy(_.name)

// set order (use for 'asc' or 'desc'):
Client.orderBy(_.name asc)

// after other query (it's non-nested query!):
Client.where(_.name === "john").orderBy(_.name desc)

// ordering by multiple fields:
Client.orderBy(_.name asc, _.age desc)

Selecting specific fields

`select`

Query will return the List[S] from any legal Scala expression, you can use the select method:

Client.select(_.name).toList
// => List[String]

Client.select(u => (u.name, u.age)).toList
// => List[(String, Int)]

// Arbitrary select expression on the database side
Client.select(c => &("name:" || c.name || ", age:" || c.age)).toList
// => List[String]

If you would like to only grab a single record per unique value in a certain field, you can use distinct:

Client.select(_.name).distinct.toList

This would generate SQL like:

SELECT DISTINCT name FROM clients

Limit and Offset

`limit`

limit returns only specified count.

Client.limit(10)

`page`

page(offset, pageLength) returns paginated records.

Client.page(2, 5)

Existence of objects

`exists`

exists(condition) returns true/false.

Client.exists(_.name === "john")
Client.exists(_.name like "jo%")
Client.exists(c => c.name like "jo%" and c.age.~ > 25)
Client.where(_.age.~ > 20).exists(_.name like "john")

Calculations

`count`

count returns records counts.

Client.where(_.name regex ".*oh.*").count
// => 20 or 0

`max`

Client.max(_.age)
Client.maximum(_.age)
// => Some(20) or None

`min`

Client.min(_.age)
Client.minimum(_.age)
// => Some(20) or None

`avg`

Client.avg(_.age)
Client.average(_.age)
// => Some(20) or None

`sum`

Client.sum(_.age)
// => Some(20) or None

`compute`

Client.compute(client => max(client.age))
// => Some(20) or None

Group and Having

Note : Available version 0.3.1 or later.

Allows to specify a GROUP clause.

SELECT "clients".* FROM "clients" GROUP BY name

`count`

count returns records counts by groups.

Client.group(_.age).count
// => Map(10 -> 1, 20 -> 2, 25 -> 1, 30 -> 5)

`max`

Client.group(_.name).max(_.age)
Client.group(_.name).maximum(_.age)
// => Map("tom" -> Some(20), "john" -> Some(30))

`min`

Client.group(_.name).min(_.age)
Client.group(_.name).minimum(_.age)
// => Map("tom" -> Some(10), "john" -> Some(25))

`avg`

Client.group(_.name).avg(_.age)
Client.group(_.name).average(_.age)
// => Map("tom" -> Some(22.4), "john" -> Some(28.5))

`sum`

Client.group(_.name).sum(_.age)
// => Map("tom" -> Some(80), "john" -> Some(120))

`compute`

Client.group(_.name).compute(client => max(client.age))
// => Map("tom" -> Some(20), "john" -> Some(30))

`having`

Allows to specify a HAVING clause.

Client.group(_.name).having(_.name === "tom").max(_.age)
// => Map("tom" -> Some(20))

Client.group(_.name).having(client => avg(client.age) > 20).sum(_.age)
// => Map("john" -> Some(120))

Joining tables

`joins`

joins[OtherModel]((SelfModel, OtherModel) => joinOnCondition)) returns joined query:

Client.joins[Order](
  // join on
  (client, order) => client.id === order.clientId
).where(
  (client, order) => client.age.~ < 20 and order.price.~ > 10000 
).select(
  (client, order) => (client.name, client.age, order.price)
).toList

This produces:

Select
  clients.name,
  clients.age,
  orders.price
From
  clients inner join orders on (clients.id = orders.client_id)
Where
  ((clients.age < 20) and (groups4.price > 10000))

Joining Multiple Associations

joins[OtherModel1, OtherModel2]((SelfModel, OtherModel1, OtherModel2) => (joinOnCondition1, joinOnCondition2)) returns multiple joined query:

Client.joins[Order, Category](
  // join on
  (client, order, category) => (client.id === order.clientId, order.categoryId === category.id)
).where(
  (client, order, category) => client.age.~ < 20 and order.price.~ > 10000 and category.name === "computer"
).select(
  (client, order, category) => (client.name, client.age, order.price, category.name)
).toList

This produces:

Select
  clients.name,
  clients.age,
  orders.price,
  categories.name
From users
  inner join orders on (users.group_id = orders.id)
  inner join categories on (orders.category_id = categories.id)
Where
  ((clients.age < 20) and (orders.price > 10000) and (categories.name = "computer"))

Eager loading associations

`includes`

You can load association while loading object using includes(association). (solution for the n+1 query problem) For example:

Order.includes(_.client).limit(10).foreach { order =>
  println(order.client.name)
}

This produces:

Select
  orders.price,
  orders.id
From
  orders
limit 10 offset 0;

Select
  clients.name,
  clients.age,
  clients.id
From
  clients inner join orders on (clients.id = orders.client_id)
Where
  (orders.id in (1,2,3,4,5,6,7,8,9,10))

Cache control

relation#reload reloads the attributes of this object from the database:

val orders = Order.where(_.price > 2000)
println(orders.toList) // execute SQL query, and cached query.     (1)
Order(3000).save
println(orders.toList) // non-execute SQL query. equal the list of (1)
println(orders.reload) // execute SQL query, and cached query.

Query interface

Single object finder

find

findBy

Find or build a new object

findByOrCreate

Using Iterable methods

Multiple objects finder

where

not

Dynamic where clause construction

findAllBy

Ordering

orderBy

Selecting specific fields

select

Limit and Offset

limit

page

Existence of objects

exists

Calculations

count

max

min

avg

sum

compute

Group and Having

count

max

min

avg

sum

compute

having

Joining tables

joins

Joining Multiple Associations

Eager loading associations

includes

Cache control

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Clone this wiki locally

`find`

`findBy`

`findByOrCreate`

Using `Iterable` methods

`where`

`not`

`findAllBy`

`orderBy`

`select`

`limit`

`page`

`exists`

`count`

`max`

`min`

`avg`

`sum`

`compute`

`count`

`max`

`min`

`avg`

`sum`

`compute`

`having`

`joins`

`includes`