Stateful vs Stateless relations
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 .
OneToMany
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.
ManyToMany
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 trait ManyToMany[O,A]
Defining foreign key constraints with relations
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.