Relations allow foreign key relationships to appear as collection like objects. There are two types of relations in Squeryl : OneToMany and ManyToMany, and each has a stateful counterpart.
Stateful relations owe their name to the fact that they maintain members of the relation in memory. A stateful relation loads all of it’s members in memory when it is accessed for the first time, and they are kept with the relation. When an object is inserted or updated, the modified (or inserted) object is persisted in the database and kept in memory ‘inside’ the relation object.
From here on, stateless relations will be referred simply as relations, stateful relations are described here .
The traits OneToMany and ManyToOne both extends Query, they have an assign method
that assigns the keys while leaving the database unchanged. The associate calls assign
and persists the changes to the database.
A ManyToMany relation has a table in the middle called the association table, it has a foreign key for the left and right side. It is a symmetrical relation, so the choice of left and right is arbitrary.
In the following example we have a course to student relation, in which the left side is the course and the right side is the student.
The association table can have other fields besides the left and right foreign keys, in the example, CourseSubscription has a grade:Float column.
Examples of many to many relation usage :
The org.squeryl.Schema.create method will create relations based on the declarations in the schema as the next example shows, note the different signatures of constrainReference. The applyDefaultForeignKeyPolicy method will determine how the relations are declared at the schema level, each relation can then redefine the default.
Calling unConstrainReference in applyDefaultForeignKeyPolicy will cause foreign key constraints to be inhibited.
A significant part of optimizing a database abstraction layer is to choose for every situation the
right balance between fine and large grained retrieval, and the optimal mix of laziness and eagerness.
Relations are lazy, and are subject to the N+1 problem . If we have a deep relation chain, it is often preferable to use a query, for example if we have an N level relation chain : R1 x R2,…, Rn, looping through relations will cause :1* |R2| * |R3| * …. * |Rn-1| database trips
where |Rk| is the number of rows in the Rk part of the relation
In such a case, a single query is clearly preferable since it will fetch all objects in a single database round trip.