Skip to main content

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​

Future<void> queryGettingStartedStatic() async {
  // Assumes a default connection is configured (see DataSource docs).
  final users = await Users.query().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​

  • 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.

Comparison Operators​

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

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')
}

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();
}

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();
}

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();
}