skip to content

In Drift's Dart query API, how do you join two tables and read typed rows from the result, including rows with no match?

level: middleimportance: should knowfreq 30%

answer

  1. select(...).join([...])
  2. leftOuterJoin with equalsExp
  3. List<TypedResult>, not row classes
  4. readTable versus readTableOrNull
  5. addColumns and read for aggregates

basics

~10 s

Start with select(habits).join([leftOuterJoin(checkIns, checkIns.habit.equalsExp(habits.id))]); get() or watch() then yields TypedResult rows, read with readTable(habits) and readTableOrNull(checkIns) for the side that may be missing.

solid answer

~30 s

You begin a normal `select(habits)` and call `.join([...])` with join clauses built by `innerJoin`, `leftOuterJoin`, `rightOuterJoin`, `fullOuterJoin` or `crossJoin`; the first four take an `ON` expression such as `checkIns.habit.equalsExp(habits.id)`. The result is a joined statement whose `get()` returns `List<TypedResult>` and whose `watch()` returns a stream of them, re-running when any joined table changes. From each `TypedResult` you call `readTable(habits)` to get a `Habit`, and `readTableOrNull(checkIns)` for the outer side, because `readTable` throws an `ArgumentError` when that table has no row. `where` and `orderBy` can reference any joined table, and `addColumns([checkIns.id.count()])` with `groupBy` lets you read aggregates through `row.read(expr)`.

code

dart · 24 lines
dart
import 'package:drift/drift.dart';

class HabitStreak {
  HabitStreak(this.habit, this.checkIns);
  final Habit habit;
  final int checkIns;
}

extension StreakJoins on AppDatabase {
  Stream<List<HabitStreak>> watchStreaks() {
    final count = checkIns.id.count();
    final query = select(habits).join([
      leftOuterJoin(checkIns, checkIns.habit.equalsExp(habits.id),
          useColumns: false),
    ])
      ..addColumns([count])
      ..groupBy([habits.id]);

    return query.watch().map((rows) => [
          for (final row in rows)
            HabitStreak(row.readTable(habits), row.read(count) ?? 0),
        ]);
  }
}

go deeper

for a junior

Recall that joins start from select(table).join([...]) and return TypedResult rows you read table by table.

for a middle

Explain readTable versus readTableOrNull on outer joins, equalsExp in the ON clause, and reading aggregates with addColumns and read.

for a senior

Show how you shape joins for watched screens so they stay cheap, avoid N+1 loops and map results into view-model classes.

for a principal

Weigh the typed join builder against Drift's SQL files or custom queries for complex reporting, balancing reviewability and type safety.

## Why joins look different in Drift A plain Drift `select(habits)` returns the generated row class, `Habit`, because every column comes from one table. A **join** combines columns from several tables in one row, so there is no single generated class that fits. Drift therefore returns a generic **`TypedResult`** per row and lets you pull typed objects out of it table by table. In a habit tracker, a typical join lists every habit together with its most recent check-in, or counts check-ins per habit to render a streak badge. ## Building the join 1. Start from the primary table: `select(habits)`. 2. Call `.join([...])` with a list of join clauses. Drift offers `innerJoin`, `leftOuterJoin`, `rightOuterJoin`, `fullOuterJoin` and `crossJoin`. 3. For inner and outer joins, pass the `ON` condition as an expression. Comparing two columns uses `equalsExp`, as in `checkIns.habit.equalsExp(habits.id)`; `equals` compares a column with a Dart value. 4. Optionally chain `..where(...)` and `..orderBy([...])`. On a join these take expressions over any of the joined tables, not a lambda over one table. | Join helper | Rows kept | Read the joined side with | |---|---|---| | `innerJoin` | only habits with a check-in | `readTable(checkIns)` | | `leftOuterJoin` | every habit, check-in may be missing | `readTableOrNull(checkIns)` | | `crossJoin` | every combination, no `ON` | `readTable` on both | ## Reading the results - `get()` returns `Future<List<TypedResult>>`; `watch()` returns `Stream<List<TypedResult>>`. A watched join re-runs when **any** of the joined tables is written, which is exactly what a streak screen needs. - `row.readTable(habits)` returns a `Habit`. If that table contributed no columns to the row, as happens on the outer side of a left join, it throws an `ArgumentError` whose message points you at `readTableOrNull`. - `row.readTableOrNull(checkIns)` returns `CheckIn?`, which is the correct call for anything on the optional side of an outer join. - Most apps map each `TypedResult` into a small class of their own, such as `HabitWithLastCheckIn(habit, checkIn)`, before handing it to the UI. ## Aggregates and extra columns A join can also carry computed columns. Declare the expression once, for example `final count = checkIns.id.count();`, add it with `query.addColumns([count])`, group with `query.groupBy([habits.id])`, and read it back per row with `row.read(count)`, which returns a nullable `int?`. When you join a table only to filter or aggregate and do not want its columns in the result, pass `useColumns: false` to the join helper. The same pattern with `checkIns.day.max()` gives the latest check-in date per habit. ## Watched joins on a streak screen A join is a `Selectable` like any other query, so everything about stream queries applies to it. Its watch set is the union of every joined table: inserting a check-in, renaming a habit or deleting one all re-run the join. That is usually what a list screen wants, but it has two consequences worth stating in an interview: - A join over large tables that re-runs after every check-in insert costs a full query each time. Aggregating in SQL (`count()`, `max()`) and returning one row per habit keeps each re-run cheap, where watching raw check-ins and counting in Dart re-sends every row. - Mapping the `TypedResult` list into view-model objects inside `.map(...)` on the stream keeps widgets free of `readTable` calls and makes the result easy to compare in tests. Queries that the Dart builder expresses awkwardly, such as a consecutive-day streak calculation, can move into a `.drift` file or a `customSelect` with `readsFrom`; both remain watchable. ## Mistakes interviewers listen for - Calling `readTable` on the optional side of a left outer join and crashing on habits that have never been checked in. - Expecting `get()` on a join to return the primary table's row class directly. - Writing the `ON` condition with `equals` and a column, which does not compile, instead of `equalsExp`. - Looping in Dart over every habit and issuing one query per habit, an N+1 pattern the join exists to avoid. - Forgetting that the SQL semantics of each join type, such as which rows an inner join drops, are SQLite's; Drift only builds the statement and maps the result.

  • In a Drift join, when would you pass useColumns: false to leftOuterJoin?
    When the joined table is there only for filtering or aggregation, such as counting check-ins per habit. Its columns are then left out of the SELECT list, which keeps rows smaller, and you read the aggregate with `row.read(expr)` instead of `readTableOrNull`.
  • In Drift, how do you join the same table twice, for example a start and an end location?
    Create aliases with `alias(locations, 'start')` and `alias(locations, 'dest')`, use each alias in its own join and `ON` expression, and read each side with `readTable` or `readTableOrNull` on the alias rather than the base table.

saying these in an interview costs you the question

  • A Drift join's get() returns the primary table's row class.
  • readTable returns null when the joined table has no row.
  • Every Drift join needs its own generated result class before it runs.
  • A watched join re-runs only when the primary table changes.
  • You fetch related rows with one query per parent instead of a join.