# Eager Loading Methods
# withGraphFetched()
queryBuilder = queryBuilder.withGraphFetched(relationExpression, graphOptions);
Fetch a graph of related items for the result of any query (eager loading).
There are two methods that can be used to load relations eagerly: withGraphFetched and withGraphJoined. The main difference is that withGraphFetched uses multiple queries under the hood to fetch the result while withGraphJoined uses a single query and joins to fetch the results. Both methods allow you to do different things which we will go through in detail in the examples below and in the examples of the withGraphJoined method.
As mentioned, this method uses multiple queries to fetch the related objects. ExpressWeb JS performs one query per level in the relation expression tree. For example only two additional queries will be created for the expression children.children
no matter how many children the item has or how many children each of the children have.
Limitations:
- Relations cannot be referenced in the root query because they are not joined.
limit
andpage
methods will work incorrectly when applied to a relation usingmodifyGraph
ormodifiers
because they will be applied on a query that fetches relations for multiple parents. You can uselimit
andpage
for the root query.
See the eager loading section for more examples and RelationExpression for more info about the relation expression language.
See the fetchGraph and $fetchGraph methods if you want to load relations for items already loaded from the database.
About performance:
Note that while withGraphJoined sounds more performant than withGraphFetched, both methods have very similar performance in most cases and withGraphFetched is actually much much faster in some cases where the relationExpression contains multiple many-to-many or has-many relations. The flat record list the db returns for joins can have an incredible amount of duplicate information in some cases. Transferring + parsing that data from the db to node can be very costly, even though the actual joins in the db are very fast. You shouldn't select withGraphJoined blindly just because it sounds more peformant. The three rules of optimization apply here too: 1. Don't optimize 2. Don't optimize yet 3. Profile before optimizing. When you don't actually need joins, use withGraphFetched.
# Arguments
Argument | Type | Description |
---|---|---|
relationExpression | RelationExpression | The relation expression describing which relations to fetch. |
options | GraphOptions | Optional options. |
# Return value
Type | Description |
---|---|
QueryBuilder | this query builder for chaining. |
# Examples
Fetches all Persons
named Arnold with all their pets. 'pets'
is the name of the relation defined in relationMappings.
const people = await Person.query()
.where("firstName", "Arnold")
.withGraphFetched("pets");
console.log(people[0].pets[0].name);
Fetch children
relation for each result Person and pets
and movies
relations for all the children.
const people = await Person.query().withGraphFetched("children.[pets, movies]");
console.log(people[0].children[0].pets[0].name);
console.log(people[0].children[0].movies[0].id);
Relation expressions can also be objects. This is equivalent to the previous example:
const people = await Person.query().withGraphFetched({
children: {
pets: true,
movies: true,
},
});
console.log(people[0].children[0].pets[0].name);
console.log(people[0].children[0].movies[0].id);
Relation results can be filtered and modified by giving modifier function names as arguments for the relations:
const people = await Person.query()
.withGraphFetched(
"children(selectNameAndId).[pets(onlyDogs, orderByName), movies]"
)
.modifiers({
selectNameAndId(builder) {
builder.select("name", "id");
},
orderByName(builder) {
builder.orderBy("name");
},
onlyDogs(builder) {
builder.where("species", "dog");
},
});
console.log(people[0].children[0].pets[0].name);
console.log(people[0].children[0].movies[0].id);
Reusable modifiers can be defined for a model class using modifiers.
class Person extends Model {
static get modifiers() {
return {
// Note that this modifier takes an argument!
filterGender(builder, gender) {
builder.where("gender", gender);
},
defaultSelects(builder) {
builder.select("id", "firstName", "lastName");
},
orderByAge(builder) {
builder.orderBy("age");
},
};
}
}
class Animal extends Model {
static get modifiers() {
return {
orderByName(builder) {
builder.orderBy("name");
},
filterSpecies(builder, species) {
builder.where("species", species);
},
};
}
}
const people = await Person.query().modifiers({
// You can bind arguments to Model modifiers like this
filterFemale(builder) {
builder.modify("filterGender", "female");
},
filterDogs(builder) {
builder.modify("filterSpecies", "dog");
},
}).withGraphFetched(`
children(defaultSelects, orderByAge, filterFemale).[
pets(filterDogs, orderByName),
movies
]
`);
console.log(people[0].children[0].pets[0].name);
console.log(people[0].children[0].movies[0].id);
Filters can also be registered using the modifyGraph method:
const people = await Person.query()
.withGraphFetched("children.[pets, movies]")
.modifyGraph("children", (builder) => {
// Order children by age and only select id.
builder.orderBy("age").select("id");
})
.modifyGraph("children.[pets, movies]", (builder) => {
// Only select `pets` and `movies` whose id > 10 for the children.
builder.where("id", ">", 10);
});
console.log(people[0].children[0].pets[0].name);
console.log(people[0].children[0].movies[0].id);
Relations can be given aliases using the as
keyword:
const people = await Person.query().withGraphFetched(`[
children(orderByAge) as kids .[
pets(filterDogs) as dogs,
pets(filterCats) as cats
movies.[
actors
]
]
]`);
console.log(people[0].kids[0].dogs[0].name);
console.log(people[0].kids[0].movies[0].id);
Eager loading is optimized to avoid the N + 1 queries problem. Consider this query:
const people = await Person.query()
.where("id", 1)
.withGraphFetched("children.children");
console.log(people[0].children.length); // --> 10
console.log(people[0].children[9].children.length); // --> 10
The person has 10 children and they all have 10 children. The query above will return 100 database rows but will generate only three database queries when using withGraphFetched
and only one query when using withGraphJoined
.
# withGraphJoined()
queryBuilder = queryBuilder.withGraphJoined(relationExpression, graphOptions);
Join and fetch a graph of related items for the result of any query (eager loading).
There are two methods that can be used to load relations eagerly: withGraphFetched and withGraphJoined. The main difference is that withGraphFetched uses multiple queries under the hood to fetch the result while withGraphJoined uses a single query and joins to fetch the results. Both methods allow you to do different things which we will go through in detail in the examples below and in the examples of the withGraphJoined method.
As mentioned, this method uses SQL joins (opens new window) to join all the relations defined in the relationExpression
and then parses the result into a graph of model instances equal to the one you get from withGraphFetched
. The main benefit of this is that you can filter the query based on the relations. See the examples.
By default left join is used but you can define the join type using the joinOperation option.
Limitations:
limit
,page
andrange
methods will work incorrectly because they will limit the result set that contains all the result rows in a flattened format. For example the result set of the eager expression children.children will have 10 * 10 * 10 rows assuming that you fetched 10 models that all had 10 children that all had 10 children.
About performance:
Note that while withGraphJoined sounds more performant than withGraphFetched, both methods have very similar performance in most cases and withGraphFetched is actually much much faster in some cases where the relationExpression contains multiple many-to-many or has-many relations. The flat record list the db returns for joins can have an incredible amount of duplicate information in some cases. Transferring + parsing that data from the db to node can be very costly, even though the actual joins in the db are very fast. You shouldn't select withGraphJoined blindly just because it sounds more peformant. The three rules of optimization apply here too: 1. Don't optimize 2. Don't optimize yet 3. Profile before optimizing. When you don't actually need joins, use withGraphFetched.
# Arguments
Argument | Type | Description |
---|---|---|
relationExpression | RelationExpression | The relation expression describing which relations to fetch. |
options | GraphOptions | Optional options. |
# Return value
Type | Description |
---|---|
QueryBuilder | this query builder for chaining. |
# Examples
All examples in withGraphFetched also work with withGraphJoined
. Remember to also study those. The following examples are only about the cases that don't work with withGraphFetched
Using withGraphJoined
all the relations are joined to the main query and you can reference them in any query building method. Note that nested relations are named by concatenating relation names using :
as a separator. See the next example:
const people = await Person.query()
.withGraphJoined("children.[pets, movies]")
.whereIn("children.firstName", ["Arnold", "Jennifer"])
.where("children:pets.name", "Fluffy")
.where("children:movies.name", "like", "Terminator%");
console.log(people[0].children[0].pets[0].name);
console.log(people[0].children[0].movies[0].id);
Using withGraphFetched you can refer to columns only by their name because the column names are unique in the query. With withGraphJoined
you often need to also mention the table name. Consider the following example. We join the relation pets
to a persons
query. Both tables have the id
column. We need to use where('persons.id', '>', 100)
instead of where('id', '>', 100)
so that ExpressWeb JS knows which id
you mean. If you don't do this, you get an ambiguous column name
error.
const people = await Person.query()
.withGraphJoined("pets")
.where("persons.id", ">", 100);
# eager()
WARNING
Deprecated! Will be removed in version 3.0. Use withGraphFetched or withGraphJoined instead.
# eagerAlgorithm()
WARNING
Deprecated! Will be removed in version 3.0. Use withGraphFetched or withGraphJoined instead.
# eagerOptions()
WARNING
Deprecated! Will be removed in version 3.0. Use withGraphFetched or withGraphJoined instead. You can pass options as the second argument for both methods.
# joinEager()
WARNING
Deprecated! Will be removed in version 3.0. Use withGraphJoined instead.
# naiveEager()
WARNING
Deprecated! Will be removed in version 3.0. Use withGraphFetched instead with maxBatchSize: 1 option.
# mergeEager()
WARNING
Deprecated! Will be removed in version 3.0. Use withGraphFetched or withGraphJoined instead. They merge the graphs by default.
# mergeJoinEager()
WARNING
Deprecated! Will be removed in version 3.0. Use withGraphFetched or withGraphJoined instead. They merge the graphs by default.
# mergeNaiveEager()
WARNING
Deprecated! Will be removed in version 3.0. Use withGraphFetched or withGraphJoined instead. They merge the graphs by default.
# eagerObject()
WARNING
Deprecated! Will be removed in version 3.0. Use graphExpressionObject without arguments instead.
# graphExpressionObject()
const builder = Person.query().withGraphFetched("children.pets(onlyId)");
const expr = builder.graphExpressionObject();
console.log(expr.children.pets.$modify);
// prints ["onlyId"]
expr.children.movies = true;
// You can modify the object and pass it back to the `withGraphFetched` method.
builder.withGraphFetched(expr);
Returns the object representation of the relation expression passed to either withGraphFetched
or withGraphJoined
.
See this section for more examples and information about the structure of the returned object.
# Return value
Type | Description |
---|---|
object | Object representation of the current relation expression passed to either withGraphFetched or withGraphJoined . |
# eagerModifiers()
WARNING
Deprecated! Will be removed in version 3.0. Use modifiers without arguments instead.
# Return value
Type | Description |
---|---|
object | Eager modifiers of the query. |
# allowGraph()
queryBuilder = queryBuilder.allowGraph(relationExpression);
Sets the allowed tree of relations to fetch, insert or upsert using withGraphFetched, withGraphJoined, insertGraph or upsertGraph methods.
When using withGraphFetched or withGraphJoined the query is rejected and an error is thrown if the expression passed to the methods is not a subset of the expression passed to allowGraph
. This method is useful when the relation expression comes from an untrusted source like query parameters of a http request.
If the model tree given to the insertGraph or the upsertGraph method isn't a subtree of the given expression, the query is rejected and and error is thrown.
See the examples.
# Arguments
Argument | Type | Description |
---|---|---|
relationExpression | RelationExpression | The allowed relation expression |
# Return value
Type | Description |
---|---|
QueryBuilder | this query builder for chaining. |
# Examples
This will throw because actors
is not allowed.
await Person.query()
.allowGraph("[children.pets, movies]")
.withGraphFetched("movies.actors");
This will not throw:
await Person.query()
.allowGraph("[children.pets, movies]")
.withGraphFetched("children.pets");
Calling allowGraph
multiple times merges the expressions. The following is equivalent to the previous example:
await Person.query()
.allowGraph("children.pets")
.allowGraph("movies")
.withGraphFetched(req.query.eager);
Usage in insertGraph
and upsertGraph
works the same way. The following will not throw.
const insertedPerson = await Person.query()
.allowGraph("[children.pets, movies]")
.insertGraph({
firstName: "Sylvester",
children: [
{
firstName: "Sage",
pets: [
{
name: "Fluffy",
species: "dog",
},
{
name: "Scrappy",
species: "dog",
},
],
},
],
});
This will throw because cousins
is not allowed:
const insertedPerson = await Person.query()
.allowGraph("[children.pets, movies]")
.upsertGraph({
firstName: "Sylvester",
children: [
{
firstName: "Sage",
pets: [
{
name: "Fluffy",
species: "dog",
},
{
name: "Scrappy",
species: "dog",
},
],
},
],
cousins: [sylvestersCousin],
});
You can use clearAllowGraph to clear any previous calls to allowGraph
.
# clearAllowGraph()
Clears all calls to allowGraph
.
# clearWithGraph()
Clears all calls to withGraphFetched
and withGraphJoined
.
# allowEager()
WARNING
Deprecated! Will be removed in version 3.0. Use allowGraph instead. Note that you may need to add clearAllowGraph call too. allowEager
cleared any old expressions automatically, while allowGraph
merges them.
# mergeAllowEager()
WARNING
Deprecated! Will be removed in version 3.0. Use allowGraph instead.
# modifyEager()
WARNING
Deprecated! Will be removed in version 3.0. Use modifyGraph instead.
# modifyGraph()
queryBuilder = queryBuilder.modifyGraph(pathExpression, modifier);
Can be used to modify withGraphFetched
and withGraphJoined
queries.
The pathExpression
is a relation expression that specifies the queries for which the modifier is given.
The following query would filter out the children's pets that are <= 10 years old:
# Arguments
Argument | Type | Description |
---|---|---|
pathExpression | RelationExpression | Expression that specifies the queries for which to give the filter. |
modifier | function(QueryBuilder | string | string[] | A modifier function, model modifier name or an array of model modifier names. |
# Return value
Type | Description |
---|---|
QueryBuilder | this query builder for chaining. |
# Examples
Person.query()
.withGraphFetched("[children.[pets, movies], movies]")
.modifyGraph("children.pets", (builder) => {
builder.where("age", ">", 10);
});
The path expression can have multiple targets. The next example sorts both the pets and movies of the children by id:
Person.query()
.withGraphFetched("[children.[pets, movies], movies]")
.modifyGraph("children.[pets, movies]", (builder) => {
builder.orderBy("id");
});
This example only selects movies whose name contains the word 'Predator':
Person.query()
.withGraphFetched("[children.[pets, movies], movies]")
.modifyGraph("[children.movies, movies]", (builder) => {
builder.where("name", "like", "%Predator%");
});
The modifier can also be a Model modifier name, or an array of them:
Person.query()
.withGraphFetched("[children.[pets, movies], movies]")
.modifyGraph("children.movies", "selectId");
# filterEager()
WARNING
Deprecated! Will be removed in version 3.0. Use modifyGraph instead.
Alias for modifyGraph.