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

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

Eager loading associations

includes

Cache

reload