英文:
Flutter Sqflite - how to add conditions to present specific data into a listview
问题
我不确定如何处理它
我明白我可以使用
"select * from" OR .where() 函数，但我不知道如何实现它。
我在我的数据库表中有第三列，我打算用它来过滤呈现的内容
对于围绕此问题的任何答案都将不胜感激
任何帮助都会感激，干杯
再次编辑
这是我的完成的数据库助手。有时当我将数据类型相等时会遇到问题。
import 'package:progressiverecording/components/measurements/measurement_model.dart';
import 'package:progressiverecording/pages/recording%20measurements/measurement_main.dart';
import 'package:sqflite/sqflite.dart' as sql;
import 'package:flutter/foundation.dart';
class MeasurementsDBHelper {
static const _dbName = "measurement.db";
static const _dbVersion = 1;
static const tableName = "measurement_table";
static const String columnId = 'id';
static const String columnContent = 'content';
static const String columnBodyPart = 'bodyPart';
static Future<void> createTables(sql.Database database) async {
database.execute('''
CREATE TABLE measurement_table (
"$columnId" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"$columnContent" TEXT NOT NULL,
"$columnBodyPart" TEXT NOT NULL
)
''');
}
static Future<sql.Database> db() async {
return sql.openDatabase(
'measurement.db',
version: _dbVersion,
onCreate: (sql.Database database, int version) async {
print("------- 创建表格 -------");
await createTables(database);
},
);
}
static Future<int> insertMeasurement(String content, String bodyPart ) async {
final db = await MeasurementsDBHelper.db();
final data = {
'content' : content,
'bodyPart' : bodyPart,
};
final id = await db.insert(
'measurement_table',
data,
conflictAlgorithm: sql.ConflictAlgorithm.replace
);
return id;
}
// 获取所有项目
static Future<List<Map<String, dynamic>>> getMeasurements() async {
final db = await MeasurementsDBHelper.db();
return db.query(
'measurement_table',
orderBy: "id",
);
}
// 获取单个项目
static Future<List<Map<String, dynamic>>> getMeasurement(int id) async {
final db = await MeasurementsDBHelper.db();
return db.query(
'measurement_table',
where: "id = ?",
whereArgs: [id],
limit: 1,
);
}
static Future<int> updateMeasurement( int id, String content, String bodyPart) async {
final db = await MeasurementsDBHelper.db();
final data = {
'content' : content,
'bodyPart' : bodyPart,
};
final result = await db.update(
'measurement_table',
data,
where: "id = ?",
whereArgs: [id],
);
return result;
}
static Future<void> deleteMeasurement(int id) async {
final db = await MeasurementsDBHelper.db();
try {
await db.delete(
"measurement_table",
where: "id = ?",
whereArgs: [id],
);
} catch (err) {
debugPrint("删除项目时出现问题: $err");
}
}
static Future<List<UserInputModel>> fetchDesiredData() async {
final db = await MeasurementsDBHelper.db();
final result = await db.rawQuery(
'''
SELECT * FROM measurement_table
WHERE "bodyPart" = $fromBodyPart;
''',
);
return result.map((map) => UserInputModel.fromMap(map)).toList();
}
}
这是我的ListView与FieldBox。当我告诉它打印过滤数据的新值时，我得到零。但当前的代码打印出数据库中的所有数据。
fetchFilteredData命令是等效的函数。
class MeasurementsDBHelper {
static const _dbName = "measurement.db";
static const _dbVersion = 1;
static const tableName = "measurement_table";
static const String columnId = 'id';
static const String columnContent = 'content';
static const String columnBodyPart = 'bodyPart';
static Future<void> createTables(sql.Database database) async {
database.execute('''
CREATE TABLE measurement_table (
"$columnId" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"$columnContent" TEXT NOT NULL,
"$columnBodyPart" TEXT NOT NULL
)
''');
}
static Future<sql.Database> db() async {
return sql.openDatabase(
'measurement.db',
version: _dbVersion,
onCreate: (sql.Database database, int version) async {
print("------- 创建表格 -------");
await createTables(database);
},
);
}
static Future<int> insertMeasurement(String content, String bodyPart ) async {
final db = await MeasurementsDBHelper.db();
final data = {
'content' : content,
'bodyPart' : bodyPart,
};
final id = await db.insert(
'measurement_table',
data,
conflictAlgorithm: sql.ConflictAlgorithm.replace
);
return id;
}
// 获取所有项目
static Future<List<Map<String, dynamic>>> getMeasurements() async {
final db = await MeasurementsDBHelper.db();
return db.query(
'measurement_table',
orderBy: "id",
);
}
// 获取单个项目
static Future<List<Map<String, dynamic>>> getMeasurement(int id) async {
final db = await MeasurementsDBHelper.db();
return db.query(
'measurement_table',
where: "id = ?",
whereArgs: [id],
limit: 1,
);
}
static Future<int> updateMeasurement( int id, String content, String bodyPart) async {
final db = await MeasurementsDBHelper.db();
final data = {
'content' : content,
'bodyPart' : bodyPart,
};
final result = await db.update(
'measurement_table',
data,
where: "id = ?",
whereArgs: [id],
);
return result;
}
static Future<void> deleteMeasurement(int id) async {
final db = await MeasurementsDBHelper.db();
try {
await db.delete(
"measurement_table",
where: "id = ?",
whereArgs: [id],
);
} catch (err) {
debugPrint("删除项目时出现问题: $err");
}
}
static Future<List<UserInputModel>> fetchFilteredData() async {
final db = await MeasurementsDBHelper.db();
final result = await db.rawQuery(
'''
SELECT * FROM measurement_table
WHERE "bodyPart" = $fromBodyPart;
''
<details>
<summary>英文:</summary>
I am not sure how to go about it
I understood that I can use
"select * from" OR .where() function but I do not know how to implement it.
I have a third column in my database table that I intend to use to filter what is presented
any answers revolving this is greatly appreciated
any help is appreciate it cheers
*EDITED AGAIN*
this is my completed database helper. i sometimes come across the issue that the data type wont work when i make it equal to each other.
import 'package:progressiverecording/components/measurements/measurement_model.dart';
import 'package:progressiverecording/pages/recording%20measurements/measurement_main.dart';
import 'package:sqflite/sqflite.dart' as sql;
import 'package:flutter/foundation.dart';
class MeasurementsDBHelper {
static const _dbName = "measurement.db";
static const _dbVersion = 1;
static const tableName = "measurement_table";
static const String columnId = 'id';
static const String columnContent = 'content';
static const String columnBodyPart = 'bodyPart';
static Future<void> createTables(sql.Database database) async {
database.execute('''
CREATE TABLE measurement_table (
"$columnId" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"$columnContent" TEXT NOT NULL,
"$columnBodyPart" TEXT NOT NULL
)
''');
}
static Future<sql.Database> db() async {
return sql.openDatabase(
'measurement.db',
version: _dbVersion,
onCreate: (sql.Database database, int version) async {
print("------- creating a table -------");
await createTables(database);
},
);
}
static Future<int> insertMeasurement(String content, String bodyPart ) async {
final db = await MeasurementsDBHelper.db();
final data = {
'content' : content,
'bodyPart' : bodyPart,
};
final id = await db.insert(
'measurement_table',
data,
conflictAlgorithm: sql.ConflictAlgorithm.replace
);
return id;
}
//getting all items
static Future<List<Map<String, dynamic>>> getMeasurements() async {
final db = await MeasurementsDBHelper.db();
return db.query(
'measurement_table',
orderBy: "id",
);
}
//getting individual item
static Future<List<Map<String, dynamic>>> getMeasurement(int id) async {
final db = await MeasurementsDBHelper.db();
return db.query(
'measurement_table',
where: "id = ?",
whereArgs: [id],
limit: 1,
);
}
static Future<int> updateMeasurement( int id, String content, String bodyPart) async {
final db = await MeasurementsDBHelper.db();
final data = {
'content' : content,
'bodyPart' : bodyPart,
};
final result = await db.update(
'measurement_table',
data,
where: "id = ?",
whereArgs: [id],
);
return result;
}
static Future<void> deleteMeasurement(int id) async {
final db = await MeasurementsDBHelper.db();
try {
await db.delete(
"measurement_table",
where: "id = ?",
whereArgs: [id],
);
} catch (err) {
debugPrint("Something went wrong when deleting an item: $err");
}
}
static Future<List<UserInputModel>> fetchDesiredData() async {
final db = await MeasurementsDBHelper.db();
final result =await db.rawQuery(
'''
SELECT * FROM measurement_table
WHERE "bodyPart" = $fromBodyPart;
''',
);
return result.map((map) => UserInputModel.fromMap(map)).toList();
}
}
this is my listview with fieldbox. when i tell it to print the new value of the filtered data i get zero. but the current code prints all the data in the database.
the fetchFilteredData command is the equivalent function.
class MeasurementsDBHelper {
static const _dbName = "measurement.db";
static const _dbVersion = 1;
static const tableName = "measurement_table";
static const String columnId = 'id';
static const String columnContent = 'content';
static const String columnBodyPart = 'bodyPart';
static Future<void> createTables(sql.Database database) async {
database.execute('''
CREATE TABLE measurement_table (
"$columnId" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"$columnContent" TEXT NOT NULL,
"$columnBodyPart" TEXT NOT NULL
)
''');
}
static Future<sql.Database> db() async {
return sql.openDatabase(
'measurement.db',
version: _dbVersion,
onCreate: (sql.Database database, int version) async {
print("------- creating a table -------");
await createTables(database);
},
);
}
static Future<int> insertMeasurement(String content, String bodyPart ) async {
final db = await MeasurementsDBHelper.db();
final data = {
'content' : content,
'bodyPart' : bodyPart,
};
final id = await db.insert(
'measurement_table',
data,
conflictAlgorithm: sql.ConflictAlgorithm.replace
);
return id;
}
//getting all items
static Future<List<Map<String, dynamic>>> getMeasurements() async {
final db = await MeasurementsDBHelper.db();
return db.query(
'measurement_table',
orderBy: "id",
);
}
//getting individual item
static Future<List<Map<String, dynamic>>> getMeasurement(int id) async {
final db = await MeasurementsDBHelper.db();
return db.query(
'measurement_table',
where: "id = ?",
whereArgs: [id],
limit: 1,
);
}
static Future<int> updateMeasurement( int id, String content, String bodyPart) async {
final db = await MeasurementsDBHelper.db();
final data = {
'content' : content,
'bodyPart' : bodyPart,
};
final result = await db.update(
'measurement_table',
data,
where: "id = ?",
whereArgs: [id],
);
return result;
}
static Future<void> deleteMeasurement(int id) async {
final db = await MeasurementsDBHelper.db();
try {
await db.delete(
"measurement_table",
where: "id = ?",
whereArgs: [id],
);
} catch (err) {
debugPrint("Something went wrong when deleting an item: $err");
}
}
static Future<List<UserInputModel>> fetchDesiredData() async {
final db = await MeasurementsDBHelper.db();
final result =await db.rawQuery(
'''
SELECT * FROM measurement_table
WHERE "bodyPart" = $fromBodyPart;
''',
);
return result.map((map) => UserInputModel.fromMap(map)).toList();
}
}
this is my model class.
class UserInputModel {
int? id;
String? content;
UserInputModel({
this.id,
required this.content,
});
Map<String, dynamic> toMap() {
var map = <String,dynamic> {};
map['id'] = id;
map['content'] = content;
return map;
}
factory UserInputModel.fromMap(Map<String, dynamic> map) => UserInputModel(
id: map['id'],
content: map['content']
);
}
simply put, my current code right now allows me to print all the user input into the listviews. i have multiple pages showing the same structure but i want each page to represent data that was pass strictly through it.
</details>
# 答案1
**得分**: 0
以下是您提供的代码的翻译部分：
```dart
[![console][1]][1]
创建模型类：
```dart
class Fields {
static const String id = '_id';
static const String content = 'content';
static const String bodyPart = 'bodyPart';
static const List<String> values = [id, content, bodyPart];
}
class UserModel {
int? id;
String content;
String bodyPart;
UserModel({
this.id,
required this.content,
required this.bodyPart,
});
Map<String, dynamic> toMap() {
return {
Fields.id: id,
Fields.content: content,
Fields.bodyPart: bodyPart,
};
}
factory UserModel.fromMap(Map<String, dynamic> map) {
return UserModel(
id: map['_id'] as int,
content: map['content'] as String,
bodyPart: map['bodyPart'] as String,
);
}
}
数据库助手：
import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';
import 'package:stackoverflowhelp/model.dart';
import 'package:flutter/material.dart';
const String databaseName = "measurement.db";
const String tableName = "measurementTable";
class MeasurementDatabase {
static final MeasurementDatabase instance = MeasurementDatabase._();
MeasurementDatabase._();
static Database? _database;
Future<Database> get database async {
if (_database != null) return _database!;
_database = await _initDB(databaseName);
return _database!;
}
Future<Database> _initDB(String filePath) async {
final dbPath = await getDatabasesPath();
final path = join(dbPath, filePath);
return await openDatabase(path, version: 1, onCreate: _onCreateDB);
}
Future _onCreateDB(Database db, int version) async {
const idType = 'INTEGER PRIMARY KEY AUTOINCREMENT';
const textType = 'TEXT NOT NULL';
await db.execute('''
CREATE TABLE $tableName(
${Fields.id} $idType,
${Fields.content} $textType,
${Fields.bodyPart} $textType
)
''');
debugPrint("success!!! DB Created");
}
///创建测量数据
Future createMeasurement(UserModel user) async {
final db = await instance.database;
debugPrint("Added Content : ${user.content} with ${user.bodyPart} to DB");
return await db.insert(tableName, user.toMap(), conflictAlgorithm: ConflictAlgorithm.replace);
}
Future<List<UserModel>> fetchMeasurement(String bodyPart) async {
final db = await instance.database;
final result = await db.rawQuery("SELECT * FROM $tableName WHERE ${Fields.bodyPart} = ?", [bodyPart]);
return result.map((e) => UserModel.fromMap(e)).toList();
}
Future<List<UserModel>> fetchAllMeasurement() async {
final db = await instance.database;
final result = await db.query(tableName);
final List<UserModel> allMeasurements = result.map((e) => UserModel.fromMap(e)).toList();
return allMeasurements;
}
}
主页：
import 'dart:math';
import 'package:flutter/material.dart';
import 'package:stackoverflowhelp/db_helper.dart';
import 'package:stackoverflowhelp/model.dart';
class HomePage extends StatefulWidget {
const HomePage({Key? key}) : super(key: key);
@override
State<HomePage> createState() => _HomePageState();
}
class _HomePageState extends State<HomePage> {
List<UserModel> fetchedData = [];
final bodyParts = ["Head", "Arms", "Chest", "Torso", "Legs", "Back", "Neck", "Head", "Arms", "Chest", "Torso", "Legs", "Back", "Neck"];
void createDummyData() async {
for (int i = 0; i < bodyParts.length; i++) {
await MeasurementDatabase.instance.createMeasurement(UserModel(content: "${Random().nextInt(10)}", bodyPart: bodyParts[i]));
}
}
void fetchBodyPart(String bodyPart) async {
fetchedData = await MeasurementDatabase.instance.fetchMeasurement(bodyPart);
debugPrint("Fetching: $bodyPart");
for (UserModel user in fetchedData) {
debugPrint("Content: ${user.content} with Body Part: ${user.bodyPart}");
}
setState(() {});
}
@override
Widget build(BuildContext context) {
return Scaffold(
appBar: AppBar(),
body: Column(
children: [
TextButton(
onPressed: () => createDummyData(),
child: const Text("Create Dummy Data"),
),
TextButton(
onPressed: () => fetchBodyPart("Torso"),
child: const Text("Fetch Body Part"),
),
ListView.builder(
shrinkWrap: true,
itemCount: fetchedData.length,
itemBuilder: (context, index) {
return ListTile(
title: Text(fetchedData[index].content),
subtitle: Text(fetchedData[index].bodyPart),
);
},
),
],
),
);
}
}
<details>
<summary>英文:</summary>
[![console][1]][1]
Create Model Class:
class Fields{
static const String id = '_id';
static const String content = 'content';
static const String bodyPart = 'bodyPart';
static const List<String> values = [id,content,bodyPart];
}
class UserModel{
int? id;
String content;
String bodyPart;
UserModel({
this.id,
required this.content,
required this.bodyPart,
});
Map<String, dynamic> toMap() {
return {
Fields.id: id,
Fields.content: content,
Fields.bodyPart: bodyPart,
};
}
factory UserModel.fromMap(Map<String, dynamic> map) {
return UserModel(
id: map['_id'] as int,
content: map['content'] as String,
bodyPart: map['bodyPart'] as String,
);
}
}
Database Helper:
import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';
import 'package:stackoverflowhelp/model.dart';
import 'package:flutter/material.dart';
const String databaseName = "measurement.db";
const String tableName = "measurementTable";
class MeasurementDatabase{
static final MeasurementDatabase instance = MeasurementDatabase._();
MeasurementDatabase._();
static Database? _database;
Future<Database> get database async {
if (_database != null) return _database!;
_database = await _initDB(databaseName);
return _database!;
}
Future<Database> _initDB(String filePath) async {
final dbPath = await getDatabasesPath();
final path = join(dbPath, filePath);
return await openDatabase(path, version: 1, onCreate: _onCreateDB);
}
Future _onCreateDB(Database db, int version)async{
const idType = 'INTEGER PRIMARY KEY AUTOINCREMENT';
const textType = 'TEXT NOT NULL';
await db.execute('''
CREATE TABLE $tableName(
${Fields.id} $idType,
${Fields.content} $textType,
${Fields.bodyPart} $textType
)
''');
debugPrint("success!!! DB Created");
}
///Create Measurement
Future createMeasurement(UserModel user)async{
final db = await instance.database;
debugPrint("Added Content : ${user.content} with ${user.bodyPart} to DB");
return await db.insert(tableName, user.toMap(),conflictAlgorithm: ConflictAlgorithm.replace);
}
Future<List<UserModel>> fetchMeasurement(String bodyPart)async{
final db = await instance.database;
final result = await db.rawQuery(
"SELECT * FROM $tableName WHERE ${Fields.bodyPart} = ?",[bodyPart]);
return result.map((e) => UserModel.fromMap(e)).toList();
}
Future<List<UserModel>> fetchAllMeasurement()async{
final db = await instance.database;
final result = await db.query(tableName);
final List<UserModel> allMeasurements = result.map((e) => UserModel.fromMap(e)).toList();
return allMeasurements;
}
}
HomePage:
import 'dart:math';
import 'package:flutter/material.dart';
import 'package:stackoverflowhelp/db_helper.dart';
import 'package:stackoverflowhelp/model.dart';
class HomePage extends StatefulWidget {
const HomePage({Key? key}) : super(key: key);
@override
State<HomePage> createState() => _HomePageState();
}
class _HomePageState extends State<HomePage> {
List<UserModel> fetchedData = [];
final bodyParts = ["Head","Arms","Chest","Torso","Legs","Back","Neck","Head","Arms","Chest","Torso","Legs","Back","Neck"];
void createDummyData()async{
for(int i = 0; i< bodyParts.length; i++){
await MeasurementDatabase.instance.createMeasurement(UserModel(content: "${Random().nextInt(10)}", bodyPart: bodyParts[i]));
}
}
void fetchBodyPart(String bodyPart)async{
fetchedData = await MeasurementDatabase.instance.fetchMeasurement(bodyPart);
debugPrint("Fetching: $bodyPart");
for(UserModel user in fetchedData){
debugPrint("Content: ${user.content} with Body Part: ${user.bodyPart}");
}
setState(() {});
}
@override
Widget build(BuildContext context) {
return Scaffold(
appBar: AppBar(),
body: Column(
children: [
TextButton(
onPressed: () => createDummyData(),
child: const Text("Create Dummy Data"),
),
TextButton(
onPressed: () => fetchBodyPart("Torso"),
child: const Text("Fetch Body Part"),
),
ListView.builder(
shrinkWrap: true,
itemCount: fetchedData.length,
itemBuilder: (context, index) {
return ListTile(
title: Text(fetchedData[index].content),
subtitle: Text(fetchedData[index].bodyPart),
);
},
),
],
),
);
}
}
[1]: https://i.stack.imgur.com/DN1nz.png
</details>
# 答案2
**得分**: 0
是的，你说得对，你可以有两种方法来添加条件直接在你的SQL查询中，并获取相应的数据：
```dart
static Future<dynamic> _fetchDesiredData(
{required String where,
required String whereTerm,
required String tableName}) async {
/* 创建用于获取所需数据的原始查询。 */
dynamic desiredData = await _db.rawQuery(
'''SELECT * FROM $tableName WHERE $where==$whereTerm;''');
return desiredData;
}
在这里，你需要将要过滤的数据的列名传递给 where，将过滤条件传递给 whereTerm，它将获取你的过滤数据。你可以根据需要添加更多条件，以进行更高级的筛选。
或者，你可以获取所有数据，然后筛选你的列表。
List<CarDm> filteredList = carsDmList.where((ele) => ele.brand == 'bmw');
在这里，你可以添加更多条件，使用 ||（或）和 &&（与）条件。
英文:
Yes you said i right, You can do with two ways adding condition directly in your sql query and fetching the according data:
static Future<dynamic> _fetchDesiredData(
{required String where,
required String whereTerm,
required String tableName}) async {
/* Creating the raw query for fetching desired data. */
dynamic desiredData = await _db.rawQuery(
'''SELECT * FROM $tableName WHERE $where==$whereTerm;''');
return desiredData;
}
here you need to pass the column name in where whose data you want to filter and the filter term in whereTerm and it will get your filtered data, You can play with it add more conditions if you want more advance filtration.
Or you and fetch all the data and filter your list.
List<CarDm> filteredList = carsDmList.where((ele) => ele.brand=='bmw');
here you can add more condition will putting ||(or) &&(and) conditions.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论