Eager Loading

Eager loading is a database optimization technique that allows you to preload related data in a single query, preventing the infamous N+1 query problem and dramatically improving your application's performance.

The N+1 Query Problem

Before understanding eager loading, let's see the problem it solves. Consider this scenario: you want to display a list of blog posts with their authors' names.

❌ The Wrong Way (N+1 Problem)

This approach executes 1 query to get posts + N queries to get each post's author = N+1 total queries!

dart


// ❌ This creates N+1 queries (1 + 100 = 101 total queries!)
final posts = await Post().query.get(); // 1 query to get posts

for (final post in posts) {
  final user = await post.user(); // 1 query PER post (100 queries!)
  print('Post by: ${user?.name}');
}

// If you have 100 posts, this executes 101 database queries!
// Query 1: SELECT * FROM posts
// Query 2: SELECT * FROM users WHERE id = 1
// Query 3: SELECT * FROM users WHERE id = 2
// ... 100 more queries!

The Solution: Eager Loading

Eager loading solves this by fetching all related data upfront with just 2 queries total, regardless of how many records you're working with.

✅ The Right Way (Eager Loading)

Only 2 queries: 1 for posts + 1 for all authors. Much faster!

dart


// ✅ This creates only 2 queries total!
final posts = await Post()
  .query
  .withRelations(['user'])  // Preload user relationship
  .get();

for (final post in posts) {
  final user = post.relation.get('user'); // No database query!
  print('Post by: ${user?.name}');
}

// Only 2 database queries total:
// Query 1: SELECT * FROM posts
// Query 2: SELECT * FROM users WHERE id IN (1,2,3,4,5...)

Basic Usage

Use the withRelations() method to specify which relationships to load. Pass an array of relationship names as strings.

💡 Important

Relationships must be defined in your model using hasMany(), belongsTo(), etc.

dart


// Load posts with their authors
final posts = await Post()
  .query
  .withRelations(['user'])
  .get();

// Access the loaded user data
for (final post in posts) {
  final author = post.relation.get('user');
  print('Title: ${post.title}');
  print('Author: ${author?.name}');
}

// Load a single post with its category
final post = await Post()
  .query
  .withRelations(['category'])
  .where('id', 1)
  .first();

final category = post?.relation.get('category');
print('Category: ${category?.name}');

Loading Multiple Relations

You can load multiple relationships at once by passing multiple strings to the array. Each relationship will be loaded efficiently.

dart


// Load multiple relationships at once
final posts = await Post()
  .query
  .withRelations([
    'user',      // Post author
    'category',  // Post category  
    'tags'       // Post tags
  ])
  .get();

// Access all loaded relations
for (final post in posts) {
  final author = post.relation.get('user');
  final category = post.relation.get('category');
  final tags = post.relation.get('tags');
  
  print('Post: ${post.title}');
  print('Author: ${author?.name}');
  print('Category: ${category?.name}');
  print('Tags: ${tags?.length ?? 0} tags');
}

Nested Relations

Load relationships of relationships using dot notation. For example, load posts with their comments, and each comment's author in a single query chain.

🔍 How it works

'comments.user' means: load comments, then for each comment, load its user.

dart


// Load posts with comments, and each comment's author
final posts = await Post()
  .query
  .withRelations([
    'user',           // Post author
    'comments.user'   // Comments + each comment's author
  ])
  .get();

// Access nested data
for (final post in posts) {
  final author = post.relation.get('user');
  final comments = post.relation.get('comments');
  
  print('Post: ${post.title} by ${author?.name}');
  print('Comments: ${comments?.length ?? 0}');
  
  if (comments != null) {
    for (final comment in comments) {
      final commentAuthor = comment.relation.get('user');
      print('  - ${comment.content} by ${commentAuthor?.name}');
    }
  }
}

// Even deeper nesting is possible
final posts = await Post()
  .query
  .withRelations([
    'comments.user.profile',  // Comment author's profile
    'category.posts.user'     // Category's other posts and their authors
  ])
  .get();

Accessing Loaded Relations

Once relations are loaded, access them using the .relation.get() method. The data is already in memory, so no additional database queries are made.

dart


final post = await Post()
  .query
  .withRelations(['user', 'comments.user'])
  .first();

// Single relation (belongsTo, hasOne)
final author = post?.relation.get('user');
print('Author: ${author?.name}');

// Multiple relations (hasMany)
final comments = post?.relation.get('comments') as List?;
print('Comment count: ${comments?.length ?? 0}');

// Check if relation was loaded
if (post?.relation.isLoaded('user') == true) {
  print('User relation is loaded');
}

// Access nested relations
if (comments != null) {
  for (final comment in comments) {
    final commentUser = comment.relation.get('user');
    print('Comment by: ${commentUser?.name}');
  }
}

Paginated Relations

For relationships with many records (like comments on a popular post), you can paginate the related data to control memory usage and improve performance.

🔧 Pagination Syntax

Use :paginated:page=X:perPage=Y after the relation name to paginate.

dart


// Paginate comments (load 10 comments per post)
final posts = await Post()
  .query
  .withRelations([
    'user',
    'comments:paginated:page=1:perPage=10'
  ])
  .get();

// Access paginated data
for (final post in posts) {
  final paginatedComments = post.relation.get('comments');
  
  if (paginatedComments != null) {
    final comments = paginatedComments['data'] as List;
    final meta = paginatedComments['meta'] as Map;
    
    print('Comments (page ${meta['current_page']}): ${comments.length}');
    print('Total comments: ${meta['total']}');
  }
}

// Load specific page of comments with their authors
final post = await Post()
  .query
  .withRelations([
    'comments:paginated:page=2:perPage=5.user'
  ])
  .first();

Advanced Configuration (Object Syntax)

For complex scenarios, use object syntax instead of strings. This gives you fine-grained control over pagination and nested relationships.

⚙️ Available Options

paginate: Enable/disable pagination (boolean)
page: Which page to load (integer)
perPage: Records per page (integer)
with: Nested relations to load (array)

dart


// Advanced configuration using object syntax
final posts = await Post()
  .query
  .withRelations([
    'user',  // Simple string syntax
    {
      'comments': {
        'paginate': true,
        'page': 1,
        'perPage': 5,
        'with': ['user', 'likes']  // Nested relations
      }
    },
    {
      'tags': {
        'paginate': false,
        'with': ['category']
      }
    }
  ])
  .get();

// Mix string and object syntax
final post = await Post()
  .query
  .withRelations([
    'user',  // String syntax
    {        // Object syntax
      'comments': {
        'paginate': true,
        'page': 1,
        'perPage': 10,
        'with': ['user.profile']
      }
    }
  ])
  .first();

Real-World Example

Here's a complete example showing how to build a blog post page with eager loading. Notice how we load everything we need in a single query chain.

dart


// Complete blog post page with eager loading
class BlogService {
  
  // Get post with all related data for display
  static Future<Map<String, dynamic>> getPostForPage(int postId) async {
    final post = await Post()
      .query
      .withRelations([
        'user.profile',                    // Author with profile
        'category',                        // Post category
        'tags',                           // Post tags
        'comments:paginated:page=1:perPage=10.user.profile',  // Recent comments
        'relatedPosts:paginated:page=1:perPage=5.user',       // Related posts
      ])
      .where('id', postId)
      .where('published', true)
      .first();

    if (post == null) {
      throw Exception('Post not found');
    }

    // All data is loaded, no more queries needed!
    final author = post.relation.get('user');
    final authorProfile = author?.relation.get('profile');
    final category = post.relation.get('category');
    final tags = post.relation.get('tags') as List?;
    final paginatedComments = post.relation.get('comments');
    final relatedPosts = post.relation.get('relatedPosts');

    return {
      'post': post,
      'author': {
        'name': author?.name,
        'email': author?.email,
        'avatar': authorProfile?.avatar,
        'bio': authorProfile?.bio,
      },
      'category': category?.name,
      'tags': tags?.map((tag) => tag.name).toList() ?? [],
      'comments': {
        'data': paginatedComments?['data'] ?? [],
        'total': paginatedComments?['meta']?['total'] ?? 0,
        'hasMore': (paginatedComments?['meta']?['current_page'] ?? 0) < 
                   (paginatedComments?['meta']?['last_page'] ?? 0),
      },
      'relatedPosts': relatedPosts?['data'] ?? [],
    };
  }
}

// Usage in your controller/widget
final blogData = await BlogService.getPostForPage(123);
print('Post: ${blogData['post'].title}');
print('Author: ${blogData['author']['name']}');
print('Comments: ${blogData['comments']['total']}');

Performance Tips & Best Practices

🚀 Performance Guidelines

✓Always use eager loading when you know you'll need related data
✓Paginate large relations (comments, notifications, etc.)
✓Load only what you need - don't over-eager load
✗Don't load relations you won't use in the current request
✗Avoid deeply nested relations (more than 3 levels) without pagination

⚠️ Common Pitfalls

Typos in relation names: Double-check your relationship method names
Missing relationship definitions: Ensure relations are defined in your model
Over-loading: Loading too many relations can slow down queries
Memory issues: Large unpaginated relations can consume lots of memory

Troubleshooting

Having issues with eager loading? Here are common problems and solutions:

dart


// ❌ Common mistakes and their solutions

// Problem: Relation returns null
final post = await Post().query.withRelations(['user']).first();
final user = post?.relation.get('user'); // Returns null

// ✅ Solutions:
// 1. Check if the relationship is defined in your Post model
class Post extends Model {
  // Make sure you have this method
  Future<User?> user() async {
    return await belongsTo(User(), 'user_id');
  }
}

// 2. Check foreign key exists and has correct data
final post = await Post().query.select(['*', 'user_id']).first();
print('User ID: ${post?.user_id}'); // Should not be null

// 3. Check if relation was actually loaded
if (post?.relation.isLoaded('user') == true) {
  print('User relation loaded successfully');
} else {
  print('User relation was not loaded');
}

// Problem: Typo in relation name
final posts = await Post()
  .query
  .withRelations(['usr'])  // ❌ Typo: should be 'user'
  .get();

// ✅ Solution: Use exact relation method name
final posts = await Post()
  .query
  .withRelations(['user'])  // ✅ Correct
  .get();

// Problem: Performance issues with large relations
final posts = await Post()
  .query
  .withRelations(['comments'])  // ❌ Could load thousands of comments
  .get();

// ✅ Solution: Use pagination
final posts = await Post()
  .query
  .withRelations([
    'comments:paginated:page=1:perPage=10'
  ])
  .get();

// Debug: Enable query logging to see what queries are executed
// (Check your ORM documentation for how to enable query logging)

The N+1 Query Problem

❌ The Wrong Way (N+1 Problem)

The Solution: Eager Loading

✅ The Right Way (Eager Loading)

Basic Usage

💡 Important

Loading Multiple Relations

Nested Relations

🔍 How it works

Accessing Loaded Relations

Paginated Relations

🔧 Pagination Syntax

Advanced Configuration (Object Syntax)

⚙️ Available Options

Real-World Example

Performance Tips & Best Practices

🚀 Performance Guidelines

⚠️ Common Pitfalls

Troubleshooting

On this page