Your app's database will probably have more than one table. You want to store tasks together with their tags, or maybe recipes and their authors. No matter what you store, it's usually the case that some of the tables are quite interconnected and it would be perfect to get just the right data from both tables at the same time. That's what SQL joins are for. To make joins safe, it's a good idea to use foreign keys. Otherwise you might end up trying to get a tag or a recipe which doesn't exist... and that's not all that good.

Finally, adding new columns or whole tables requires you to provide a migration to let the previous versions of the database upgrade themselves.

We're building a task list application in this series, so we will add tags to the mix. They will be stored inside a separate table. Every task can have a tag and to get the tasks together with tags, we will utilize foreign keys and table joins. The user interface will also change a bit (I know, it's not glamorous ).

With the updated UI comes the need for a new package - a material color picker. Version of the Moor package has also gotten updated since the last part, so let's use the latest one for good measure.

pubspec.yaml dependencies: flutter: sdk: flutter moor_flutter: ^ 1.6 .0 # For the UI provider: ^ 3.0 .0 + 1 flutter_slidable: ^ 0.5 .3 flutter_material_color_picker: ^ 1.0 .0 ... dev_dependencies: flutter_test: sdk: flutter moor_generator: ^ 1.6 .0 build_runner:

Similar to how we have a table definition for Tasks, we will have one for Tags. A tag will have a name and a color represented as an integer. Name will be set as the primary key of the tag which, among other things, will make tag names unique.

moor_database.dart class Tags extends Table { TextColumn get name => text().withLength(min: 1 , max: 10 )(); IntColumn get color => integer()(); Set <Column> get primaryKey => {name}; }

Of course, you can add the classic auto-incrementing id column to the tags table. We're using the name as the key for reducing the amount of lines of code in this tutorial.

Now is a good time to kick off the build process as we will make a lot of changes to this file down the line.

flutter packages pub run build_runner watch

Having a table, it will be beneficial if we can add and access its contents. We will create a TagDao to keep the queries separate from the TaskDao. There will be only two queries - watchTags and insertTag.

moor_database.dart (tables: [Tags]) class TagDao extends DatabaseAccessor < AppDatabase > with _ $ TagDaoMixin { final AppDatabase db; TagDao( this .db) : super (db); Stream< List <Tag>> watchTags() => select(tags).watch(); Future insertTag(Insertable<Tag> tag) => into(tags).insert(tag); }

Finally, update the @UseMoor annotation on the AppDatabase with the new table and DAO.

(tables: [Tasks, Tags], daos: [TaskDao, TagDao])

Individual tasks will use the name of a tag to reference it. Also, a task doesn't necessarily have to have a tag, in which case it will simply reference null. Joining tasks together with their tags will be done by looking up the referenced tag name in the tags table. Of course, we won't write this lookup code ourselves, we will let SQL and Moor do their job. Let's add a new column to the Tasks table definition.

moor_database.dart class Tasks extends Table { IntColumn get id => integer().autoIncrement()(); TextColumn get tagName => text().nullable()(); TextColumn get name => text().withLength(min: 1 , max: 50 )(); DateTimeColumn get dueDate => dateTime().nullable()(); BoolColumn get completed => boolean().withDefault(Constant( false ))(); }

Do you see a problem in the code above? The tagName column can contain values (apart from null) which don't reference any tag at all. How should then the join statement know what to join if the task's tag doesn't exist?

Foreign key

To add some security to the tasks table's data, we will use a foreign key constraint on the tagName column. This way, when we try to insert a new task and there isn't already a tag with the specified name in the tags table, an exception will be thrown and the whole join disaster will be halted before it even started. Let's apply a customConstraint on the tagName property to make sure it references values present in the tags table's name column.

TextColumn get tagName => text().nullable().customConstraint( 'NULL REFERENCES tags(name)' )();

Why does the tagName column have NULL constraints applied twice - once in fluent syntax and another time in the customConstraint?

It's because custom constraints disable the fluent ones in the actual DB, but we still want the generated Tag data class to have the tagName field be not required.

Migrations

At this point, before we happily join tasks with tags and run the app, we have to think about letting the already existing database version know about the changed schema. Sure, we could just create a brand new database, just as it's done automatically on the first launch of the app, but that would wipe all of the stored data, and we definitely don't want that to happen.

Instead, we will provide a migration strategy for going from version 1 of the database without tags to version 2 with tags. Together with this, we will actually bump up the schemaVersion property. This will all happen inside the database class.

moor_database.dart (tables: [Tasks, Tags], daos: [TaskDao, TagDao]) class AppDatabase extends _ $ AppDatabase { AppDatabase() : super ((FlutterQueryExecutor.inDatabaseFolder( path: 'db.sqlite' , logStatements: true , ))); int get schemaVersion => 2 ; MigrationStrategy get migration => MigrationStrategy( onUpgrade: (migrator, from, to) async { if (from == 1 ) { await migrator.addColumn(tasks, tasks.tagName); await migrator.createTable(tags); } }, ); }

Enabling foreign keys

This migration property ties in nicely with the foreign key we've added previously. It turns out that foreign keys are actually not enabled by default in SQLite - we have to enable them ourselves with a custom statement. We want to run this statement before any other queries are run to prevent the chance of "unchecked data" from entering the database. This is a perfect use-case for the beforeOpen callback.

moor_database.dart MigrationStrategy get migration => MigrationStrategy( ... beforeOpen: (db, details) async { await db.customStatement( 'PRAGMA foreign_keys = ON' ); }, );

Joining the tables

With all of the very necessary setup in place, we can finally jump onto the icing on the cake - the actual table join. First, we'll create a simple class to group a task with its tag, hence the name TaskWithTag.

moor_database.dart class TaskWithTag { final Task task; final Tag tag; TaskWithTag({ this .task, this .tag, }); }

The join itself will happen inside the TaskDao, so we have to update it to also access the tags table, and we'll also remove all of the code from the previous part which would now just clutter up the class (you can always get the old code from the link or from the git repository). The method watchAllTasks will no longer return a simple List<Task>, but a List<TaskWithTag> instead. Previously, we only "selected, ordered and watched". Now, we will "select, order, join and watch". Watching on a join statement gets us a List<TypedResult>, so we'll have to manually map it to our desired List<TaskWithTag>.

moor_database.dart ( tables: [Tasks, Tags], ) class TaskDao extends DatabaseAccessor < AppDatabase > with _ $ TaskDaoMixin { final AppDatabase db; TaskDao( this .db) : super (db); Stream< List <TaskWithTag>> watchAllTasks() { return (select(tasks) ..orderBy( ([ (t) => OrderingTerm(expression: t.dueDate, mode: OrderingMode.asc), (t) => OrderingTerm(expression: t.name), ]), )) .join( [ leftOuterJoin(tags, tags.name.equalsExp(tasks.tagName)), ], ) .watch() .map( (rows) => rows.map( (row) { return TaskWithTag( task: row.readTable(tasks), tag: row.readTable(tags), ); }, ).toList(), ); } Future insertTask(Insertable<Task> task) => into(tasks).insert(task); Future updateTask(Insertable<Task> task) => update(tasks).replace(task); Future deleteTask(Insertable<Task> task) => delete(tasks).delete(task); }

leftOuterJoin for this particular situation. Moor also supports an innerJoin and crossJoin. Learn more about joins and the differences between them We're using afor this particular situation. Moor also supports anand. Learn more about joins and the differences between them on Wikipedia

Updating the UI

The user interface will be changed to work with tags. Since we've removed the "completed tasks only" queries from the TaskDao to keep the code simpler, we'll also remove the UI for this functionality. As usual, I won't really explain the UI-coding part in much detail. If making UIs (which look much better than this one ) is something you're interested in, let me know! First, we will add a Provider for the TagDao using a MultiProvider widget inside main.dart.

main.dart import 'package:flutter/material.dart' ; import 'package:provider/provider.dart' ; import 'data/moor_database.dart' ; import 'ui/home_page.dart' ; void main() => runApp(MyApp()); class MyApp extends StatelessWidget { Widget build(BuildContext context) { final db = AppDatabase(); return MultiProvider( providers: [ Provider(builder: (_) => db.taskDao), Provider(builder: (_) => db.tagDao), ], child: MaterialApp( title: 'Material App' , home: HomePage(), ), ); } }

We'll add a NewTagInputWidget which will very much copy the NewTaskInputWidget. This is where we'll use the MaterialColorPicker inside a popup AlertDialog. New tags will be added to the database using the TagDao from the onSubmitted callback on a TextField.

new_tag_input_widget.dart import 'package:flutter/material.dart' ; import 'package:flutter_material_color_picker/flutter_material_color_picker.dart' ; import 'package:moor/moor.dart' ; import 'package:provider/provider.dart' ; import '../../data/moor_database.dart' ; class NewTagInput extends StatefulWidget { const NewTagInput({ Key key, }) : super (key: key); _NewTagInputState createState() => _NewTagInputState(); } class _NewTagInputState extends State < NewTagInput > { static const Color DEFAULT_COLOR = Colors.red; Color pickedTagColor = DEFAULT_COLOR; TextEditingController controller; void initState() { super .initState(); controller = TextEditingController(); } Widget build(BuildContext context) { return Container( padding: const EdgeInsets.all( 8.0 ), child: Row( children: <Widget>[ _buildTextField(context), _buildColorPickerButton(context), ], ), ); } Flexible _buildTextField(BuildContext context) { return Flexible( flex: 1 , child: TextField( controller: controller, decoration: InputDecoration(hintText: 'Tag Name' ), onSubmitted: (inputName) { final dao = Provider.of<TagDao>(context); final task = TagsCompanion( name: Value(inputName), color: Value(pickedTagColor.value), ); dao.insertTag(task); resetValuesAfterSubmit(); }, ), ); } Widget _buildColorPickerButton(BuildContext context) { return Flexible( flex: 1 , child: GestureDetector( child: Container( width: 25 , height: 25 , decoration: BoxDecoration( shape: BoxShape.circle, color: pickedTagColor, ), ), onTap: () { _showColorPickerDialog(context); }, ), ); } Future _showColorPickerDialog(BuildContext context) { return showDialog( context: context, builder: (context) { return AlertDialog( content: MaterialColorPicker( allowShades: false , selectedColor: DEFAULT_COLOR, onMainColorChange: (colorSwatch) { setState(() { pickedTagColor = colorSwatch; }); Navigator.of(context).pop(); }, ), ); }, ); } void resetValuesAfterSubmit() { setState(() { pickedTagColor = DEFAULT_COLOR; controller.clear(); }); } }

The NewTaskInputWidget will also change a bit. It will now have a "tag selector" implemented as a DropdownButton. Items in this DropdownButton will be populated by watching tags on the TagDao.

new_task_input_widget.dart import 'package:flutter/material.dart' ; import 'package:moor/moor.dart' ; import 'package:provider/provider.dart' ; import '../../data/moor_database.dart' ; class NewTaskInput extends StatefulWidget { const NewTaskInput({ Key key, }) : super (key: key); _NewTaskInputState createState() => _NewTaskInputState(); } class _NewTaskInputState extends State < NewTaskInput > { DateTime newTaskDate; Tag selectedTag; TextEditingController controller; void initState() { super .initState(); controller = TextEditingController(); } Widget build(BuildContext context) { return Container( padding: const EdgeInsets.all( 8.0 ), child: Row( mainAxisAlignment: MainAxisAlignment.spaceBetween, children: <Widget>[ _buildTextField(context), _buildTagSelector(context), _buildDateButton(context), ], ), ); } Expanded _buildTextField(BuildContext context) { return Expanded( flex: 1 , child: TextField( controller: controller, decoration: InputDecoration(hintText: 'Task Name' ), onSubmitted: (inputName) { final dao = Provider.of<TaskDao>(context); final task = TasksCompanion( name: Value(inputName), dueDate: Value(newTaskDate), tagName: Value(selectedTag?.name), ); dao.insertTask(task); resetValuesAfterSubmit(); }, ), ); } StreamBuilder< List <Tag>> _buildTagSelector(BuildContext context) { return StreamBuilder< List <Tag>>( stream: Provider.of<TagDao>(context).watchTags(), builder: (context, snapshot) { final tags = snapshot.data ?? List (); DropdownMenuItem<Tag> dropdownFromTag(Tag tag) { return DropdownMenuItem( value: tag, child: Row( children: <Widget>[ Text(tag.name), SizedBox(width: 5 ), Container( width: 15 , height: 15 , decoration: BoxDecoration( shape: BoxShape.circle, color: Color(tag.color), ), ), ], ), ); } final dropdownMenuItems = tags.map((tag) => dropdownFromTag(tag)).toList() ..insert( 0 , DropdownMenuItem( value: null , child: Text( 'No Tag' ), ), ); return Expanded( child: DropdownButton( onChanged: (Tag tag) { setState(() { selectedTag = tag; }); }, isExpanded: true , value: selectedTag, items: dropdownMenuItems, ), ); }, ); } IconButton _buildDateButton(BuildContext context) { return IconButton( icon: Icon(Icons.calendar_today), onPressed: () async { newTaskDate = await showDatePicker( context: context, initialDate: DateTime .now(), firstDate: DateTime ( 2010 ), lastDate: DateTime ( 2050 ), ); }, ); } void resetValuesAfterSubmit() { setState(() { newTaskDate = null ; selectedTag = null ; controller.clear(); }); } }

Finally, we'll also change HomePage to display the NewTagInputWidget and also to show the tag of a task in the ListView.

home_page.dart import 'package:floor_introduction_prep/ui/widget/new_tag_input_widget.dart' ; import 'package:flutter/material.dart' ; import 'package:provider/provider.dart' ; import 'package:flutter_slidable/flutter_slidable.dart' ; import '../data/moor_database.dart' ; import 'widget/new_task_input_widget.dart' ; class HomePage extends StatefulWidget { _HomePageState createState() => _HomePageState(); } class _HomePageState extends State < HomePage > { Widget build(BuildContext context) { return Scaffold( appBar: AppBar( title: Text( 'Tasks' ), ), body: Column( children: <Widget>[ Expanded(child: _buildTaskList(context)), NewTaskInput(), NewTagInput(), ], )); } StreamBuilder< List <TaskWithTag>> _buildTaskList(BuildContext context) { final dao = Provider.of<TaskDao>(context); return StreamBuilder( stream: dao.watchAllTasks(), builder: (context, AsyncSnapshot< List <TaskWithTag>> snapshot) { final tasks = snapshot.data ?? List (); return ListView.builder( itemCount: tasks.length, itemBuilder: (_, index) { final item = tasks[index]; return _buildListItem(item, dao); }, ); }, ); } Widget _buildListItem(TaskWithTag item, TaskDao dao) { return Slidable( actionPane: SlidableDrawerActionPane(), secondaryActions: <Widget>[ IconSlideAction( caption: 'Delete' , color: Colors.red, icon: Icons.delete, onTap: () => dao.deleteTask(item.task), ) ], child: CheckboxListTile( title: Text(item.task.name), subtitle: Text(item.task.dueDate?.toString() ?? 'No date' ), secondary: _buildTag(item.tag), value: item.task.completed, onChanged: (newValue) { dao.updateTask(item.task.copyWith(completed: newValue)); }, ), ); } Column _buildTag(Tag tag) { return Column( mainAxisAlignment: MainAxisAlignment.center, crossAxisAlignment: CrossAxisAlignment.start, children: <Widget>[ if (tag != null ) ...[ Container( width: 10 , height: 10 , decoration: BoxDecoration( shape: BoxShape.circle, color: Color(tag.color), ), ), Text( tag.name, style: TextStyle( color: Colors.black.withOpacity( 0.5 ), ), ), ], ], ); } }

Conclusion