Loading Related Data

// The models used on this page
class Category { id(db) { db.integer().primary().auto(); } name(db)  { db.string(); } tags(db)     { db.hasMany('Tag'); } }
class Tag      { id(db) { db.integer().primary().auto(); } label(db) { db.string(); } category(db) { db.belongsTo('Category').nullable(); } posts(db) { db.manyToMany('Post'); } }
class Author   { id(db) { db.integer().primary().auto(); } name(db)  { db.string(); } posts(db)    { db.hasMany('Post'); } }
class Post     { id(db) { db.integer().primary().auto(); } title(db) { db.string(); } author(db)   { db.belongsTo('Author').nullable(); } tags(db) { db.manyToMany('Tag'); } }

// One joined statement (EF Include) — belongsTo / hasOne / hasMany
const posts = await db.Post
  .where((p) => p.title.like($$), 'Eternia%')
  .include('p => p.author')          // lambda string...
  .include('tags')                   // ...or bare navigation name
  .toList();

posts[0].author.name;                // loaded
posts[0].tags.map((t) => t.label);   // manyToMany navigations load through the split loader automatically

// Nested levels (EF ThenInclude) — chain again for deeper levels
const posts = await db.Post
  .include('p => p.tags')
  .thenInclude('t => t.category')
  .orderBy('p => p.id')
  .toList();

posts[0].tags[0].category.name;

// single()/first() run the nested includes too
const post = await db.Post.include('tags').thenInclude('category').where((p) => p.id == $$, id).single();

// EF AsSplitQuery: every include() loads as a separate batched query — no cartesian explosion, no N+1
const authors = await db.Author
  .asSplitQuery()                    // call it BEFORE include()
  .include('a => a.posts')
  .thenInclude('p => p.tags')
  .thenInclude('t => t.category')
  .toList();
// → 1 query for authors + ONE batched query per level (IN on the parent keys, chunked by 500)

const post = await db.Post.find(id);

// EF Entry(e).Reference(n).Load() / Collection(n).Load()
await db.entry(post).load('author');
await db.entry(post).reference('author').load();
const tags = await db.entry(post).collection('tags').load();
await db.loadNavigation(post, 'tags');               // same thing, context-level

db.entry(post).isLoaded('author');                   // true
db.isNavigationLoaded(post, 'tags');                 // true

// Lazy loading is ON by default. An unloaded navigation returns a Promise that
// loads it ONCE (parameterized, any engine) and caches it:
const post = await db.Post.find(id);
const author = await post.author;     // first read: one SELECT
post.author.name;                     // afterwards the read is synchronous

// null means "loaded, nothing there"; undefined means "not loaded yet"
const orphan = await db.Post.asSplitQuery().include('author').first();
orphan.author;                        // null — a missing parent is NOT lazy-loaded again

// Opt out per navigation: unloaded reads are null until loaded explicitly
class Lazy { id(db) { db.integer().primary().auto(); } author(db) { db.belongsTo('Author').lazyLoadingOff(); } }

const post = await db.Post.find(id);
const bob  = await db.Author.find(bobId);

post.author = bob;           // sets post.author_id, marks the entity dirty (EF fix-up)
await db.saveChanges();      // UPDATE Post SET author_id = ? — the engines persist the KEY, never the object

post.author_id = aliceId;    // changing the FK invalidates the loaded parent...
(await post.author).name;    // ...so the next read re-resolves instead of returning Bob

// legacy idiom still works and keeps the FK in sync
post.author = aliceId;

// Included navigations honor the target entity's global query filters (soft delete, tenant…)
this.dbset(Tag).queryFilter('softDelete', 't => t.deletedAt == null');

const posts = await db.Post.include('tags').toList();            // deleted tags are NOT in post.tags
const all   = await db.Post.ignoreQueryFilters().include('tags').toList();   // propagates to every level
const some  = await db.Post.ignoreQueryFilters(['softDelete']).include('tags').thenInclude('category').toList();

// a filtered-out belongsTo parent reads null

// Insert: link persisted targets by key, insert new targets first (EF cascade insert)
const post = db.Post.new();
post.title = 'Grayskull';
post.tags = [existingTag, otherTagId, { label: 'new' }];
await db.saveChanges();                 // one PostTag join row per element

// Loaded collections have EF's add()/remove() — nothing hits the DB until saveChanges()
const tags = await post.tags;
tags.add(anotherTag);
tags.add({ label: 'brand-new' });       // inserted first, then linked
await tags.remove(existingTag);         // schedules the join row's DELETE
await db.saveChanges();

// the same through entry()
db.entry(post).collection('tags').add(tag);
await db.entry(tag).collection('posts').load();   // reverse side

// SELECT only what you need — projections never track
const skus   = await db.Product.select('p => p.sku').orderBy('p => p.id').toList();
const cats   = await db.Item.select('i => i.cat').distinct().toList();
const emails = await db.User.where((u) => u.active == $$, true).pluck('email');   // SELECT email …
const dtos   = await db.User.toObjectList({ includeRelationships: false });          // plain objects, not tracked

// Read-only entity graphs: asNoTracking() + include()
const feed = await db.Post.asNoTracking().include('author').take(50).toList();