Flutter中的多對多關系

我正在嘗試在 Flutter 中創建一個應用程式來幫助我組織我的學校生活。所以，我想做的是有很多課程和很多學期。一個學期必須至少有一個課程，一個課程可以屬于多個學期。我希望我假設我需要 m:m 關系是正確的。

為課程和學期創建了兩個資料類后，我應該如何處理我在 SQLite 中提到的兩個類之間的關系？到目前為止，我的代碼是這樣的：

課程.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,
    );
  }
}

學期.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"],
    );
  }
}

此外，我已將資料庫初始化為：

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,
);

uj5u.com熱心網友回復：

如果我理解正確，您可以同時宣告 a List<String> courseIdsinSemester和 a List<int> semesterIdsin Course。

uj5u.com熱心網友回復：

我為 Flutter 使用了 Moor，我能夠實作我想要的。我只是按照檔案頁面上的教程，代碼如下：

1. 我創建了一個新表SemesterEntries和一個新的資料型別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. 我可以使用以下函式查詢和插入資料

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));
    }
  });
}

雖然我不完全明白發生了什么，但上述解決方案給了我我想要的。

標籤：扑 sqlite

上一篇：應用程式從Android中已洗掉的資料庫檔案中提供資料

下一篇：Android-使用Room從SQLite獲取單個物件