I' m trying to create an app in Flutter to help me organize my school life. So, what I want to do is have many Courses and many Semesters. One semester must have at least on Course and one Course can belong to many Semesters. I hope I'm right to assume I need m:m relation.

Having created the two data classes for Courses and Semesters how should I proceed in order to have the relation between the two classes I mentioned in SQLite? My code so far is this:

course.dart

class Course {
  final String courseID;
  final String courseName;
  final String courseTeacher;
  final String courseEclass;
  final double courseCredits;
  final bool isRequired;

  Course({
    required this.courseID,
    required this.courseName,
    required this.courseTeacher,
    required this.courseEclass,
    required this.courseCredits,
    required this.isRequired,
  });

  Map<String, dynamic> toMap() {
    return {
      "course_id": courseID,
      "course_name": courseName,
      "course_teacher": courseTeacher,
      "course_eclass": courseEclass,
      "course_credits": courseCredits,
      "is_required": isRequired,
    };
  }

  factory Course.fromMap(Map<String, dynamic> map) {
    return Course(
      courseID: map["course_id"],
      courseName: map["course_name"],
      courseTeacher: map["course_teacher"],
      courseEclass: map["course_eclass"],
      courseCredits: map["course_credits"],
      isRequired: map["is_required"] == 1 ? true : false,
    );
  }
}

semester.dart

class Semester {
  final int semesterID;
  final String semesterName;
  final DateTime semesterStartDate;
  final DateTime semesterEndDate;
  final List<Course> semesterCourses;
  final double semesterMaxCredits;

  Semester({
    required this.semesterID,
    required this.semesterName,
    required this.semesterStartDate,
    required this.semesterEndDate,
    required this.semesterCourses,
    required this.semesterMaxCredits,
  });

  Map<String, dynamic> toMap() {
    return {
      "semester_id": semesterID,
      "semester_name": semesterName,
      "semester_start_date": semesterStartDate.millisecondsSinceEpoch,
      "semester_end_date": semesterEndDate.millisecondsSinceEpoch,
      "semester_courses_ids": semesterCourses.map((x) => x.courseID).join(","),
      "semester_max_credits": semesterMaxCredits,
    };
  }

  factory Semester.fromMap(Map<String, dynamic> map) {
    return Semester(
      semesterID: map["semester_id"],
      semesterName: map["semester_nam"],
      semesterStartDate: DateTime.fromMillisecondsSinceEpoch(map["semester_start_date"]),
      semesterEndDate: DateTime.fromMillisecondsSinceEpoch(map["semester_end_date"]),
      semesterCourses: List<Course>.from(map['semesterCourses']?.map((x) => Course.fromMap(x))),
      semesterMaxCredits: map["semester_max_credits"],
    );
  }
}

Also I have initialized the database as:

WidgetsFlutterBinding.ensureInitialized();
var dbPath = join(await getDatabasesPath(), "university_manager.db");

var database = await openDatabase(
    dbPath,
    onCreate: (db, version) {
      db.execute("""
        CREATE TABLE courses(course_id TEXT PRIMARY KEY,
          course_name TEXT,
          course_teacher TEXT,
          course_eclass TEXT,
          course_credits INTEGER,
          is_required INTEGER)
        """);
      return db.execute("""
        CREATE TABLE semesters(
          semester_id INTEGER PRIMARY KEY AUTOINCREMENT,
          semester_name TEXT,
          semester_start_date INTEGER,
          semester_end_date INTEGER,
          semester_courses_ids TEXT,
          semester_max_credits INTEGER,
          FOREIGN KEY (semester_courses_ids) REFERENCES courses (course_id)
            ON DELETE NO ACTION ON UPDATE NO ACTION)
        """);
    },
    version: 1,
);

CodePudding user response：

If I understand this correctly, you can declare both a List<String> courseIds in Semester as well as a List<int> semesterIds in Course.

CodePudding user response：

I used Moor for Flutter and I was able to achieve what I wanted. I just followed the tutorial on the documentation page and the code is as follows:

1. I created a new table SemesterEntries and a new data type SemesterWithCourses

class SemesterEntries extends Table {
  IntColumn get semester => integer()();
  TextColumn get course => text()();
}

class SemesterWithCourses {
  final Semester semester;
  final List<Course> courses;

  SemesterWithCourses(this.semester, this.courses);
}

2. I can query and insert data using the following functions

Stream<List<SemesterWithCourses>> watchAllSemesters() {
  final semesterStream = select(semesters).watch();
  return semesterStream.switchMap((semesters) {
    final idToSemester = {
      for (var semester in semesters) semester.id: semester
    };
    final ids = idToSemester.keys;

    final entryQuery = select(semesterEntries).join([
      innerJoin(courses, courses.courseID.equalsExp(semesterEntries.course))
    ])
      ..where(semesterEntries.semester.isIn(ids));

    return entryQuery.watch().map((rows) {
      final idToCourses = <int, List<Course>>{};

      for (var row in rows) {
        final course = row.readTable(courses);
        final id = row.readTable(semesterEntries).semester;

        idToCourses.putIfAbsent(id, () => []).add(course);
      }

      return [
        for (var id in ids)
          SemesterWithCourses(idToSemester[id]!, idToCourses[id] ?? [])
      ];
    });
  });
}

Future<void> insertSemester(SemesterWithCourses entry) async {
  return transaction(() async {
    final semester = entry.semester;
    await into(semesters).insert(semester, mode: InsertMode.replace);
    await (delete(semesterEntries)
          ..where((entry) => entry.semester.equals(semester.id)))
        .go();

    for (final course in entry.courses) {
      await into(semesterEntries).insert(
          SemesterEntrie(semester: semester.id, course: course.courseID));
    }
  });
}

While I don't fully understand what happened the above solution gives me what I want.