# 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 andwithGraphJoined. The main difference is thatwithGraphFetched uses multiple queries under the hood to fetch the result whilewithGraphJoined 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 thewithGraphJoined method.

As mentioned, this method uses multiple queries to fetch the related objects. Objection performs one query per level in the relation expression tree. For example only two additional queries will be created for the expressionchildren.children no matter how many children the item has or how many children each of the children have. This algorithm is explained in detail inthis blog post(opens new window) (note thatwithGraphFetched method used to be calledeager).

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 theeager loading section for more examples andRelationExpression for more info about the relation expression language.

See thefetchGraph and$fetchGraph methods if you want to load relations for items already loaded from the database.

About performance:

Note that whilewithGraphJoined sounds more performant thanwithGraphFetched, both methods have very similar performance in most cases andwithGraphFetched is actually much much faster in some cases where therelationExpression 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 selectwithGraphJoined 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, usewithGraphFetched.

# Arguments

# Return value

# Examples

Fetches allPersons named Arnold with all their pets.'pets' is the name of the relation defined inrelationMappings.

const people=await Person.query().where('firstName','Arnold').withGraphFetched('pets');console.log(people[0].pets[0].name);

Fetchchildren relation for each result Person andpets andmoviesrelations 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 usingmodifiers. Also see themodifiers recipe.

classPersonextendsModel{staticgetmodifiers(){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');}};}}classAnimalextendsModel{staticgetmodifiers(){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 thisfilterFemale(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 themodifyGraph 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 theas 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);// --> 10console.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 usingwithGraphFetched and only one query when usingwithGraphJoined.

# 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 andwithGraphJoined. The main difference is thatwithGraphFetched uses multiple queries under the hood to fetch the result whilewithGraphJoined 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 thewithGraphJoined method.

As mentioned, this method usesSQL joins(opens new window) to join all the relations defined in therelationExpression and then parses the result into a graph of model instances equal to the one you get fromwithGraphFetched. 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 thejoinOperation 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 whilewithGraphJoined sounds more performant thanwithGraphFetched, both methods have very similar performance in most cases andwithGraphFetched is actually much much faster in some cases where therelationExpression 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 selectwithGraphJoined 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, usewithGraphFetched.

# Arguments

# Return value

# Examples

All examples inwithGraphFetched also work withwithGraphJoined. Remember to also study those. The following examples are only about the cases that don't work withwithGraphFetched

UsingwithGraphJoined 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);

UsingwithGraphFetched you can refer to columns only by their name because the column names are unique in the query. WithwithGraphJoined you often need to also mention the table name. Consider the following example. We join the relationpets to apersons query. Both tables have theid column. We need to usewhere('persons.id', '>', 100) instead ofwhere('id', '>', 100) so that objection knows whichid you mean. If you don't do this, you get anambiguous column name error.

const people=await Person.query().withGraphJoined('pets').where('persons.id','>',100);

# 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 eitherwithGraphFetched orwithGraphJoined.

Seethis section for more examples and information about the structure of the returned object.

# Return value

# allowGraph()

queryBuilder= queryBuilder.allowGraph(relationExpression);

Sets the allowed tree of relations to fetch, insert or upsert usingwithGraphFetched,withGraphJoined,insertGraph orupsertGraph methods.

When usingwithGraphFetched orwithGraphJoined the query is rejected and an error is thrown if theexpression passed to the methods is not a subset of theexpression passed toallowGraph. 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 theinsertGraph or theupsertGraph method isn't a subtree of the givenexpression, the query is rejected and and error is thrown.

See the examples.

# Arguments

# Return value

# Examples

This will throw becauseactors 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');

CallingallowGraph 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 ininsertGraph andupsertGraph 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 becausecousins 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 useclearAllowGraph to clear any previous calls toallowGraph.

# clearAllowGraph()

Clears all calls toallowGraph.

# clearWithGraph()

Clears all calls towithGraphFetched andwithGraphJoined.

# modifyGraph()

queryBuilder= queryBuilder.modifyGraph(pathExpression, modifier);

Can be used to modifywithGraphFetched andwithGraphJoined queries.

ThepathExpression 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

# Return value

# 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 aModel modifier name, or an array of them:

Person.query().withGraphFetched('[children.[pets, movies], movies]').modifyGraph('children.movies','selectId');

← Mutating Methods Join Methods →