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. Objection 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. This algorithm is explained in detail in this blog post (note that withGraphFetched method used to be called eager).

Limitations:

Relations cannot be referenced in the root query because they are not joined.
limit and page methods will work incorrectly when applied to a relation using modifyGraph or modifiers because they will be applied on a query that fetches relations for multiple parents. You can use limit and page for the root query. To limit the related items per parent, set the maxBatchSize graph option to 1 so that each parent gets its own query.
first doesn't limit the related items per parent when applied to a relation using modifyGraph or modifiers: all matching related items are still assigned to the parents, or with useLimitInFirst, the whole query is limited to one item. Use limit(1) together with maxBatchSize: 1 instead.

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. Also see the modifiers recipe.

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.

TypeScript

Relations are usually declared as optional properties, since they are only set when fetched. When the relation expression is a literal, withGraphFetched, withGraphJoined, fetchGraph and $fetchGraph narrow the result type, so that the fetched relations become required, at every level of the expression:

class Person extends Model {
  firstName!: string;
  parent?: Person | null;
  pets?: Animal[];
  children?: Person[];
}

const people = await Person.query().withGraphFetched('[pets.owner, children.pets]');

people[0].pets[0].owner.firstName; // `pets` and `owner` are not optional.
people[0].children[0].pets.length;
people[0].parent; // Not fetched, still `Person | null | undefined`.

The narrowing works with string and object notation, and with modifiers like pets(onlyDogs). It is kept by first(), findById(), findOne(), throwIfNotFound(), page() and the other query builder methods, and multiple withGraphFetched calls are merged, like objection merges the expressions. Custom query builders are kept too, as long as the model declares its query builder type with this, as in the custom query builder recipe: declare QueryBuilderType: MyQueryBuilder<this>.

Fetching a relation only removes undefined from its type, it never adds or removes null: parent?: Person | null becomes Person | null, while parent?: Person becomes Person. Since objection sets to-one relations to null when there is no related row, declare to-one relations that can be empty as T | null, e.g. parent?: Person | null.

The narrowed types remain assignable to the declared ones, so existing type annotations like Person[] or QueryBuilder<Person> keep working. $query() on a narrowed instance isn't narrowed, as it doesn't fetch the relations again.

Limitations:

Only literal expressions are narrowed. An expression of type string (e.g. a variable or a function argument) leaves the result type as it was.
Nodes the type-level parser doesn't understand are skipped instead of reported: aliased relations (pets as dogs), * and recursion (children.^, where children itself is still narrowed) are not narrowed, and unknown relation names are ignored.
Generic helpers with an explicit query builder return type no longer compile, since the narrowed query builder is a different type than QB. Pass string as type argument to opt out of the narrowing and keep QB:

function withPets<QB extends QueryBuilder<Person>>(query: QB): QB {
  // `query.withGraphFetched('pets')` would be a type error here.
  return query.withGraphFetched<string>('pets');
}

withGraphJoined()

queryBuilder = queryBuilder.withGraphJoined(relationExpression, graphOptions);

Join and fetch a graph of related items for the result of any query (eager loading).

As mentioned, this method uses SQL joins 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. The joinOperation only applies to the relations of the call it's passed to, including their nested relations, so multiple withGraphJoined calls can use different join types:

const people = await Person.query()
  .withGraphJoined('parent', { joinOperation: 'innerJoin' })
  .withGraphJoined('pets.toys', { joinOperation: 'leftJoin' });

Calls without a joinOperation use the default, which is the joinOperation of defaultGraphOptions or leftJoin. If a relation is passed to multiple calls with a joinOperation, the last one is used for it. All other options are shared by all withGraphJoined calls of the query.

Limitations:

limit, page and range 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. The total count of page and range and the result of resultSize are not affected by this, as they count the distinct root models.
All models in the relation expression, including the root model, need a primary key. The flat result rows are grouped into a graph using the idColumn values, so the idColumn must exist in the table and uniquely identify the rows. If the idColumn is null or doesn't exist in the table, the rows can't be told apart, and withGraphJoined throws an error instead of returning a wrong result. Use withGraphFetched for models without a primary key.
The relations are joined as subqueries, and only the selections of their modifiers that objection can name are selected from them: columns, aliased columns like 'name as petName', aggregates with an alias like count('* as toyCount'), count('id', { as: 'toyCount' }) or sum({ totalPrice: 'price' }), ref and raw with as(), subqueries with as(), and raw SQL that ends with an alias like as "petName", as pet_name or as ??. Subqueries and raw SQL like this are selected in addition to the other columns, and don't change which other columns are selected. Other selections, like count() without an alias or raw SQL with an unquoted alias that contains upper case letters, are missing from the result. Give them an alias, for example count('* as petCount') or raw('count(*)').as('petCount').
To merge the rows into models, withGraphJoined adds the idColumn and the relation columns to the selections of the joined subqueries. When a modifier uses groupBy, it needs to group by the idColumn too, for example groupBy('animals.id'), as most databases don't allow selecting columns that aren't grouped. To group by other columns only, use withGraphFetched.

About performance:

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.

In TypeScript, the result type is narrowed to include the fetched relations, see withGraphFetched.

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

Mixing withGraphJoined and withGraphFetched

withGraphJoined and withGraphFetched can be used in the same query. The joined relations are joined to the main query, so you can filter by them, and the fetched relations are then loaded using separate queries for the resulting models. The order of the calls doesn't matter.

const people = await Person.query()
  .withGraphJoined('pets')
  .withGraphFetched('[movies, children]')
  .where('pets.species', 'dog');

// Only people that have a dog are returned and only their dogs are in
// `pets`. `movies` and `children` contain all related items of those people.
console.log(people[0].pets[0].species);
console.log(people[0].movies.length);

A top-level relation can be either joined or fetched, but not both. Passing the same relation to both methods, even when only a nested relation is different (for example withGraphJoined('pets') and withGraphFetched('pets.toys')), throws an error. The * expression can't be used when mixing the methods. modifyGraph works with both joined and fetched relations.

To add relations without caring whether they are joined or fetched, for example in reusable modifiers, use withGraph. It keeps the algorithm of the relations that are already loaded.

withGraph()

queryBuilder = queryBuilder.withGraph(relationExpression, graphOptions);

With graphOptions.algorithm set to 'fetch' or 'join', this is the same as withGraphFetched or withGraphJoined.

Without an algorithm, the relations are added to the graph without choosing an algorithm, which is useful when the graph is extended in different places, for example in modifiers:

Top-level relations that are already joined or fetched keep their algorithm, including their nested relations.
New top-level relations use the algorithm of the most recent withGraphJoined, withGraphFetched or withGraph call with an algorithm, and are fetched if there was none. Child queries inherit it from their parent query.

Unlike passing the same relation to both withGraphJoined and withGraphFetched, this never throws for relations that are already loaded. All other options are passed on, and modifiers, modifyGraph and clearWithGraph work the same way. clearWithGraph also forgets the most recently used algorithm.

Arguments

Argument	Type	Description
relationExpression	RelationExpression	The relation expression describing which relations to load.
options	GraphOptions	Optional options, plus `algorithm`: `'fetch'` or `'join'`. Other values throw.

Return value

Type	Description
QueryBuilder	`this` query builder for chaining.

In TypeScript, the result type is narrowed like with withGraphFetched.

Examples

const people = await Person.query()
  .withGraphJoined('pets')
  .withGraphFetched('movies')
  // `pets.toys` is joined and `movies.actors` fetched, like their parents.
  // `children` is fetched, as `withGraphFetched` was used most recently.
  .withGraph('[pets.toys, movies.actors, children]');

const people = await Person.query().withGraph('pets', { algorithm: 'join' });

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`.

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.

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

Find Methods

Mutating Methods