通过父表访问 SQL 模型类的访问方法

huangapple go评论59阅读模式
英文:

Access method to a SQL model class through the parent table

问题

我有一个包含两个表的数据库,每个表都有自己的类,其中包括toMap和fromMap方法。每个表都有一个提供者和SQL助手类,用于CRUD操作。我的问题是,当我只有第一个表(父表)的上下文时,如何访问第二个表。想要访问模型类secondTable()。

第二个表的id是外键,引用了第一个表的id。

在我的第二个表SQL操作中,我尝试以下方式:

Future<List<Map<String, dynamic>>> getData(int firstTableId) async {
    final db = SQLite.db;
    List<Map<String, dynamic>> results = await db.rawQuery('''
        SELECT *
        FROM secondtable
        INNER JOIN firsttable ON secondtable.id = firsttable.id
        WHERE id = ?
      ''', [firstTableId]);
    
    return results;
}

之后,我有一个提供者:

Future<SecondTable?> getData(int firstTableId) async {
    final results = await _databaseHelper.getData(firstTableId);
    if (results.isNotEmpty) {
        Map<String, dynamic> row = results.first;

        SecondTable secondTable = SecondTable.fromMap(row);
        return secondTable;
    } else {
        return null;
    }
}

在需要信息的页面中,我像这样调用提供者:

@override
void initState() {
    super.initState();
    _seconTableDataProvider =
        Provider.of<SecondTableDataProvider>(context, listen: false);
    loadSecondTable();
}

FirstTable _firstTable = FirstTable();
SecondTable? _secondTable = SecondTable();

Future<void> loadSecondTable() async {
    _secondTable = await _seconTableDataProvider.getData(_firstTable.id!);
}

我总是得到一个强制类型转换错误,因为它返回null。如果我像这样硬编码一个id:

_seconTableDataProvider.getData(1);

问题是DatabaseException(模棱两可的列名:id)。

但如果我创建一个新的变量作为外键,它不会获取第一个表列的值,它再次为null。

我不知道现在该怎么办。

英文:

I have a DB with two tables, each table has his own class with toMap and fromMap method. Each table has a provider and SQLhelper class with the CRUD. My question is how can I access to the table 2 when I only have the context of the table 1 (parent table). The idea is to access to the model class secondTable().

The secondtable.id is the foreign key which references firsttable.id

In my second table sql operations I'm trying this way:

 Future&lt;List&lt;Map&lt;String, dynamic&gt;&gt;&gt; getData(int firstTableId) async {
    final db = SQLite.db;
    List&lt;Map&lt;String, dynamic&gt;&gt; results = await db.rawQuery(&#39;&#39;&#39;
    SELECT *
    FROM secondtable
    INNER JOIN firsttable ON secondtable.id = firsttable.id
    WHERE id = ?
  &#39;&#39;&#39;, [firstTableId]);

    return results;

After that I have a provider:

Future&lt;SecondTable?&gt; getData(int firstTableId) async {
    final results = await _databaseHelper.getData(firstTableId);
    if (results.isNotEmpty) {
      Map&lt;String, dynamic&gt; row = results.first;

      
      SecondTable secondTable = SecondTable.fromMap(row);
      return secondTable;
    } else {
      return null;
    }
  }

And in the page that I need the information I call the provider like that:

@override
  void initState() {
    super.initState();
    _seconTableDataProvider =
        Provider.of&lt;SecondTableDataProvider&gt;(context, listen: false);
    loadSecondTable();
  }
FirstTable _firstTable = FirstTable();
SecondTable? _secondTable = SecondTable();

  Future&lt;void&gt;  loadSecondTable() async {
    _secondTable = await   _seconTableDataProvider.getData(_firstTable.id!);
  }

I have always a cast error, because return me null. If I hardcode an id like:

_seconTableDataProvider.getData(1);

The problem is DatabaseException(ambiguous column name: id

But if I create a new variable as a foreign key. It doesn't take the value ok the firstTable column referenced and its null again.

I don't know how to act now.

答案1

得分: 0

以下是翻译好的部分:

 SQL 语法中存在问题。正确的做法是:

List<Map<String, dynamic>> results = await db.rawQuery('''
    SELECT secondtable.*
    FROM secondtable
    INNER JOIN firsttable ON secondtable.firstTableId = firsttable.id
    WHERE firsttable.id = ?
  ''', [firstTableId]);

之后,您必须确保初始化 _firstTable

@override
void initState() {
    super.initState();
    _secondTableDataProvider =
        Provider.of<SecondTableDataProvider>(context, listen: false);
}

@override
void didChangeDependencies() {
    super.didChangeDependencies();
    _firstTable = ModalRoute.of(context)!.settings.arguments as FirstTable;
    loadSecondTable();
}

最后,您需要注意异步调用 loadSecondTable 函数,因为它需要访问数据的时间。所以请使用 FutureBuilder 或其他选项。

英文:

There is a problem in the SQL syntax. The correct way to do it is:

List&lt;Map&lt;String, dynamic&gt;&gt; results = await db.rawQuery(&#39;&#39;&#39;
    SELECT secondtable.*
    FROM secondtable
    INNER JOIN firsttable ON secondtable.firstTableId = firsttable.id
    WHERE firsttable.id = ?
  &#39;&#39;&#39;, [firstTableId]);

After that you have to make sure to initialize the _firstTable.

 @override
  void initState() {
    super.initState();
_secondTableDataProvider =
        Provider.of&lt;SecondTableDataProvider&gt;(context, listen: false);
  }

  @override
  void didChangeDependencies() {
    super.didChangeDependencies();
    _firstTable = ModalRoute.of(context)!.settings.arguments as FirstTable;
    loadSecondTable();
  }

And finally you need to take care with the async call to the loadSecondTable function, due the time it needs to acces the data. So use Fututrebuilder or other options.

huangapple
  • 本文由 发表于 2023年2月18日 00:57:31
  • 转载请务必保留本文链接:https://go.coder-hub.com/75487157.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定