Khadem Logo
Khadem BETA

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)

Default Relations - Auto-Loading

Define relationships that should always be loaded automatically for a model. This eliminates the need to manually specify withRelations() every time.

✨ Automatic Loading

Relations listed in defaultRelations are automatically loaded on get(), first(), findById(), and paginate().

dart
import 'package:khadem/khadem.dart';

// Define default relations in your model
class User extends KhademModel<User> with HasRelationships {
  @override
  List<dynamic> get defaultRelations => [
    'profile',    // Always load user's profile
    'roles',      // Always load user's roles
  ];

  @override
  Map<String, RelationDefinition> get relations => {
    'profile': hasOne<Profile>(
      foreignKey: 'user_id',
      relatedTable: 'profiles',
      factory: () => Profile(),
    ),
    'roles': belongsToMany<Role>(
      pivotTable: 'user_roles',
      foreignPivotKey: 'user_id',
      relatedPivotKey: 'role_id',
      relatedTable: 'roles',
      localKey: 'id',
      factory: () => Role(),
    ),
    'posts': hasMany<Post>(
      foreignKey: 'user_id',
      relatedTable: 'posts',
      factory: () => Post(),
    ),
  };
}

// Now these queries automatically load profile and roles:
final user = await User().query.where('id', '=', 1).first();
// profile and roles are already loaded!

final users = await User().query.where('status', '=', 'active').get();
// All users have profile and roles loaded

final paginatedUsers = await User().query.paginate(perPage: 20);
// Paginated users also have default relations

// You can still add more relations on top of defaults
final user = await User().query
    .withRelations(['posts'])  // Load posts in addition to defaults
    .where('id', '=', 1)
    .first();
// Now has profile, roles, AND posts

without() - Excluding Default Relations

Sometimes you want to skip loading specific default relations for performance reasons. Use without() to exclude them from a specific query.

🎯 Selective Exclusion

Use without() when you don't need all default relations for a particular query.

dart
import 'package:khadem/khadem.dart';

class User extends KhademModel<User> with HasRelationships {
  @override
  List<dynamic> get defaultRelations => ['profile', 'roles', 'settings'];
}

// Scenario 1: Exclude specific default relations
final users = await User().query
    .without(['settings'])  // Don't load settings
    .get();
// Only profile and roles are loaded

// Scenario 2: Exclude multiple default relations
final users = await User().query
    .without(['profile', 'settings'])  // Skip profile and settings
    .get();
// Only roles are loaded

// Scenario 3: Exclude all defaults for a lightweight query
final users = await User().query
    .without(['profile', 'roles', 'settings'])  // Skip all
    .select(['id', 'name', 'email'])
    .get();
// No relations loaded - very fast!

// Scenario 4: Mix without() and withRelations()
final users = await User().query
    .without(['settings'])           // Don't load settings
    .withRelations(['posts'])        // But do load posts
    .get();
// Loads: profile, roles, posts (but not settings)

// Performance optimization example
Future<List<User>> getUsersForExport() async {
  // For CSV export, we don't need relations at all
  return await User().query
      .without(['profile', 'roles', 'settings'])
      .select(['id', 'name', 'email', 'created_at'])
      .get();
}

withOnly() - Override Default Relations

Completely override the default relations and load only the specific relations you need. This ignores defaultRelations and loads only what you specify.

🔄 Complete Override

withOnly() completely replaces default relations. Use when you need precise control.

dart
import 'package:khadem/khadem.dart';

class User extends KhademModel<User> with HasRelationships {
  @override
  List<dynamic> get defaultRelations => ['profile', 'roles', 'settings'];
}

// withOnly() completely replaces default relations

// Scenario 1: Load ONLY posts (ignores all defaults)
final users = await User().query
    .withOnly(['posts'])
    .get();
// Only posts are loaded. No profile, roles, or settings!

// Scenario 2: Load ONLY what's needed for a specific view
final users = await User().query
    .withOnly(['profile'])  // Just profile, nothing else
    .where('status', '=', 'active')
    .get();

// Scenario 3: Load NO relations at all
final users = await User().query
    .withOnly([])  // Empty array = no relations
    .get();
// Fastest query possible - no eager loading

// Scenario 4: Complex nested loading, ignoring defaults
final users = await User().query
    .withOnly([
      'posts.comments.user',  // Deep nesting
      'profile',              // Just profile from defaults
    ])
    .get();

// Comparison:
// withRelations() ADDS to defaults
final u1 = await User().query.withRelations(['posts']).first();
// Loads: profile, roles, settings, posts ← Added to defaults

// withOnly() REPLACES defaults
final u2 = await User().query.withOnly(['posts']).first();
// Loads: posts ← Only this, ignores defaults

// without() REMOVES from defaults
final u3 = await User().query.without(['settings']).first();
// Loads: profile, roles ← Defaults minus excluded

Relation Counts - withCounts

Instead of loading full relationships, sometimes you just need to know how many related records exist. Use withCounts in your model to automatically include counts.

📊 Performance Benefit

Counting is much faster than loading full relations. Perfect for displaying statistics.

dart
import 'package:khadem/khadem.dart';

// Define relation counts in your model
class User extends KhademModel<User> with HasRelationships {
  @override
  List<String> get withCounts => [
    'posts',     // Include posts_count
    'comments',  // Include comments_count
    'followers', // Include followers_count
  ];

  @override
  Map<String, RelationDefinition> get relations => {
    'posts': hasMany<Post>(
      foreignKey: 'user_id',
      relatedTable: 'posts',
      factory: () => Post(),
    ),
    'comments': hasMany<Comment>(
      foreignKey: 'user_id',
      relatedTable: 'comments',
      factory: () => Comment(),
    ),
    'followers': belongsToMany<User>(
      pivotTable: 'followers',
      foreignPivotKey: 'user_id',
      relatedPivotKey: 'follower_id',
      relatedTable: 'users',
      localKey: 'id',
      factory: () => User(),
    ),
  };

  // Access counts as properties
  int get postsCount => getAppended('posts_count') as int? ?? 0;
  int get commentsCount => getAppended('comments_count') as int? ?? 0;
  int get followersCount => getAppended('followers_count') as int? ?? 0;
}

// Counts are automatically included when querying
final user = await User().query.where('id', '=', 1).first();
print('Posts: ${user?.postsCount}');        // e.g., 42
print('Comments: ${user?.commentsCount}');  // e.g., 156
print('Followers: ${user?.followersCount}'); // e.g., 1024

// Counts in JSON output
final json = user?.toJson();
// {
//   "id": 1,
//   "name": "John Doe",
//   "posts_count": 42,
//   "comments_count": 156,
//   "followers_count": 1024
// }

// Manual count loading
final user = await User().query.where('id', '=', 1).first();
await user?.load('posts');
final count = (user?.getRelation('posts') as List?)?.length ?? 0;
user?.setAppended('posts_count', count);

Combining All Techniques

Here's a comprehensive example showing how to use all eager loading features together in a real application.

dart
import 'package:khadem/khadem.dart';

// Advanced User model with all features
class User extends KhademModel<User> with HasRelationships {
  @override
  List<dynamic> get defaultRelations => [
    'profile',
    'roles',
  ];

  @override
  List<String> get withCounts => [
    'posts',
    'followers',
    'following',
  ];

  @override
  Map<String, RelationDefinition> get relations => {
    'profile': hasOne<Profile>(
      foreignKey: 'user_id',
      relatedTable: 'profiles',
      factory: () => Profile(),
    ),
    'roles': belongsToMany<Role>(
      pivotTable: 'user_roles',
      foreignPivotKey: 'user_id',
      relatedPivotKey: 'role_id',
      relatedTable: 'roles',
      localKey: 'id',
      factory: () => Role(),
    ),
    'posts': hasMany<Post>(
      foreignKey: 'user_id',
      relatedTable: 'posts',
      factory: () => Post(),
    ),
    'followers': belongsToMany<User>(
      pivotTable: 'followers',
      foreignPivotKey: 'user_id',
      relatedPivotKey: 'follower_id',
      relatedTable: 'users',
      localKey: 'id',
      factory: () => User(),
    ),
    'following': belongsToMany<User>(
      pivotTable: 'followers',
      foreignPivotKey: 'follower_id',
      relatedPivotKey: 'user_id',
      relatedTable: 'users',
      localKey: 'id',
      factory: () => User(),
    ),
  };
}

// Example 1: User profile page (load everything)
Future<User?> getUserProfile(int userId) async {
  final user = await User().query
      .withRelations([
        'posts.comments',     // Add posts with comments
        'followers.profile',  // Add followers with profiles
      ])
      .where('id', '=', userId)
      .first();
  
  // Has: profile, roles (defaults) + posts, followers (explicit)
  // Plus: posts_count, followers_count, following_count
  return user;
}

// Example 2: User list page (lightweight)
Future<PaginatedResult<User>> getUsersList() async {
  final users = await User().query
      .withOnly(['profile'])  // Only profile, ignore other defaults
      .where('status', '=', 'active')
      .paginate(perPage: 20);
  
  // Has: profile only
  // Plus: posts_count, followers_count, following_count (automatic)
  return users;
}

// Example 3: Export CSV (no relations)
Future<List<User>> exportUsers() async {
  final users = await User().query
      .withOnly([])  // No relations at all
      .select(['id', 'name', 'email', 'created_at'])
      .get();
  
  // No relations, no counts - fastest possible
  return users;
}

// Example 4: Admin dashboard (custom mix)
Future<List<User>> getAdminDashboard() async {
  final users = await User().query
      .without(['profile'])              // Don't need profile
      .withRelations([
        'posts.category',                // Add posts with categories
        'roles.permissions',             // Add role permissions
      ])
      .where('is_admin', '=', true)
      .get();
  
  // Has: roles (default) + posts, role permissions (explicit)
  // Doesn't have: profile (excluded)
  // Plus: all counts
  return users;
}

On this page