Getting values from 2 tables in Supabase and storing in a Flutter data model

Question

How do I properly save the returned results of Supabase from 2 tables in a Flutter class. Here are the sql statements that created the 2 tables:

-- Table for Survey Categories 
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    category_name TEXT NOT NULL,
    parent_category_id INT REFERENCES categories(id)
);

-- Table for Survey Questions 
CREATE TABLE questions (
    id SERIAL PRIMARY KEY,
    question_text TEXT NOT NULL,
    question_id VARCHAR(5),
    main_category_id INT REFERENCES categories(id) NOT NULL,
    sub_category_id INT REFERENCES categories(id)
);

Here is the class

class Question {
  final int id;
  final String questionText;
  final String? questionId;
  final int mainCategoryId;
  final String mainCategoryName;
  final int? subCategoryId;
  final String? subCategoryName;

  Question(
      {required this.id,
      required this.questionText,
      this.questionId,
      required this.mainCategoryId,
      required this.mainCategoryName,
      this.subCategoryId,
      this.subCategoryName});

  Question.fromJson(Map<dynamic, dynamic> json)
      : id = json['id'],
        questionText = json['question_text'],
        questionId = json['question_id'],
        mainCategoryId = json['main_category_id'],
        mainCategoryName = json['main_category']['category_name'],
        subCategoryId = json['sub_category_id'],
        subCategoryName = json['sub_category']['category_name'];
}

Here is the repository

class QuestionRepository {
  final supabase = Supabase.instance.client;

  Future<List<Question>> getQuestions() async {
    try {
      final response = await supabase.from('questions').select(
          '*, main_category: main_category_id(*), sub_category: sub_category_id(*)');

      //print(response);
      if (response.isNotEmpty) {
        final questions =
            response.map((data) => Question.fromJson(data)).toList();

        //print(questions);
        return questions;
      } else {
        throw Exception('Failed to fetch questions!!');
      }
    } catch (error) {
      throw Exception('Error fetching questions: $error');
    }
  }
}

I am just getting a CircularProgressIndicator in a separate widget that supposed to display the questions. Debugging by printing the values, the response variable showed the correct results, a sample entry was {id: 53, question_text: Alam ko ang tamang solusyon sa isyu at suliranin sa aming komunidad, question_id: 3, main_category_id: 8, sub_category_id: 11, main_category: {id: 8, category_name: Family Life Management, parent_category_id: null}, sub_category: {id: 11, category_name: Kaalaman (Knowledge), parent_category_id: 8}}. I also tried to print the question but nothing happens / not printing anything so it must be that the response is not being saved in question. How should I properly declare it in the class noting some have null values? My original query involves only one table and it worked, but now I tried using 2 tables and it was not working. Thanks in advance!

Answer 1

Microsoft Bing/CoPilot answered it for me. I should have handled null values. The corrected code was:

Question.fromJson(Map<dynamic, dynamic> json)
    : id = json['id'],
      questionText = json['question_text'],
      questionId = json['question_id'],
      mainCategoryId = json['main_category_id'],
      mainCategoryName = json['main_category'] != null ? json['main_category']['category_name'] : null,
      subCategoryId = json['sub_category_id'],
      subCategoryName = json['sub_category'] != null ? json['sub_category']['category_name'] : null;

It is now working.