Relation queries
The Serverpod query framework supports filtering on, sorting on, and including relational data structures. In SQL this is often achieved using a join operation. The functionality is available if there exists any one-to-one or one-to-many object relations between two models.
Include relational data
To include relational data in a query, use the include
method. The include
method has a typed interface and contains all the declared relations in your yaml file.
var employee = await Employee.db.findById(
session,
employeeId,
include: Employee.include(
address: Address.include(),
),
);
The example above return a employee including the related address object.
Nested includes
It is also possible to include deeply nested objects.
var employee = await Employee.db.findById(
session,
employeeId,
include: Employee.include(
company: Company.include(
address: Address.include(),
),
),
);
The example above returns an employee including the related company object that has the related address object included.
Any relational object can be included or not when making a query but only the includes that are explicitly defined will be included in the result.
var user = await Employee.db.findById(
session,
employeeId,
include: Employee.include(
address: Address.include(),
company: Company.include(
address: Address.include(),
),
),
);
The example above includes several different objects configured by specifying the named parameters.
Include relational lists
Including a list of objects (1:n relation) can be done with the special includeList
method. In the simplest case, the entire list is included.
var user = await Company.db.findById(
session,
employeeId,
include: Company.include(
employees: Employee.includeList(),
),
);
The example above returns a company with all related employees included.
Nested includes
The includeList
method works slightly differently from a normal include
and to include nested objects the includes
field must be used. When including something on a list it means that every entry in the list will each have access to the nested object.
var user = await Company.db.findById(
session,
employeeId,
include: Company.include(
employees: Employee.includeList(
includes: Employee.include(
address: Address.include(),
),
),
),
);
The example above returns a company with all related employees included. Each employee will have the related address object included.
It is even possible to include lists within lists.
var user = await Company.db.findById(
session,
employeeId,
include: Company.include(
employees: Employee.includeList(
includes: Employee.include(
tools: Tool.includeList(),
),
),
),
);
The example above returns a company with all related employees included. Each employee will have the related tools list included.
For each call to includeList (nested or not) the Serverpod Framework will perform one additional query to the database.
Filter and sort
When working with large datasets, it's often necessary to filter and sort the records to retrieve the most relevant data. Serverpod offers methods to refine the included list of related objects: