How do I fetch the data from sqflite tables to display in list view?

Question

I created two tables, WordsTable and WordsDefenition.

In the first table, WordId_id is a foreign key to the second table. In the second table, three word definitions are stored for each word from the first table.

Here is how I inserted the data into both tables, what I want to ask is how do I fetch the data from both tables to display in list view?

import 'package:save_geez_learning_aid/SqlFlite_Database/DIctionery_word_model.dart';
import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';

class SqlfdatabaseHelper{
  Future<Database> initializeDB() async {
    String path = await getDatabasesPath();
    return openDatabase(
      join(path, 'example.db'),
      onCreate: (database, version) async {
        await database.execute(
          "CREATE TABLE WordsTable("
              "WordId_id INTEGER PRIMARY KEY AUTOINCREMENT, "
              "Column_word TEXT NOT NULL,"
              "Column_Languages Text NOT NULL,"
              ")",
        );
        await database.execute(
            "INSERT INTO WordsTable ('WordId_id', 'Column_word', 'Column_Languages')values (1, 'ዘመድ', 'amharic')",
        );

        await database.execute(
          "CREATE TABLE WordsDefenition("
              "WordId_id int FOREIGN KEY REFERENCES WordsTable(WordId_id),"
              "Column_word_geez TEXT NOT NULL,"
              "Column_word_oromoo TEXT NOT NULL,"
              "Column_word_amharic TEXT NOT NULL,"
              "Column_word_english TEXT NOT NULL,"
              
              ")",
        );

        await database.execute(
          "INSERT INTO WordsDefenition ('WordId_id', 'Column_word_geez', 'Column_word_oromoo ','Column_word_amharic','Column_word_amharic')values (1, 'fira', 'fira', 'familly')",
        );


      },
      version: 1,
    );
  }
}

what is your problem ? you have error ? what is error text ? — DJafari, Aug 06 '21 at 10:53

score 0 · Answer 1 · answered Aug 06 '21 at 13:25

0

You could use the .rawQuery or .query method to fetch the data. It would return result in Future<List<Map<String,Object?>>> then you could convert it to List and send it to ListView.

answered Aug 06 '21 at 13:25

Phil D

64
10

AS You can see i have two tables that are related by foreign key so how I fetch data from the two tables and display in list view – beby Aug 07 '21 at 02:00
Check out the sqlite page on Joining, https://www.sqlitetutorial.net/sqlite-inner-join/, I will reference inner join here for your case for example: SELECT * FROM WordsTable INNER JOIN WordsDefenition ON WordsTable.WordId_id = WordsDefenition.WorldId_id – Phil D Aug 07 '21 at 05:58
Thank you brother and also i want to implement a search bar functionality to search a word . the word is displayed when the user search it, please can you show me a hint or Starting code to implement all these. it takes me too much time – beby Aug 07 '21 at 06:00
i use the above code to create the two table. how do i know actually those tables are created – beby Aug 07 '21 at 06:02
I haven't use much on search bar. But I think you could look up for package about search bar on pub dev. For knowing if the tables are created, you could check if the database **example.db** is there using Android Studio. Refer here more details explanation: https://stackoverflow.com/questions/17529766/view-contents-of-database-file-in-android-studio – Phil D Aug 07 '21 at 06:10

How do I fetch the data from sqflite tables to display in list view?

1 Answers1