Flutter中Sqlflite嵌套模型的使用方法咨询

Hey there! Let's figure out how to work with your nested UserModel and CourseModel structure using sqflite. Since SQLite is a relational database, we'll need to split your data into two normalized tables (one for users, one for courses) and link them with a foreign key. Let's break this down step by step.

Step 1: Fix Your Model Classes #

First, let's tweak your existing models to make them compatible with database operations, and fix a few syntax issues:

class CourseModel {
  String coursename;

  CourseModel({required this.coursename});

  // Convert CourseModel to Map for database insertion
  Map<String, dynamic> toMap() {
    return {
      'coursename': coursename,
    };
  }

  // Create CourseModel from Map (for query results)
  static CourseModel fromMap(Map<String, dynamic> map) {
    return CourseModel(
      coursename: map['coursename'],
    );
  }
}

class UserModel {
  int? id; // Let SQLite auto-generate the id if not provided
  String name;
  List<CourseModel> courses;

  UserModel({this.id, required this.name, required this.courses});

  // Convert UserModel to Map (exclude courses, we'll handle them separately)
  Map<String, dynamic> toMap() {
    return {
      'id': id,
      'name': name,
    };
  }

  // Create UserModel from Map (courses will be added later via query)
  static UserModel fromMap(Map<String, dynamic> map) {
    return UserModel(
      id: map['id'],
      name: map['name'],
      courses: [], // Initialize empty, we'll populate this later
    );
  }

  // Add a course (fixed duplicate check)
  void addCourse(CourseModel course) {
    // Check if the course already exists by name
    bool isDuplicate = courses.any((c) => c.coursename == course.coursename);
    if (!isDuplicate) {
      courses.add(course);
    }
  }
}

Step 2: Database Table Design #

We'll create two tables:

• users: Stores user details (id as primary key)
• courses: Stores course details, with a user_id foreign key linking to the user's id

Here's how to define them in your DatabaseHelper:

import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';

class DatabaseHelper {
  static final DatabaseHelper instance = DatabaseHelper._privateConstructor();
  static Database? _database;

  DatabaseHelper._privateConstructor();

  // Get database instance
  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await _initDatabase();
    return _database!;
  }

  // Initialize database
  Future<Database> _initDatabase() async {
    String path = join(await getDatabasesPath(), 'user_courses.db');
    return await openDatabase(
      path,
      version: 1,
      onCreate: (db, version) async {
        // Create users table
        await db.execute('''
          CREATE TABLE users(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL
          )
        ''');
        // Create courses table with foreign key to users.id
        await db.execute('''
          CREATE TABLE courses(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            coursename TEXT NOT NULL,
            user_id INTEGER NOT NULL,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
          )
        ''');
      },
    );
  }
}

The ON DELETE CASCADE ensures that if a user is deleted, all their linked courses are also deleted automatically.

Step 3: Implement CRUD Operations #

Now let's add methods to insert, query, update, and delete users with their courses.

Insert a User with Courses #

// Inside DatabaseHelper class
Future<int> insertUser(UserModel user) async {
  Database db = await instance.database;
  // Insert the user first to get their auto-generated id
  int userId = await db.insert('users', user.toMap());
  // Insert each course linked to this user
  for (var course in user.courses) {
    Map<String, dynamic> courseMap = course.toMap();
    courseMap['user_id'] = userId; // Add foreign key
    await db.insert('courses', courseMap);
  }
  return userId;
}

Query a User with Their Courses #

Future<UserModel?> getUserById(int userId) async {
  Database db = await instance.database;
  // Get user data
  List<Map<String, dynamic>> userMaps = await db.query(
    'users',
    where: 'id = ?',
    whereArgs: [userId],
  );
  if (userMaps.isEmpty) return null;
  
  UserModel user = UserModel.fromMap(userMaps.first);
  
  // Get all courses linked to this user
  List<Map<String, dynamic>> courseMaps = await db.query(
    'courses',
    where: 'user_id = ?',
    whereArgs: [userId],
  );
  
  // Convert course maps to CourseModel and add to user
  user.courses = courseMaps.map((map) => CourseModel.fromMap(map)).toList();
  return user;
}

Update a User and Their Courses #

Future<int> updateUser(UserModel user) async {
  Database db = await instance.database;
  int userId = user.id!;
  
  // Update user details
  int userUpdateCount = await db.update(
    'users',
    user.toMap(),
    where: 'id = ?',
    whereArgs: [userId],
  );
  
  // First delete existing courses for this user
  await db.delete(
    'courses',
    where: 'user_id = ?',
    whereArgs: [userId],
  );
  
  // Insert updated courses
  for (var course in user.courses) {
    Map<String, dynamic> courseMap = course.toMap();
    courseMap['user_id'] = userId;
    await db.insert('courses', courseMap);
  }
  
  return userUpdateCount;
}

Delete a User #

Future<int> deleteUser(int userId) async {
  Database db = await instance.database;
  // The ON DELETE CASCADE will handle deleting linked courses
  return await db.delete(
    'users',
    where: 'id = ?',
    whereArgs: [userId],
  );
}

Step 4: Example Usage #

Here's how you'd use these methods in your app:

// Create a user with courses
UserModel newUser = UserModel(
  name: 'abc',
  courses: [
    CourseModel(coursename: 'some_coursename'),
    CourseModel(coursename: 'some_other_coursename'),
  ],
);

// Insert user into database
int userId = await DatabaseHelper.instance.insertUser(newUser);

// Get the user back with courses
UserModel? fetchedUser = await DatabaseHelper.instance.getUserById(userId);
print(fetchedUser?.name); // Output: abc
print(fetchedUser?.courses.length); // Output: 2

// Add a new course and update the user
fetchedUser?.addCourse(CourseModel(coursename: 'new_course'));
if (fetchedUser != null) {
  await DatabaseHelper.instance.updateUser(fetchedUser);
}

Key Notes #

• Normalization: Splitting data into separate tables avoids data duplication and makes updates easier.
• Foreign Keys: The user_id in the courses table ensures that each course is linked to exactly one user.
• Model Conversion: Always convert between your model classes and Map objects when interacting with sqflite, since sqflite only works with maps.

内容的提问来源于stack exchange，提问作者Alok