Query Builder

The Query Builder provides a fluent, type-safe API for building database queries.

Snippet context

• Snippets focus on the query builder calls and omit full setup.
• Unless shown otherwise, assume either:
  • a DataSource named dataSource, or
  • a default connection configured so static helpers like Users.query() work.

Default connection resolution is handled through ConnectionManager.

Getting Started

Default connection

Future<void> queryGettingStartedStatic() async {
  // Assumes a default connection is configured (see DataSource docs).
  final users = await Users.query().get();
}

From DataSource

Future<void> queryGettingStarted(DataSource dataSource) async {
  final users = await dataSource.query<$User>().get();
}

Table Prefixing

If DataSourceOptions.tablePrefix is set, Ormed automatically prefixes all unqualified table references (base tables, joins, relation/pivot tables, and ad‑hoc table() calls). Schema‑qualified names like public.users are left untouched.

Selecting Data

Get All Records

Future<void> queryGetAll(DataSource dataSource) async {
  final users = await dataSource.query<$User>().get();
}

Select Specific Columns

Future<void> querySelect(DataSource dataSource) async {
  final users = await dataSource.query<$User>().select([
    'id',
    'email',
    'name',
  ]).get();
}

First Record

Future<void> queryFirst(DataSource dataSource) async {
  // Returns null if not found
  final user = await dataSource.query<$User>().first();

  // Throws if not found
  final userOrFail = await dataSource.query<$User>().firstOrFail();
}

Find by Primary Key

Future<void> queryFind(DataSource dataSource) async {
  final user = await dataSource.query<$User>().find(1);
  final userOrFail = await dataSource.query<$User>().findOrFail(1);
}

Where Clauses

Common patterns

• Prefer whereEquals('id', id) for primary key lookups.
• Use whereIn for sets, and whereBetween for ranges.
• Group complex logic with where((q) => ...) so operator precedence is explicit.
• Use whereTyped((q) => q.field.eq(...)) for typed predicate fields; untyped callbacks still work but are dynamic.

Examples

Future<void> whereClauseExamples(QueryContext context) async {
  // Equals
  await context.query<$User>().whereEquals('name', 'John').get();

  // Not equals
  await context.query<$User>().whereNotEquals('status', 'banned').get();

  // Comparison operators
  await context.query<$User>().whereGreaterThan('age', 18).get();
  await context.query<$User>().whereGreaterThanOrEqual('age', 21).get();
  await context.query<$User>().whereLessThan('age', 65).get();

  // In clause
  await context.query<$User>().whereIn('role', ['admin', 'moderator']).get();

  // Like clause
  await context.query<$User>().whereLike('name', '%john%').get();

  // Null checks
  await context.query<$User>().whereNull('deletedAt').get();
  await context.query<$User>().whereNotNull('emailVerifiedAt').get();

  // Between
  await context.query<$User>().whereBetween('age', 18, 65).get();
}

Comparison Operators

Future<void> whereComparison(DataSource dataSource) async {
  final users = await dataSource
      .query<$User>()
      .where('age', '>', 18)
      .where('age', '<=', 65)
      .get();
}

Qualified Columns

You can use qualified column names (e.g., table.column) in where clauses. This is particularly useful when working with joins or many-to-many relationships where column names might collide.

In / Not In

Future<void> whereIn(DataSource dataSource) async {
  final users = await dataSource.query<$User>().whereIn('role', [
    'admin',
    'moderator',
  ]).get();

  final bannedUsers = await dataSource.query<$User>().whereNotIn('status', [
    'banned',
    'suspended',
  ]).get();
}

Null Checks

Future<void> whereNull(DataSource dataSource) async {
  final users = await dataSource.query<$User>().whereNull('deleted_at').get();

  final verifiedUsers = await dataSource
      .query<$User>()
      .whereNotNull('verified_at')
      .get();
}

Between

Future<void> whereBetween(DataSource dataSource) async {
  final users = await dataSource
      .query<$User>()
      .whereBetween('age', 18, 65)
      .get();
}

Like / Contains

Future<void> whereLike(DataSource dataSource) async {
  final users = await dataSource
      .query<$User>()
      .whereLike('email', '%@example.com')
      .get();
}

Or Where

Future<void> whereOr(DataSource dataSource) async {
  final users = await dataSource
      .query<$User>()
      .whereEquals('role', 'admin')
      .orWhere('role', '=', 'moderator')
      .get();
}

Grouped Conditions

Future<void> whereGrouped(DataSource dataSource) async {
  final users = await dataSource
      .query<$User>()
      .where('active', '=', true)
      .whereGroup(
        (q) => q.where('role', '=', 'admin').orWhere('role', '=', 'moderator'),
      )
      .get();

  // SQL: WHERE active = 1 AND (role = 'admin' OR role = 'moderator')
}

Typed Predicate Fields

Future<void> whereTypedPredicateExamples(QueryContext context) async {
  // Typed predicate fields via whereTyped
  await context
      .query<$User>()
      .whereTyped((q) => q.email.eq('alice@example.com'))
      .get();

  // Typed field access also works in untyped callbacks (dynamic), but without
  // static checking.
  await context.query<$User>().where((q) => q.name.isNotNull()).get();
}

Ordering

Future<void> orderingLimitingExamples(QueryContext context) async {
  // Order by single column (ascending by default)
  await context.query<$User>().orderBy('name').get();

  // Order descending
  await context.query<$User>().orderBy('createdAt', descending: true).get();

  // Multiple order by
  await context.query<$User>().orderBy('lastName').orderBy('firstName').get();

  // Limit and offset
  await context.query<$User>().limit(10).get();
  await context.query<$User>().limit(10).offset(20).get();

  // First record
  final firstUser = await context.query<$User>().orderBy('id').first();
}

Joins

Join multiple tables to query related data in a single query. Use join for inner joins (only matching rows), leftJoin to include all left-side rows even without matches, rightJoin for all right-side rows, and crossJoin for Cartesian products. joinRelation leverages defined model relationships.

Future<void> joinsExamples(DataSource dataSource) async {
  // Inner join
  final usersWithPosts = await dataSource
      .query<$User>()
      .join('posts', 'users.id', '=', 'posts.author_id')
      .get();

  // Left join - includes users without posts
  final allUsersWithOptionalPosts = await dataSource
      .query<$User>()
      .leftJoin('posts', 'users.id', '=', 'posts.author_id')
      .get();

  // Right join
  final allPostsWithOptionalUsers = await dataSource
      .query<$Post>()
      .rightJoin('users', 'posts.author_id', '=', 'users.id')
      .get();

  // Cross join - Cartesian product
  final productColors = await dataSource
      .query<$User>()
      .crossJoin('colors')
      .get();

  // Join with callback for complex conditions
  final activeUserPosts = await dataSource.query<$User>().join('posts', (join) {
    join.on('users.id', '=', 'posts.author_id')
        .where('posts.status', '=', 'published');
  }).get();

  // Join through relationships
  final usersWithPublishedPosts = await dataSource
      .query<$User>()
      .joinRelation('posts')
      .whereEquals('posts.published', true)
      .get();
}

Subqueries

Filter results based on the output of another query. Subqueries are useful when you need to filter by aggregate results or check for existence of related records without joining. Use whereInSubquery to match values from a subquery, whereExists for existence checks (often more efficient than joins for filtering).

Future<void> subqueryExamples(DataSource dataSource) async {
  // WHERE IN subquery - users with published posts
  final activeAuthors = await dataSource.query<$User>().whereInSubquery(
    'id',
    dataSource.query<$Post>().select(['author_id']).whereEquals('published', true),
  ).get();

  // WHERE NOT IN subquery - users without posts
  final usersWithoutPosts = await dataSource.query<$User>().whereNotInSubquery(
    'id',
    dataSource.query<$Post>().select(['author_id']),
  ).get();

  // WHERE EXISTS - users with at least one comment
  final usersWithComments = await dataSource.query<$User>().whereExists(
    dataSource.query<$Comment>().whereColumn('comments.user_id', 'users.id'),
  ).get();

  // WHERE NOT EXISTS - users with no comments
  final usersWithoutComments = await dataSource.query<$User>().whereNotExists(
    dataSource.query<$Comment>().whereColumn('comments.user_id', 'users.id'),
  ).get();
}

Group By & Having

Group rows by column values and aggregate data. Use groupBy to group rows, then filter the grouped results with having (not where — where filters before grouping, having filters after). Commonly used with aggregate functions like count, sum, avg.

Future<void> groupingExamples(DataSource dataSource) async {
  // Group by single column
  final usersByCity = await dataSource
      .query<$User>()
      .select(['city'])
      .countAggregate(alias: 'total')
      .groupBy(['city'])
      .get();

  // Group by multiple columns
  final usersByStateAndCity = await dataSource
      .query<$User>()
      .select(['state', 'city'])
      .countAggregate(alias: 'total')
      .groupBy(['state', 'city'])
      .get();

  // HAVING clause - filter grouped results
  final popularCities = await dataSource
      .query<$User>()
      .select(['city'])
      .countAggregate(alias: 'user_count')
      .groupBy(['city'])
      .having('user_count', PredicateOperator.greaterThan, 100)
      .get();

  // HAVING with raw SQL
  final citiesWithHighTotalAge = await dataSource
      .query<$User>()
      .select(['city'])
      .groupBy(['city'])
      .havingRaw('SUM(age) > ?', [500])
      .get();
}

Pagination

Numbered pages with totals

Use paginate when you need total counts and page metadata.

Future<void> paginationWithTotals(DataSource dataSource) async {
  final usersPage = await dataSource
      .query<$User>()
      .paginate(perPage: 10, page: 2);

  print('Total users: ${usersPage.total}');
  print('Page ${usersPage.currentPage} of ${usersPage.lastPage}');

  for (final row in usersPage.items) {
    print('User ${row.model.id}: ${row.model.email}');
  }
}

Simple pagination (no count)

Skip the count query with simplePaginate to quickly determine if more pages exist.

Future<void> paginationWithoutCount(DataSource dataSource) async {
  final page = await dataSource
      .query<$User>()
      .simplePaginate(perPage: 20, page: 1);

  print('Loaded ${page.items.length} users');
  print('Has more pages: ${page.hasMorePages}');
}

Cursor pagination

cursorPaginate avoids OFFSET for large datasets and can paginate on any sortable column (default: primary key).

Future<void> cursorPagination(DataSource dataSource) async {
  final firstPage = await dataSource
      .query<$User>()
      .cursorPaginate(perPage: 5, column: 'id');

  final nextPage = await dataSource
      .query<$User>()
      .cursorPaginate(
        perPage: 5,
        column: 'id',
        cursor: firstPage.nextCursor,
      );

  print('Has more: ${nextPage.hasMore}');
  print('Next cursor: ${nextPage.nextCursor}');
}

Chunking and streaming

Process large tables without loading everything at once. chunk uses offset/limit; chunkById/eachById walk an ordered column; streaming yields rows as they arrive.

Future<void> paginationChunking(DataSource dataSource) async {
  await dataSource.query<$User>().chunk(100, (rows) async {
    for (final row in rows) {
      print('Chunk user: ${row.model.id}');
    }
    return true; // continue
  });

  await dataSource.query<$User>().chunkById(50, (rows) async {
    for (final row in rows) {
      print('Chunk by id user: ${row.model.id}');
    }
    return true;
  }, column: 'id');

  await dataSource.query<$User>().eachById(25, (row) async {
    if (row.model.id == 42) {
      return false; // stop early
    }
    return true;
  }, column: 'id');
}

Future<void> paginationStreaming(DataSource dataSource) async {
  await for (final row in dataSource
      .query<$User>()
      .streamRows(eagerLoadBatchSize: 200)) {
    print('Row email: ${row.model.email}');
  }

  await for (final user in dataSource.query<$User>().streamModels()) {
    print('User model: ${user.email}');
  }
}

Group Limits

Limit results per group using window functions. This requires a driver/database that supports window functions.

Future<void> groupLimitExamples(QueryContext context) async {
  final recentPerAuthor = await context
      .query<$Post>()
      .orderBy('publishedAt', descending: true)
      .limitPerGroup(2, 'authorId', offset: 1)
      .get();
}

Aggregates

Future<void> aggregateExamples(QueryContext context) async {
  // Count
  final userCount = await context.query<$User>().count();

  // Exists check
  final hasAdmins = await context
      .query<$User>()
      .whereEquals('role', 'admin')
      .exists();

  // Max/Min
  final maxAge = await context.query<$User>().max('age');
  final minAge = await context.query<$User>().min('age');

  // Sum/Avg
  final totalBalance = await context.query<$User>().sum('balance');
  final avgAge = await context.query<$User>().avg('age');
}

Distinct

Future<void> distinctExample(DataSource dataSource) async {
  final roles = await dataSource.query<$User>().distinct().select([
    'role',
  ]).get();
}

Eager Loading Relations

Future<void> relationExamples(QueryContext context) async {
  // Eager load a relation
  final posts = await context.query<$Post>().with_(['author']).get();

  // Load multiple relations
  final postsWithComments = await context.query<$Post>().with_([
    'author',
    'comments',
  ]).get();

  // Nested relation loading
  final postsDeep = await context.query<$Post>().with_([
    'author.profile',
    'comments.user',
  ]).get();

  // Join relation for filtering without loading
  final userPosts = await context
      .query<$User>()
      .joinRelation('posts')
      .whereEquals('posts.published', true)
      .get();
}

Typed Relation Helpers

Future<void> typedRelationHelpers(QueryContext context) async {
  final users = await context
      .query<$UserWithPosts>()
      .whereHasPosts((q) => q.title.like('%Dart%'))
      .withPosts((q) => q.title.like('%Dart%'))
      .get();
}

Raw Expressions

When you need database-specific functionality:

Future<void> rawExpressions(DataSource dataSource) async {
  final users = await dataSource.query<$User>().whereRaw("LOWER(email) = ?", [
    'john@example.com',
  ]).get();

  final usersWithFullName = await dataSource
      .query<$User>()
      .selectRaw("*, CONCAT(first_name, ' ', last_name) AS full_name")
      .get();
}

Partial Projections

Get partial entities with only selected columns:

Future<void> partialProjections(DataSource dataSource) async {
  final partial = await dataSource.query<$User>().select([
    'id',
    'email',
  ]).firstPartial();

  print(partial?.id); // Available
  print(partial?.email); // Available
  // partial?.name is not available (not selected)
}

Soft Delete Scopes

For models with soft deletes:

Future<void> softDeleteScopes(DataSource dataSource) async {
  // Default: excludes soft-deleted
  final posts = await dataSource.query<$Post>().get();

  // Include soft-deleted
  final allPosts = await dataSource.query<$Post>().withTrashed().get();

  // Only soft-deleted
  final trashedPosts = await dataSource.query<$Post>().onlyTrashed().get();
}

Query Caching

Cache query results for improved performance:

Future<void> queryCaching(DataSource dataSource) async {
  // Cache for 5 minutes
  final users = await dataSource
      .query<$User>()
      .remember(Duration(minutes: 5))
      .get();

  // Cache forever (until manually cleared)
  final settings = await dataSource.query<$User>().rememberForever().get();

  // Disable caching for specific query
  final freshData = await dataSource.query<$User>().dontRemember().get();

  // Clear query cache
  await dataSource.flushQueryCache();
}

Unions

Combine result sets from multiple queries. union removes duplicate rows (slower but cleaner), while unionAll keeps all rows including duplicates (faster). Both queries must have the same column structure. insertUsing efficiently bulk-inserts rows from another query.

Future<void> unionExamples(DataSource dataSource) async {
  // UNION - combine results, removing duplicates
  final activeOrAdminUsers = await dataSource
      .query<$User>()
      .whereEquals('active', true)
      .union(dataSource.query<$User>().whereEquals('role', 'admin'))
      .get();

  // UNION ALL - combine results, keeping duplicates
  final allUsersIncludingDupes = await dataSource
      .query<$User>()
      .whereEquals('active', true)
      .unionAll(dataSource.query<$User>().whereEquals('role', 'admin'))
      .get();

  // INSERT USING - bulk insert from another query
  final archivedCount = await dataSource.query<$User>().insertUsing(
    ['name', 'email', 'archived_at'],
    dataSource
        .query<$User>()
        .whereEquals('active', false)
        .select(['name', 'email'])
        .selectRaw('NOW()'),
  );
}

Locking

Lock rows within transactions to prevent race conditions and ensure data consistency. Use lockForUpdate when you'll modify the rows (prevents other transactions from reading or modifying), or sharedLock when you only need to read (allows concurrent reads but prevents modifications). Always use within a transaction.

Future<void> lockingExamples(DataSource dataSource) async {
  await dataSource.transaction(() async {
    // Lock for update - prevents other transactions from modifying
    final user = await dataSource
        .query<$User>()
        .whereEquals('id', 1)
        .lockForUpdate()
        .first();

    // Update the locked row
    if (user != null) {
      await dataSource
          .query<$User>()
          .whereEquals('id', 1)
          .update({'balance': user.row['balance'] + 100});
    }
  });

  await dataSource.transaction(() async {
    // Shared lock - allows reads but prevents modifications
    final user = await dataSource
        .query<$User>()
        .whereEquals('id', 1)
        .sharedLock()
        .first();

    // Can read but other transactions can't modify
    print('User balance: ${user?.row['balance']}');
  });
}

Scopes & Macros

Encapsulate reusable query logic. Local scopes are model-specific constraints (e.g., User.scope('active')), global scopes automatically apply to all queries for a model (e.g., soft deletes), and macros work across any model type. Use withoutGlobalScopes to bypass automatic filters when needed.

Future<void> scopesExamples(DataSource dataSource) async {
  // Apply a local scope (must be registered first)
  final activeUsers = await dataSource.query<$User>().scope('active').get();

  // Apply a query macro (global, works on any model)
  final recentPosts = await dataSource
      .query<$Post>()
      .macro('recent', [7]) // last 7 days
      .get();

  // Ignore global scopes
  final allPosts = await dataSource
      .query<$Post>()
      .withoutGlobalScopes() // includes soft-deleted
      .get();

  // Ignore specific global scope
  final postsIncludingDeleted = await dataSource
      .query<$Post>()
      .withoutGlobalScope('softDeletes')
      .get();
}

CRUD Operations

Create, update, and delete records.

Future<void> crudExamples(DataSource dataSource) async {
  // Create a single record
  final user = await dataSource.query<$User>().create({
    'name': 'John Doe',
    'email': 'john@example.com',
  });

  // Create multiple records
  final users = await dataSource.query<$User>().createMany([
    {'name': 'Alice', 'email': 'alice@example.com'},
    {'name': 'Bob', 'email': 'bob@example.com'},
  ]);

  // Update records
  final updatedCount = await dataSource
      .query<$User>()
      .whereEquals('active', false)
      .update({'status': 'inactive'});

  // Delete records
  final deletedCount = await dataSource
      .query<$User>()
      .whereEquals('banned', true)
      .delete();

  // Increment/decrement
  await dataSource
      .query<$User>()
      .whereEquals('id', 1)
      .increment('login_count', 1);

  await dataSource
      .query<$User>()
      .whereEquals('id', 1)
      .decrement('credits', 10);

  // Upsert - insert or update if exists
  await dataSource.query<$User>().upsert(
    [
      {'email': 'john@example.com', 'name': 'John Updated'},
    ],
    uniqueBy: ['email'],
    update: ['name'],
  );
}

Utility Methods

Helper methods for query building and debugging.

Future<void> utilityMethodsExamples(DataSource dataSource) async {
  // Conditional query building with when()
  final orderStatus = 'shipped';
  final orders = await dataSource
      .query<$Post>()
      .when(orderStatus == 'shipped', (q) => q.whereEquals('status', 'shipped'))
      .get();

  // unless() - inverse of when()
  final postsIgnoreDraft = await dataSource
      .query<$Post>()
      .unless(
        false,
        (q) => q.whereEquals('status', 'published'),
      )
      .get();

  // tap() - debug/inspect query without modifying it
  final users = await dataSource
      .query<$User>()
      .tap((q) => print('Query: ${q}')) // inspect
      .whereEquals('active', true)
      .get();

  // value() - get single column value
  final maxAge = await dataSource
      .query<$User>()
      .orderBy('age', descending: true)
      .value<int>('age');

  print('Max age: $maxAge');
}

Advanced Joins

Beyond basic joins—subquery joins, lateral joins, and index hints.

Future<void> advancedJoinsExamples(DataSource dataSource) async {
  // Join with WHERE clause combined
  final usersWithActiveComments = await dataSource
      .query<$User>()
      .joinWhere('comments', 'users.id', '=', 'comments.user_id')
      .where('comments.status', '=', 'active')
      .get();

  // Left join with WHERE
  final allUsersWithActiveComments = await dataSource
      .query<$User>()
      .leftJoinWhere('comments', 'users.id', '=', 'comments.user_id')
      .get();

  // Join a subquery
  final recentPostsByTopAuthors = await dataSource.query<$Post>().joinSub(
    'top_authors',
    (q) => q
        .select(['id', 'name'])
        .where('posts', '>', 10),
    'top_authors.id',
    '=',
    'posts.author_id',
  ).get();

  // Straight join (MySQL - forces left-to-right evaluation)
  final straightJoinExample = await dataSource
      .query<$User>()
      .straightJoin('posts', 'users.id', '=', 'posts.user_id')
      .get();

  // Cross join subquery (Cartesian product with subquery)
  final cartesianWithSubquery = await dataSource
      .query<$Product>()
      .crossJoinSub(
        'sizes',
        (q) => q.select(['id', 'name']).from('sizes'),
      )
      .get();
}

JSON Queries

Query and filter JSON fields with path expressions and operations.

Future<void> jsonQueryExamples(DataSource dataSource) async {
  // JSON contains - check if JSON field contains a value
  final usersWithRole = await dataSource
      .query<$User>()
      .whereJsonContains('permissions', 'admin')
      .get();

  // JSON contains key - check if JSON field has a specific key
  final usersWithFeatureFlag = await dataSource
      .query<$User>()
      .whereJsonContainsKey('features', 'beta_access')
      .get();

  // JSON length - filter by JSON array/object size
  final usersWithMultipleTags = await dataSource
      .query<$User>()
      .whereJsonLength('tags', '>', 2)
      .get();

  // JSON overlaps - check if two JSON arrays share elements
  final usersWithCommonTags = await dataSource
      .query<$User>()
      .whereJsonOverlaps('tags', ['popular', 'featured'])
      .get();

  // Complex: combine JSON query with other conditions
  final activeAdminsWithFeature = await dataSource
      .query<$User>()
      .whereEquals('active', true)
      .whereJsonContains('roles', 'admin')
      .whereJsonContainsKey('features', 'analytics')
      .get();
}

Index Hints

Optimize queries by directing the database optimizer to use (or avoid) specific indexes. MySQL-specific.

Future<void> indexHintsExamples(DataSource dataSource) async {
  // Use index hint - suggest an index (not required to use)
  final usersWithIndex = await dataSource
      .query<$User>()
      .useIndex(['email_idx'])
      .whereEquals('email', 'john@example.com')
      .get();

  // Force index - require use of specific index
  final forcedIndexQuery = await dataSource
      .query<$User>()
      .forceIndex(['active_user_idx'])
      .whereEquals('active', true)
      .get();

  // Ignore index - exclude specific index from optimizer
  final ignoreIndexQuery = await dataSource
      .query<$User>()
      .ignoreIndex(['slow_index'])
      .whereEquals('role', 'admin')
      .get();

  // Multiple indexes
  final multiIndexQuery = await dataSource
      .query<$User>()
      .useIndex(['email_idx', 'active_idx'])
      .get();
}

Advanced Soft Delete Operations

Restore deleted records and permanently delete without soft deletes.

Future<void> softDeleteAdvancedExamples(DataSource dataSource) async {
  // Default: excludes soft-deleted records
  final activePosts = await dataSource.query<$Post>().get();

  // Include soft-deleted records
  final allPostsIncludingDeleted = await dataSource
      .query<$Post>()
      .withTrashed()
      .get();

  // Only soft-deleted records
  final onlyDeletedPosts = await dataSource.query<$Post>().onlyTrashed().get();

  // Restore soft-deleted record
  final restoredCount = await dataSource
      .query<$Post>()
      .onlyTrashed()
      .whereEquals('id', 1)
      .restore();

  print('Restored $restoredCount posts');

  // Permanently delete without triggering soft delete
  final permanentlyDeleted = await dataSource
      .query<$Post>()
      .whereEquals('id', 2)
      .forceDelete();

  // Restore multiple records
  final bulkRestore = await dataSource
      .query<$Post>()
      .onlyTrashed()
      .whereNull('restore_reason')
      .restore();
}

Advanced Ordering

Random order, relation order, and raw expressions.

Future<void> advancedOrderingExamples(DataSource dataSource) async {
  // Random order
  final randomUsers = await dataSource
      .query<$User>()
      .orderByRandom()
      .limit(10)
      .get();

  // Raw order expression
  final customOrdered = await dataSource
      .query<$User>()
      .orderByRaw("CASE WHEN active = 1 THEN 0 ELSE 1 END, name")
      .get();

  // Order by relation count (users with most posts first)
  final usersByPostCount = await dataSource
      .query<$User>()
      .orderByRelation('posts', 'desc')
      .get();

  // Combine multiple orderings
  final complexOrder = await dataSource
      .query<$User>()
      .whereEquals('active', true)
      .orderBy('role')
      .orderByRaw('LENGTH(name)')
      .orderByRandom()
      .get();
}

Column Comparison

Compare values between columns or with correlated subqueries.

Future<void> whereColumnExamples(DataSource dataSource) async {
  // Compare two columns
  final usersWithBalanceAboveLimit = await dataSource
      .query<$User>()
      .whereColumn('balance', '>', 'credit_limit')
      .get();

  // Check column equality
  final usersWithSameCityAsProfile = await dataSource
      .query<$User>()
      .whereColumn('city', '=', 'profile.city')
      .get();

  // Combined with other conditions
  final activeUsersOverLimit = await dataSource
      .query<$User>()
      .whereEquals('active', true)
      .whereColumn('debt', '>', 'max_debt_allowed')
      .get();
}