How to make use of the user defined View to pipe data into application with flutter/drift(moor)

Question

I declared some Views following the example in the drift documentation besides database table definitions and managed to go through the generation process. After then I was quite confused as to the usage of the abstract View classes, which I could neither instantiate to make query nor incorporate it into query definitions(get or watch).

abstract class TestingRemoteSignalView extends View {
  TestingRemoteSignal get testingRemoteSignal;
  Bay get bay;
  RemoteSignal get remoteSignal;
  Expression<String> get description => bay.name + remoteSignal.signalName;

  @override
  Query as() {
    return select([
      testingRemoteSignal.id,
      bay.name,
      description,
      testingRemoteSignal.passed,
      testingRemoteSignal.skipped,
      testingRemoteSignal.touched,
      testingRemoteSignal.memo,
    ]).from(testingRemoteSignal).join([
      innerJoin(bay, testingRemoteSignal.bay.equalsExp(bay.id)),
      innerJoin(
          remoteSignal, testingRemoteSignal.signal.equalsExp(remoteSignal.id))
    ]);
  }
}

What is the use of these View classes and how to make queries out of them? Maybe something like:

final query = select(TestingRemoteSignalView)..where((t) => t.passed.isEquals(true));
query.watch();

Answer 1

This page in the Drift documentation has a section on views here. I found this video on Youtube quite helpful as well.

Once you have created your view code as you have in your question, you need to add the view to database definition as follows (from the documentation):

@DriftDatabase(tables: [Todos, Categories], views: [CategoryTodoCount])
class MyDatabase extends _$MyDatabase { 

Then you need to delete and rebuild your '.g.dart' file.

You should then be able to access the view in a query like any other table. Here is an example from my own code:

View definition

abstract class FrequencyView extends View {
  Frequency get frequency;
  Instrument get instrument;
  Range get range;

  @override
  Query as() => select([
        frequency.id,
        frequency.type,
        frequency.start,
        frequency.end,
        instrument.iname,
        range.rname
      ]).from(frequency).join([
        innerJoin(instrument, instrument.id.equalsExp(frequency.instrumentID))
      ]).join([innerJoin(range, range.id.equalsExp(frequency.rangeID))]);
}

// FrequencyView declared in DraftDatabase declaration below...
@DriftDatabase(tables: [Frequency, Range, Instrument], views: [FrequencyView])
class MyDatabase extends _$MyDatabase {
  // we tell the database where to store the data with this constructor
  MyDatabase() : super(_openConnection());

  // you should bump this number whenever you change or add a table definition.
  // Migrations are covered later in the documentation.
  @override
  int get schemaVersion => 1;
}

As an aside, I had fields with the same name in different tables ('name'), which caused errors in the .g.dart file once I had rebuilt it. I wasn't sure if you can alias column names in a view so I just gave them all unique names.

View select query

import 'package:drift/drift.dart' as drift;

Future<List<FrequencyViewData>> _getFrequencyPlus(MyDatabase db) async {
  List<FrequencyViewData> values = await (db.select(db.frequencyView)
        ..orderBy([(t) => drift.OrderingTerm(expression: t.start)]))
      .get();

  return values;
}

This query returns a full set of data. I assume you can add "where" clauses to this just like a standard select clause on a single table to get partial returns.