Khadem Logo
Khadem BETA

Query Builder

The QueryBuilderInterface in Khadem offers a fluent, expressive, and type-safe interface for querying your database. It supports common operations like select, insert, update, delete, pagination, and aggregation, all while supporting model hydration and eager loading.

Selecting Records

Use select and where to fetch records.
dart

// Select specific columns
final users = await User().query
  .select(['id', 'name', 'email'])
  .where('status', '=', 'active')
  .orderBy('name')
  .get();

// Select all columns (default)
final users = await User().query
  .where('status', '=', 'active')
  .get();

// Get first record
final user = await User().query
  .where('email', '=', 'john@example.com')
  .first();

// Get single record or null
final user = await User().query
  .where('id', '=', 1)
  .first();

Filtering with WHERE and OR

Chain where, orWhere, and whereRaw conditions fluently.
dart

final results = await User().query
  .where('role', '=', 'admin')
  .orWhere('email', 'like', '%@example.com')
  .get();

Ordering and Limiting

Sort results with orderBy and limit the number of records.
dart

// Order by single column
final users = await User().query
  .orderBy('name')
  .get();

// Order by multiple columns
final users = await User().query
  .orderBy('created_at', direction: 'desc')
  .orderBy('name')
  .get();

// Limit and offset
final users = await User().query
  .orderBy('created_at', direction: 'desc')
  .limit(10)
  .get();

// Skip records with offset
final users = await User().query
  .orderBy('created_at', direction: 'desc')
  .limit(10)
  .offset(20) // Skip first 20 records
  .get();

Pagination

Paginate your results using paginate. Returns a PaginatedResult object.
dart

final paginated = await User().query
  .where('status', '=', 'active')
  .paginate(perPage: 10, page: 1);

print(paginated.data); // List<User>
print(paginated.total); // Total records

Inserting Records

Insert new records using insert. Returns the inserted ID.
dart

final id = await User().query.insert({
  'name': 'John Doe',
  'email': 'john@example.com'
});

Bulk Operations

Perform bulk insert, update, and delete operations efficiently.
dart

// Bulk insert multiple records
final userIds = [];
for (final userData in userDataList) {
  final id = await User().query.insert(userData);
  userIds.add(id);
}

// Bulk update multiple records
await User().query
  .where('status', '=', 'inactive')
  .update({'status': 'active'});

// Bulk delete with conditions
await User().query
  .where('last_login', '<', DateTime.now().subtract(Duration(days: 365)))
  .delete();

// Conditional bulk operations
await User().query
  .where('subscription_status', '=', 'expired')
  .where('created_at', '<', DateTime.now().subtract(Duration(days: 30)))
  .update({'status': 'suspended'});

Updating Records

Perform updates only when a where condition exists.
dart

await User().query
  .where('id', '=', 5)
  .update({
    'name': 'Updated Name'
  });

// Update multiple fields
await User().query
  .where('email', '=', 'old@example.com')
  .update({
    'email': 'new@example.com',
    'email_verified_at': DateTime.now(),
  });

// Update with complex conditions
await User().query
  .where('status', '=', 'pending')
  .where('created_at', '<', DateTime.now().subtract(Duration(days: 7)))
  .update({
    'status': 'expired',
    'updated_at': DateTime.now(),
  });

Deleting Records

Use delete carefully—it requires a where clause.
dart

await User().query
  .where('id', '=', 10)
  .delete();

// Delete with multiple conditions
await User().query
  .where('status', '=', 'inactive')
  .where('last_login', '<', DateTime.now().subtract(Duration(days: 365)))
  .delete();

// Soft delete (if implemented in your model)
await User().query
  .where('id', '=', 10)
  .update({
    'deleted_at': DateTime.now(),
  });

Eager Loading Relations

Use withRelations to load related data.
dart

// Basic eager loading
final user = await User().query
  .withRelations(['posts', 'profile'])
  .where('id', '=', 1)
  .first();

// Access loaded relations
final posts = user?.relation.get('posts'); // List<Post>
final profile = user?.relation.get('profile'); // Profile

// Multiple relations with conditions
final users = await User().query
  .withRelations(['posts', 'comments'])
  .where('status', '=', 'active')
  .get();

// Nested eager loading (if supported)
final users = await User().query
  .withRelations(['posts.comments'])
  .get();

// Eager loading with query constraints
final users = await User().query
  .withRelations(['posts' => (query) => query.where('published', '=', true).limit(5)])
  .get();

Aggregates (count, sum, avg, min, max)

Easily calculate aggregates like count, sum, avg, min, and max.
dart

final total = await User().query.count();
final avgAge = await User().query.avg('age');
final maxAge = await User().query.max('age');
final minAge = await User().query.min('age');

Exists and Pluck

Check if records exist or extract single column values.
dart

// Check if any records exist
final hasUsers = await User().query
  .where('status', '=', 'active')
  .exists();

// Extract single column values
final emails = await User().query
  .where('status', '=', 'active')
  .pluck('email');

final ids = await User().query.pluck('id');

Streaming Results

Use asStream() for memory-efficient processing of large datasets.
dart

// Process large datasets efficiently
final userStream = User().query
  .where('created_at', '>', DateTime.now().subtract(Duration(days: 30)))
  .asStream();

await for (final user in userStream) {
  // Process each user
  print('Processing user: ${user.name}');
  // Send email, export data, etc.
}

Advanced Query Features

Combine multiple conditions, use raw SQL, and chain complex queries.
dart

// Complex where conditions
final users = await User().query
  .where('age', '>', 18)
  .where((q) => q
    .where('role', '=', 'admin')
    .orWhere('role', '=', 'moderator')
  )
  .whereNotNull('email_verified_at')
  .get();

// Raw SQL where clause
final users = await User().query
  .whereRaw('DATE(created_at) = CURDATE()')
  .get();

// Group by and having
final stats = await User().query
  .select(['role', 'COUNT(*) as count'])
  .groupBy('role')
  .having('count', '>', 5)
  .get();

// Clone queries for reuse
final baseQuery = User().query.where('status', '=', 'active');
final admins = await baseQuery.clone().where('role', '=', 'admin').get();
final moderators = await baseQuery.clone().where('role', '=', 'moderator').get();

Conditional Queries

Use when to conditionally add clauses to your query.
dart

// Basic conditional query
final query = User().query.when(isAdmin, (q) => q.where('role', '=', 'admin'));
final users = await query.get();

// Multiple conditions
final users = await User().query
  .when(searchTerm != null, (q) => q.where('name', 'like', '%$searchTerm%'))
  .when(status != null, (q) => q.where('status', '=', status))
  .when(sortBy != null, (q) => q.orderBy(sortBy!))
  .get();

// Complex conditional logic
final users = await User().query
  .when(includeInactive, (q) => q.where('status', '=', 'active'))
  .when(!includeInactive, (q) => q.whereIn('status', ['active', 'pending']))
  .get();

// Conditional eager loading
final users = await User().query
  .when(loadPosts, (q) => q.withRelations(['posts']))
  .when(loadProfile, (q) => q.withRelations(['profile']))
  .get();

Raw SQL Output

Use toSql() to preview the raw SQL query string.
dart

final sql = User().query
  .where('id', '=', 1)
  .toSql();

print(sql); // Raw SQL query string

On this page