fluttersqflite
Flutter Sqflite - how to add conditions to present specific data into a listview
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.
Solution
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),
);
},
),
],
),
);
}
}