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]
}

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]

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

Note : This method is not type-safe yet. Use the Model#where method instead.

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

Note : This method is not type-safe yet.

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

Using Model.where(conditions) finds records by org.squeryl.dsl.ast.LogicalBoolean. 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

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

Note : This method is not type-safe yet. Use the Model#where method instead.

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]

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

Group and Having

No implement yet.

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(19).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

reload