Query Builder

The Vania Query Builder provides a fluent, intuitive interface for building and executing database queries. It offers a comprehensive set of methods for constructing complex SQL queries while maintaining readability and preventing SQL injection attacks.

Getting Started

Basic Connection

Access the Query Builder through the DB class:

import 'package:vania/vania.dart';

// Get a query builder instance
final query = DB.table('users');

// With custom connection
final query = DB.connection('mysql').table('users');

Method Chaining

The Query Builder uses fluent method chaining, allowing you to build complex queries step by step:

final users = await DB.table('users')
    .where('status', '=', 'active')
    .where('age', '>=', 18)
    .orderBy('name', 'asc')
    .get();

Basic Usage

Specifying Tables

// Single table
final query = DB.table('users');

// Table with alias
final query = DB.table('users', 'u');

Raw SQL

When you need to use raw SQL expressions:

final users = await DB.table('users')
    .selectRaw('count(*) as total')
    .whereRaw('age > ?', [18])
    .get();

Retrieving Results

Get All Records

// Get all records
final users = await DB.table('users').get();

// Get specific columns
final users = await DB.table('users').get(['name', 'email']);

Get Single Record

// Get first record
final user = await DB.table('users').first();

// Get first record with specific columns
final user = await DB.table('users').first(['name', 'email']);

// Get first record or throw exception
final user = await DB.table('users').firstOrFail();

Find by ID

// Find by primary key
final user = await DB.table('users').find(1);

// Find by ID with specific columns
final user = await DB.table('users').find(1, columns: ['name', 'email']);

// Find by specific column
final user = await DB.table('users').find('vania@vdart.dev', byColumnName: 'email');

// Find by ID or throw exception
final user = await DB.table('users').findOrFail(1);

Check Existence

// Check if records exist
final hasUsers = await DB.table('users').where('id','=',1).exists(); // Return true if id 1 exists

// Check if no records exist
final noUsers = await DB.table('users').where('id','=',200).doesntExist(); // Return true if id 200 doesn't exist

Streaming Results

// Process large datasets with cursor
await for (final user in DB.table('users').cursor()) {
  print('Processing user: ${user['name']}');
}

// Lazy loading with chunks
await for (final chunk in DB.table('users').lazy(1000)) {
  for (final user in chunk) {
    print('Processing user: ${user['name']}');
  }
}

Chunked Processing

// Process records in chunks
await DB.table('users').chunk(100, (users) {
  for (final user in users) {
    print('Processing: ${user['name']}');
  }
});

// Chunk by ID for better performance
await DB.table('users').chunkById(100, (users) {
  for (final user in users) {
    print('Processing: ${user['name']}');
  }
}, 'id');

Select Statements

Selecting Columns

// Select all columns
final users = await DB.table('users').select(['*']).get();

// Select specific columns
final users = await DB.table('users').select(['name', 'email']).get();

// Add more columns to existing selection
final users = await DB.table('users')
    .select(['name'])
    .addSelect(['email', 'phone'])
    .get();

Raw Select Statements

final users = await DB.table('users')
    .selectRaw('count(*) as total, status')
    .groupBy(['status'])
    .get();

Subquery Selections

final users = await DB.table('users')
    .selectSub(
        DB.table('posts').selectRaw('COUNT(*)').where('user_id', '=', DB.raw('users.id')),
        'posts_count'
    )
    .addSelect(['first_name','id'])
    .get();

Where Clauses

Basic Where Clauses

Important: All where clauses require explicit operators:

// Basic where with operator (REQUIRED)
final users = await DB.table('users')
    .where('name', '=', 'John')
    .get();

// Multiple where conditions
final users = await DB.table('users')
    .where('status', '=', 'active')
    .where('age', '>=', 18)
    .get();

// Or where conditions
final users = await DB.table('users')
    .where('name', '=', 'John')
    .orWhere('name', '=', 'Jane')
    .get();

Comparison Operators

// Greater than
final users = await DB.table('users').where('age', '>', 18).get();

// Greater than or equal
final users = await DB.table('users').where('age', '>=', 18).get();

// Less than
final users = await DB.table('users').where('age', '<', 65).get();

// Less than or equal
final users = await DB.table('users').where('age', '<=', 65).get();

// Not equal
final users = await DB.table('users').where('status', '!=', 'inactive').get();

// Like operator
final users = await DB.table('users').where('name', 'like', '%john%').get();

Convenience Where Methods

// Equal to
final users = await DB.table('users').whereEqualTo('status', 'active').get();

// Not equal to
final users = await DB.table('users').whereNotEqualTo('status', 'inactive').get();

// Greater than
final users = await DB.table('users').whereGreaterThan('age', 18).get();

// Greater than or equal to
final users = await DB.table('users').whereGreaterThanOrEqualTo('age', 18).get();

// Less than
final users = await DB.table('users').whereLessThan('age', 65).get();

// Less than or equal to
final users = await DB.table('users').whereLessThanOrEqualTo('age', 65).get();

Where Between

// Between values
final users = await DB.table('users')
    .whereBetween('age', [18, 65])
    .get();

// Not between
final users = await DB.table('users')
    .whereNotBetween('age', [18, 65])
    .get();

// Between columns
final users = await DB.table('users')
    .whereBetweenColumns('age', ['min_age', 'max_age'])
    .get();

Where In

// In array
final users = await DB.table('users')
    .whereIn('id', [1, 2, 3, 4, 5])
    .get();

// Not in array
final users = await DB.table('users')
    .whereNotIn('status', ['inactive', 'banned'])
    .get();

Where Null

// Is null
final users = await DB.table('users').whereNull('email_verified_at').get();

// Is not null
final users = await DB.table('users').whereNotNull('email_verified_at').get();

Where Like

// Like (case-insensitive by default)
final users = await DB.table('users')
    .whereLike('name', '%john%')
    .get();

// Case-sensitive like
final users = await DB.table('users')
    .whereLike('name', '%John%', caseSensitive: true)
    .get();

// Not like
final users = await DB.table('users')
    .whereNotLike('name', '%admin%')
    .get();

Date Where Clauses

// Where date
final users = await DB.table('users')
    .whereDate('created_at', '=', '2024-01-01')
    .get();

// Where year
final users = await DB.table('users')
    .whereYear('created_at', '=', 2024)
    .get();

// Where month
final users = await DB.table('users')
    .whereMonth('created_at', '=', 12)
    .get();

// Where day
final users = await DB.table('users')
    .whereDay('created_at', '=', 25)
    .get();

// Where time
final users = await DB.table('users')
    .whereTime('created_at', '>=', '09:00:00')
    .get();

Special Date Methods

// Today
final users = await DB.table('users').whereToday('created_at').get();

// Before today
final users = await DB.table('users').whereBeforeToday('created_at').get();

// After today
final users = await DB.table('users').whereAfterToday('created_at').get();

// Past dates
final users = await DB.table('users').wherePast('created_at').get();

// Future dates
final users = await DB.table('users').whereFuture('created_at').get();

Where Column Comparisons

// Compare two columns
final users = await DB.table('users')
    .whereColumn('first_name', '=', 'last_name')
    .get();

// With different operator
final users = await DB.table('users')
    .whereColumn('created_at', '>', 'updated_at')
    .get();

Where Exists

// Where exists subquery
final users = await DB.table('users')
    .whereExists((query) {
      return query.table('orders')
          .where('user_id', '=', DB.raw('users.id'));
    })
    .get();

// Where not exists
final users = await DB.table('users')
    .whereNotExists((query) {
      return query.table('orders')
          .where('user_id', '=', DB.raw('users.id'));
    })
    .get();

JSON Where Clauses

// JSON contains
final users = await DB.table('users')
    .whereJsonContains('options', {'theme': 'dark'})
    .get();

// JSON doesn't contain
final users = await DB.table('users')
    .whereJsonDoesntContain('options', {'admin': true})
    .get();

// JSON length
final users = await DB.table('users')
    .whereJsonLength('tags', '>', 3)
    .get();

Full Text Search

// Full text search
final posts = await DB.table('posts')
    .whereFullText(['title', 'content'], 'search query')
    .get(); // SELECT * FROM posts WHERE MATCH(title, content) AGAINST('search query')

// With options
final posts = await DB.table('posts')
    .whereFullText(['title', 'content'], 'search query', {
      'mode': 'boolean',
    })
    .get(); // SELECT * FROM posts WHERE MATCH(title, content) AGAINST('search query' IN boolean MODE)

Advanced Where Methods

// Where all values match
final users = await DB.table('users')
    .whereAll('tags', ['php', 'laravel', 'vue'])
    .get();  //SELECT * FROM users WHERE tags = 'php' AND tags = 'dart'

// Where any value matches
final users = await DB.table('users')
    .whereAny('tags', ['php', 'python', 'javascript'])
    .get();  // SELECT * FROM users WHERE tags IN ('php', 'dart')

// Where none match
final users = await DB.table('users')
    .whereNone('tags', ['deprecated', 'legacy'])
    .get(); // SELECT * FROM users WHERE tags NOT IN ('php', 'dart')

// Where row values
final users = await DB.table('users')
    .whereRowValues(['first_name', 'tags'], '=', ['User 1', 'dart'])
    .get(); // SELECT * FROM users WHERE (first_name, tags) = ('User 1', 'dart')

Relationship-Based Where Clauses

// Where has related records
final users = await DB.table('users')
    .whereHas('orders', (query) {
      return query.where('status', '=', 'completed')
          .where('total', '>', 100);
    })
    .get();

// Where has related records (with OR condition)
final users = await DB.table('users')
    .where('status', '=', 'active')
    .orWhereHas('orders', (query) {
      return query.where('created_at', '>', '2024-01-01');
    })
    .get();

// Where doesn't have related records
final users = await DB.table('users')
    .whereDoesntHave('orders', (query) {
      return query.where('status', '=', 'cancelled');
    })
    .get();

// Where doesn't have related records (with OR condition)
final users = await DB.table('users')
    .where('created_at', '>', '2024-01-01')
    .orWhereDoesntHave('subscriptions', (query) {
      return query.where('status', '=', 'expired');
    })
    .get();

// Complex relationship queries
final activeUsersWithRecentOrders = await DB.table('users')
    .where('status', '=', 'active')
    .whereHas('orders', (query) {
      return query.where('created_at', '>=', DateTime.now().subtract(Duration(days: 30)))
          .where('status', '!=', 'cancelled');
    })
    .whereDoesntHave('complaints', (query) {
      return query.where('status', '=', 'open');
    })
    .get();

// Users with posts but no comments
final usersWithPostsNoComments = await DB.table('users')
    .whereHas('posts', (query) {
      return query.where('published', '=', true);
    })
    .whereDoesntHave('comments', (query) {
      return query.where('created_at', '>', '2024-01-01');
    })
    .get();

Joins

Inner Joins

// Basic inner join
final users = await DB.table('users')
    .join('profiles', 'users.id', '=', 'profiles.user_id')
    .get();

// Multiple joins
final users = await DB.table('users')
    .join('profiles', 'users.id', '=', 'profiles.user_id')
    .join('roles', 'users.role_id', '=', 'roles.id')
    .get();

Left Joins

// Left join
final users = await DB.table('users')
    .leftJoin('profiles', 'users.id', '=', 'profiles.user_id')
    .get();

Right Joins

// Right join
final users = await DB.table('users')
    .rightJoin('profiles', 'users.id', '=', 'profiles.user_id')
    .get();

Cross Joins

// Cross join
final result = await DB.table('sizes')
    .crossJoin('colors')
    .get();

Subquery Joins

// Join with subquery
final latestPosts = DB.table('posts')
    .select(['user_id', DB.raw('MAX(created_at) as latest')])
    .groupBy(['user_id']);

final users = await DB.table('users')
    .joinSub(latestPosts, 'latest_posts', 'users.id', '=', 'latest_posts.user_id')
    .get();

// Left join with subquery
final users = await DB.table('users')
    .leftJoinSub(latestPosts, 'latest_posts', 'users.id', '=', 'latest_posts.user_id')
    .get();

Unions

Basic Unions

// Union queries
final first = DB.table('users').where('status', '=', 'active');
final second = DB.table('users').where('status', '=', 'pending');

final users = await first.union(second).get();

Union All

// Union all (includes duplicates)
final first = DB.table('users').select(['name']);
final second = DB.table('admins').select(['name']);

final names = await first.unionAll(second).get();

Ordering, Grouping & Limiting

Ordering Results

// Order by ascending
final users = await DB.table('users').orderBy('name', 'asc').get();

// Order by descending
final users = await DB.table('users').orderBy('created_at', 'desc').get();

// Convenience methods
final users = await DB.table('users').orderByAsc('name').get();
final users = await DB.table('users').orderByDesc('created_at').get();

// Multiple order by
final users = await DB.table('users')
    .orderBy('status', 'asc')
    .orderBy('name', 'asc')
    .get();

// Random order
final users = await DB.table('users').inRandomOrder().get();

// Latest records
final users = await DB.table('users').latest('created_at').get();

Grouping Results

// Group by single column
final stats = await DB.table('orders')
    .select(['status', DB.raw('COUNT(*) as count')])
    .groupBy(['status'])
    .get();

// Group by multiple columns
final stats = await DB.table('orders')
    .select(['status', 'country', DB.raw('COUNT(*) as count')])
    .groupBy(['status', 'country'])
    .get();

Having Clauses

// Having clause
final stats = await DB.table('orders')
    .select(['status', DB.raw('COUNT(*) as count')])
    .groupBy(['status'])
    .having('count', '>', 5)
    .get();

// Having between
final stats = await DB.table('orders')
    .select(['user_id', DB.raw('SUM(amount) as total')])
    .groupBy(['user_id'])
    .havingBetween('total', [100, 1000])
    .get();

Limiting Results

// Limit number of results
final users = await DB.table('users').limit(10).get();

// Skip records (offset)
final users = await DB.table('users').skip(10).limit(10).get();

// Take records (alias for limit)
final users = await DB.table('users').take(10).get();

// Offset
final users = await DB.table('users').offset(10).limit(10).get();

Aggregations

Count

// Count all records
final count = await DB.table('users').count();

// Count specific column
final count = await DB.table('users').count('email');

// Count with conditions
final activeCount = await DB.table('users')
    .where('status', '=', 'active')
    .count();

Mathematical Aggregations

// Sum
final totalAmount = await DB.table('orders').sum('amount');

// Average
final avgAge = await DB.table('users').avg('age');

// Maximum
final maxAmount = await DB.table('orders').max('amount');

// Minimum
final minAge = await DB.table('users').min('age');

Getting Single Values

// Get single column value
final name = await DB.table('users').where('id', '=', 1).value('name');

// Pluck column values
final names = await DB.table('users').pluck('name');

// Pluck with key
final namesByEmail = await DB.table('users').pluck('name', 'email');

Pagination

Simple Pagination

// Basic pagination
final result = await DB.table('users').paginate(perPage: 15);

print('Current page: ${result['current_page']}');
print('Total pages: ${result['last_page']}');
print('Total records: ${result['total']}');
print('Data: ${result['data']}');

Advanced Pagination

// Pagination with specific page
final result = await DB.table('users').paginate(
  perPage: 20,
  page: 2,
  columns: ['id', 'name', 'email'],
);

// Simple pagination (previous/next only)
final result = await DB.table('users').simplePaginate(
  perPage: 15,
  columns: ['id', 'name', 'email'],
  pageName: 'page',
  page: 1,
);

CRUD Operations

Insert Operations

// Insert single record
final success = await DB.table('users').insert({
  'name': 'John Doe',
  'email': 'john@example.com',
  'password': 'hashed_password',
});

// Insert and get ID
final id = await DB.table('users').insertGetId({
  'name': 'Jane Doe',
  'email': 'jane@example.com',
});

// Insert multiple records
final success = await DB.table('users').insertMany([
  {'name': 'User 1', 'email': 'user1@example.com'},
  {'name': 'User 2', 'email': 'user2@example.com'},
]);

// Insert or ignore (skip if duplicate)
final success = await DB.table('users').insertOrIgnore({
  'name': 'John Doe',
  'email': 'john@example.com',
});

// Upsert (insert or update)
final success = await DB.table('users').upsert(
  {'email': 'john@example.com', 'name': 'John Updated'},
  ['email'], // unique columns
  {'name': 'John Updated'}, // update data
);

Update Operations

// Update records
final success = await DB.table('users')
    .where('id', '=', 1)
    .update({'name': 'Updated Name'});

// Update or insert
final success = await DB.table('users').updateOrInsert(
  {'email': 'john@example.com'}, // search criteria
  {'name': 'John Doe', 'updated_at': DateTime.now()}, // update data
);

// Increment/Decrement values
final success = await DB.table('users')
    .where('id', '=', 1)
    .increment('login_count');

final success = await DB.table('users')
    .where('id', '=', 1)
    .increment('points', 10);

final success = await DB.table('users')
    .where('id', '=', 1)
    .decrement('attempts', 1);

// Increment multiple columns
final success = await DB.table('users')
    .where('id', '=', 1)
    .incrementEach({
      'posts_count': 1,
      'comments_count': 5,
    });

Delete Operations

// Delete records
final success = await DB.table('users')
    .where('status', '=', 'inactive')
    .delete();

// Truncate table
final success = await DB.table('users').truncate();

// Force truncate (ignore foreign key constraints)
final success = await DB.table('users').truncate(force: true);

Transactions

Basic Transactions

// Simple transaction
final success = await DB.transaction(() async {
  await DB.table('users').insert({'name': 'John', 'email': 'john@example.com'});
  await DB.table('profiles').insert({'user_id': 1, 'bio': 'User bio'});
  
  // If any operation fails, the entire transaction is rolled back
}).catchError((e)  {
  print('Error $e');
  return false;
});

print('Transaction successful: $success');

Bulk Transactional Operations

// Transactional bulk operations
final success = await DB.table('users').transactionalBulkOperation(() async {
  await DB.table('users').bulkInsert(userData);
  await DB.table('user_profiles').bulkInsert(profileData);
  await DB.table('user_permissions').bulkInsert(permissionData);
});

Bulk Operations

Bulk Insert

// Basic bulk insert
final data = [
  {'name': 'User 1', 'email': 'user1@example.com'},
  {'name': 'User 2', 'email': 'user2@example.com'},
  {'name': 'User 3', 'email': 'user3@example.com'},
];

final success = await DB.table('users').bulkInsert(
  data,
  batchSize: 1000,
  conflictAction: ConflictAction.ignore,
);

// Bulk insert with conflict resolution
final success = await DB.table('users').bulkInsert(
  data,
  conflictAction: ConflictAction.update,
  conflictColumns: ['email'],
  updateColumns: ['name', 'updated_at'],
);

Bulk Update

// Bulk update with match column
final updates = [
  {'id': 1, 'name': 'Updated User 1', 'status': 'active'},
  {'id': 2, 'name': 'Updated User 2', 'status': 'active'},
  {'id': 3, 'name': 'Updated User 3', 'status': 'inactive'},
];

final success = await DB.table('users').bulkUpdate(
  updates,
  matchColumn: 'id',
  updateColumns: ['name', 'status'],
  batchSize: 500,
);

Bulk Delete

// Bulk delete by IDs
final success = await DB.table('users').bulkDelete(
  column: 'id',
  values: [1, 2, 3, 4, 5],
  batchSize: 1000,
);

// Bulk delete with conditions
final conditions = [
  {'status': 'inactive', 'last_login': '< 2023-01-01'},
  {'email_verified': false, 'created_at': '< 2024-01-01'},
];

final success = await DB.table('users').bulkDeleteWhere(
  conditions,
  batchSize: 500,
);

Merge Operations

// Advanced merge operation
final sourceData = [
  {'id': 1, 'name': 'John Updated', 'email': 'john@example.com'},
  {'id': 2, 'name': 'Jane New', 'email': 'jane@example.com'},
];

final success = await DB.table('users').merge(
  sourceData,
  matchOn: ['email'],
  whenMatched: ConflictAction.update,
  whenNotMatched: ConflictAction.insert,
  updateColumns: ['name'],
  additionalValues: {'updated_at': DateTime.now()},
);

Parallel Processing

// Parallel bulk insert
final success = await DB.table('users').parallelBulkInsert(
  data,
  parallelism: 4,
  batchSize: 1000,
  conflictAction: ConflictAction.ignore,
);

// Batch processing
await DB.table('users').batchProcess(
  batchSize: 1000,
  processor: (batch, batchNumber) async {
    print('Processing batch $batchNumber with ${batch.length} records');
    // Process each batch
    for (final record in batch) {
      // Custom processing logic
      await processRecord(record);
    }
  },
);

// Chunked processing with transformation
await DB.table('users').chunkedProcess(
  chunkSize: 500,
  processor: (chunk) async {
    // Transform and return processed data
    return chunk.map((record) {
      record['processed_at'] = DateTime.now().toIso8601String();
      return record;
    }).toList();
  },
  destination: 'processed_users', // Optional destination table
);

Window Functions

Ranking Functions

// Row number
final users = await DB.table('users')
    .rowNumber(
      partitionBy: 'department',
      orderBy: 'salary DESC',
      as: 'row_num',
    )
    .get();

// Rank
final users = await DB.table('users')
    .rank(
      partitionBy: 'department',
      orderBy: 'salary DESC',
      as: 'rank',
    )
    .get();

// Dense rank
final users = await DB.table('users')
    .denseRank(
      partitionBy: 'department',
      orderBy: 'salary DESC',
      as: 'dense_rank',
    )
    .get();

Value Functions

// Lag (previous value)
final users = await DB.table('users')
    .lag(
      'salary',
      offset: 1,
      defaultValue: 0,
      partitionBy: 'department',
      orderBy: 'hire_date',
      as: 'prev_salary',
    )
    .get();

// Lead (next value)
final users = await DB.table('users')
    .lead(
      'salary',
      offset: 1,
      defaultValue: 0,
      partitionBy: 'department',
      orderBy: 'hire_date',
      as: 'next_salary',
    )
    .get();

// First value
final users = await DB.table('users')
    .firstValue(
      'salary',
      partitionBy: 'department',
      orderBy: 'hire_date',
      as: 'first_salary',
    )
    .get();

// Last value
final users = await DB.table('users')
    .lastValue(
      'salary',
      partitionBy: 'department',
      orderBy: 'hire_date',
      as: 'last_salary',
    )
    .get();

Distribution Functions

// Ntile (percentile buckets)
final users = await DB.table('users')
    .ntile(
      4, // 4 buckets (quartiles)
      partitionBy: 'department',
      orderBy: 'salary',
      as: 'quartile',
    )
    .get();

// Percent rank
final users = await DB.table('users')
    .percentRank(
      partitionBy: 'department',
      orderBy: 'salary',
      as: 'percent_rank',
    )
    .get();

// Cumulative distribution
final users = await DB.table('users')
    .cumeDist(
      partitionBy: 'department',
      orderBy: 'salary',
      as: 'cume_dist',
    )
    .get();

Aggregate Window Functions

// Window sum
final users = await DB.table('users')
    .windowSum(
      'salary',
      partitionBy: 'department',
      orderBy: 'hire_date',
      as: 'running_total',
    )
    .get();

// Window average
final users = await DB.table('users')
    .windowAvg(
      'salary',
      partitionBy: 'department',
      as: 'dept_avg_salary',
    )
    .get();

// Window count
final users = await DB.table('users')
    .windowCount(
      'id',
      partitionBy: 'department',
      as: 'dept_employee_count',
    )
    .get();

// Window max/min
final users = await DB.table('users')
    .windowMax(
      'salary',
      partitionBy: 'department',
      as: 'dept_max_salary',
    )
    .windowMin(
      'salary',
      partitionBy: 'department',
      as: 'dept_min_salary',
    )
    .get();

Common Table Expressions (CTEs)

Basic CTEs

// Simple CTE
final hierarchyQuery = DB.table('employees')
    .select(['id', 'name', 'manager_id'])
    .where('manager_id', '=', null);

final result = await DB.table('hierarchy')
    .withCte('hierarchy', hierarchyQuery)
    .select(['*'])
    .get();

Multiple CTEs

// Multiple CTEs
final departmentStats = DB.table('employees')
    .select(['department_id', DB.raw('COUNT(*) as emp_count')])
    .groupBy(['department_id']);

final salaryStats = DB.table('employees')
    .select(['department_id', DB.raw('AVG(salary) as avg_salary')])
    .groupBy(['department_id']);

final result = await DB.table('departments')
    .withMultiple({
      'dept_counts': departmentStats,
      'salary_avgs': salaryStats,
    })
    .join('dept_counts', 'departments.id', '=', 'dept_counts.department_id')
    .join('salary_avgs', 'departments.id', '=', 'salary_avgs.department_id')
    .get();

Recursive CTEs

// Recursive CTE for hierarchical data
final baseCase = DB.table('employees')
    .select(['id', 'name', 'manager_id', DB.raw('0 as level')])
    .where('manager_id', '=', null);

final recursiveCase = DB.table('employees')
    .select(['e.id', 'e.name', 'e.manager_id', DB.raw('h.level + 1')])
    .join('hierarchy h', 'e.manager_id', '=', 'h.id');

final result = await DB.table('hierarchy')
    .withRecursive('hierarchy', baseCase, recursiveCase, 
        columns: ['id', 'name', 'manager_id', 'level'])
    .orderBy('level')
    .orderBy('name')
    .get();

Materialized CTEs

// Materialized CTE (PostgreSQL)
final expensiveQuery = DB.table('large_table')
    .join('another_large_table', 'large_table.id', '=', 'another_large_table.ref_id')
    .select(['complex_calculation_result'])
    .groupBy(['category']);

final result = await DB.table('processed_data')
    .withMaterialized('processed_data', expensiveQuery)
    .where('category', '=', 'important')
    .get();

// Non-materialized CTE
final result = await DB.table('temp_data')
    .withNotMaterialized('temp_data', expensiveQuery)
    .limit(100)
    .get();

Raw Expressions

Using Raw SQL

// Raw select
final users = await DB.table('users')
    .selectRaw('COUNT(*) as total, status')
    .groupBy(['status'])
    .get();

// Raw where
final users = await DB.table('users')
    .whereRaw('age > ? AND status = ?', [18, 'active'])
    .get();

// Raw expressions in various contexts
final users = await DB.table('users')
    .select(['name', DB.raw('DATE(created_at) as join_date')])
    .orderByRaw('FIELD(status, "active", "pending", "inactive")')
    .get();

Complex Raw Queries

final stats = await DB.table('orders')
    .selectRaw('''
        DATE(created_at) as order_date,
        COUNT(*) as total_orders,
        SUM(amount) as total_amount,
        AVG(amount) as avg_amount,
        CASE 
            WHEN COUNT(*) > 100 THEN "high"
            WHEN COUNT(*) > 50 THEN "medium"
            ELSE "low"
        END as volume_category
    ''')
    .groupByRaw('DATE(created_at)')
    .havingRaw('COUNT(*) > ?', [10])
    .get();

IsolateDB - Isolated Database Operations

Overview

IsolateDB enables running database operations in separate Dart isolates, providing true parallel processing and preventing heavy database operations from blocking the main application thread. This is particularly useful for CPU-intensive tasks, bulk operations, and long-running queries.

Basic Usage

import 'package:vania/vania.dart';

// Get database configuration from your app config
final dbConfig = database['connections']['mysql'];

// Run database operations in isolate
final result = await IsolateDB.run(() async {
  // All DB operations here run in a separate isolate
  final users = await DB.table('users')
      .where('status', '=', 'active')
      .get();
  
  return users.length;
}, dbConfig);

print('Found $result active users');

Query Debugging

// Debug query building
final query = DB.table('users')
    .where('status', '=', 'active')
    .where('age', '>=', 18)
    .orderBy('name');

// Get SQL string
print('SQL: ${query.toSql()}');

// Get SQL with bound values
print('Raw SQL: ${query.toRawSql()}');

// Get bindings
print('Bindings: ${query.getBindings()}');