Flutter Moor Database: Joining queries proper structure

Question

I have just started to use Moor Database for Flutter. I am going to join my two tables to get some columns from both tables.

I have checked the example that is given in docs as follow:

// we define a data class to contain both a todo entry and the associated category
class EntryWithCategory {
  EntryWithCategory(this.entry, this.category);

  final TodoEntry entry;
  final Category category;
}

// in the database class, we can then load the category for each entry
Stream<List<EntryWithCategory>> entriesWithCategory() {
  final query = select(todos).join([
    leftOuterJoin(categories, categories.id.equalsExp(todos.category)),
  ]);

  // see next section on how to parse the result
}

I am not able to understand that where to put this class. If I am creating a new class then it's giving me an error that the select keyword is not found. Also tried to import related to moor but not working.

Where I can write join queries and make this class?

score 0 · Answer 1 · answered Aug 01 '21 at 02:58

Moor basically says to get the results and build the class manually. That class has no relationship with the database, so you can put it wherever you want. It is just the suggested way of doing it.

So, the select statement returns an object that you can iterate over the resulting rows, as an SQL response. And with that results build the classes that will be returned.

Look at the next example of the docs:

return query.watch().map((rows) {
  return rows.map((row) {
    return EntryWithCategory(
      row.readTable(todos),
      row.readTableOrNull(categories),
    );
  }).toList();
});

Because is a stream, calls watch(),and then map().

This first map returns the result of the query, properly name rows, every time one of the rows changes in the database, it will emit all the rows again.

The second map inside the first is for turning every row into a EntryWithCategory object. That way the whole function returns a list of those object updated with every change.

You are right maybe. My question is different. I have 30+ classes (tables) in my database. I have to manage many joining queries for all of them. I want to maintain the structure for that but If I am creating any Class for Join, it's not having reference of database keywords like `select` n all. What I have to do If I want to maintain separately rather than in once CLASS of DATABASE? — Pratik Butani, Aug 02 '21 at 05:35

score 0 · Answer 2 · answered Aug 15 '22 at 06:28

You can create other model for several tables. Try this variant.

import 'package:drift/drift.dart';

part 'car_dao.g.dart';

@DriftAccessor(tables: [Cars, Bikes])
class CarDao extends DatabaseAccessor<AppDatabase> with _$CarDaoMixin {
  final AppDatabase db;

  CarDao(this.db) : super(db);


  Future<List<CarWithBikeModel>> getCarsWithBikes() async {
    final carList = await (select(cars).get();

    final bikeList = await (select(bikes).get();

    return  CarWithBikeModel(
          cars: carList,
          bikes: bikeList);
         
    
  }



}

Flutter Moor Database: Joining queries proper structure

2 Answers2

Linked