嵌套的 FutureBuilder 与从数据库延迟加载的嵌套调用

2022-01-23 00:00:00 database flutter sqlite sqflite flutter-layout

我需要在我可以遵循的两种方法中选择最佳方法.

我有一个使用 sqflite 保存数据的 Flutter 应用程序，在数据库中我有两个表:

员工:

+-------------+------------------+------+|员工编号 |员工姓名 |dep_id|+-------------+------+------+|e12 |艾达洛夫莱斯 |dep1 |+-------------+------+------+|e22 |爱因斯坦 |dep2 |+-------------+------+------+|e82 |格蕾丝·霍珀 |dep3 |+-------------+------+------+

SQL:

CREATE TABLE Employee(employee_id TEXT NOT NULL PRIMARY KEY,employee_name TEXT NOT NULL ,dep_id 文本，外键(dep_id)参考部门(dep_id)删除设置为空);

部门:

+--------+-----------+-------+|dep_id |dep_title |dep_num|+--------+-----------+--------+|dep1 |数学 |dep1 |+--------+-----------+--------+|dep2 |物理学 |dep2 |+--------+-----------+--------+|dep3 |电脑 |dep3 |+--------+-----------+--------+

SQL:

CREATE TABLE Department(dep_id TEXT NOT NULL PRIMARY KEY，dep_title TEXT NOT NULL ,dep_num 整数，);

我需要显示存储在 Employee 表中的部门的 ListGrid.我应该查看 Employee 表并从中获取部门 id，这很容易，但是在获取该 dep_id 之后，我需要从这些 id 中制作一张卡片，所以我需要来自 部门表.我从 Emplyee 表中获取的那些 id 的完整信息在 Department 表中.

<块引用>

每个表有数千行.

我有一个数据库助手类来连接数据库:

DbHelper 是这样的:

Future<List<String>>getDepartmentIds() 异步{'从员工表中获取所有 dep_id'}未来<部门>getDepartment(String id) 异步{'从部门表中获取部门以获取特定 ID'}未来<列表<部门>>getEmployeeDepartments() 异步{'''1.fetch all dep_id from Employee table2.对于每个id从部门表中获取部门记录'''var ids = 等待 getDepartmentIds();列出<部门>部门=[]；ids.forEach((map) async {deps.add(await getDepartment(map['dep_id']));});}

有两种方法:

第一个:

  1. 在 dbhelper 中定义一个函数，该函数返回 Employee 表中的所有 dep_id(getDepartmentIds 以及另一个返回部门对象的函数(model) 用于该特定 id.(getDepartment)

  2. 现在我需要两个 FutureBuilder 在彼此内部，一个用于获取 id，另一个用于获取部门模型.

第二个:

  1. 定义一个函数，首先获取 id，然后在该函数内部将每个 id 映射到部门模型.(getEmployeeDepartments)
  2. 所以我需要一个 FutureBuilder .

哪个更好??我应该让 FutureBuilders 处理它还是我应该向 dbHelper 施加压力来处理它?<​​/p>

如果我使用第一种方法，那么我必须(据我所知！)放置第二个未来调用(基于它的 id 获取 Department Object(model) 的那个(getDepartment)) 在 build 函数上，建议不要这样做.

第二个的问题是它在 dbHelper 中做了很多嵌套调用.

我使用 ListView.builder 来提高性能.

我用一些数据检查了两者，但无法确定哪个更好.我想这取决于颤振和 sqlite(sqflite).

哪个更好或有更好的方法?

解决方案

鉴于我在此示例中没有看到太多代码，我将对您的问题进行高级回答.

评估方法一

  1. 这部分马上就突出了:从 Employee 表中返回所有 dep_id"
  2. 我会说从头开始，因为全部归还"通常不是一个好的解决方案，尤其是因为您提到您的表格有很多行.

评估方法二

  1. 我不确定这与第一种方法相比在性能上有何差异，出于同样的原因，它似乎也很糟糕.我认为这只是极大地改变了您的 UI 逻辑.

典型的无尽"列表方法

  1. 您可以对 Employees 表进行查询，并与 Departments 表进行连接.
  2. 您将在您的 UI 上实现分页，并将您的值传递给第一步中的查询.
  3. 在基本层面上，您需要以下变量:Take、Skip、HasMore
  4. Take:请求每个查询的项目数
  5. 跳过:在下一个查询中要跳过的项目数，这将是您当前在内存中驱动 UI 的列表中的项目数的大小.
  6. HasMore:您可以在每个查询的响应中设置此项，让 UI 知道是否还有更多项目.
  7. 当您向下滚动列表时，当您到达底部时，您将请求更多项目.

最初发出查询例如:Take: 10, Skip: 0到达 UI 底部时的下一个查询:Take: 10, Skip: 10等等.

示例 sql 查询:

选择 *来自员工 E在 D.id = E.dept_id 上加入部门 D按 E.employee_name 订购偏移 {SKIP#} 行仅 FETCH NEXT {TAKE#} 行

希望这会有所帮助，我不完全确定您实际上要做什么 - 就代码而言.

I need to choose best approach between two approaches that I can follow.

I have a Flutter app that use sqflite to save data, inside the database I have two tables:

Employee:

+-------------+-----------------+------+
| employee_id | employee_name   |dep_id|
+-------------+-----------------+------+
|     e12     | Ada Lovelace    | dep1 |
+-------------+-----------------+------+
|     e22     | Albert Einstein | dep2 |
+-------------+-----------------+------+
|     e82     | Grace Hopper    | dep3 |
+-------------+-----------------+------+

SQL:

CREATE TABLE Employee(
    employee_id TEXT NOT NULL PRIMARY KEY,
    employee_name TEXT NOT NULL ,
    dep_id TEXT,
    FOREIGN KEY(dep_id) REFERENCES Department(dep_id)
    ON DELETE SET NULL
);

Department:

+--------+-----------+-------+
| dep_id | dep_title |dep_num|
+--------+-----------+-------+
| dep1   | Math      | dep1  |
+--------+-----------+-------+
| dep2   | Physics   | dep2  |
+--------+-----------+-------+
| dep3   | Computer  | dep3  |
+--------+-----------+-------+

SQL:

CREATE TABLE Department(
    dep_id TEXT NOT NULL PRIMARY KEY,
    dep_title TEXT NOT NULL ,
    dep_num INTEGER,
);

I need to show a ListGrid of departments that are stored in the Employee table. I should look at Employee table and fetch department id from it, This is easy but after fetching that dep_id I need to make a card from those ids so I need information from Department table. complete inforamtion for thoses id I had fetched from Emplyee table is inside Department table.

There are thousands of rows in each table.

I have a database helper class to connect to the database :

DbHelper is something like this:

Future<List<String>> getDepartmentIds() async{
    'fetch all dep_id from Employee table'
}

Future<Department> getDepartment(String id) async{
    'fetch Department from Department table for a specific id'
}

Future<List<Department>> getEmployeeDepartments() async{
    '''1.fetch all dep_id from Employee table
    2.for each id fetch Department records from Department table'''

    var ids = await getDepartmentIds();
    List<Departments> deps=[];
    ids.forEach((map) async {
        deps.add(await getDepartment(map['dep_id']));
      });
}

There is two approaches:

First One:

  1. Define a function in dbhelper that returns all dep_id from Employee table(getDepartmentIds and another function that returns a department object(model) for that specific id.(getDepartment)

  2. Now I need two FutureBuilder inside each other, one for fetching ids and the other one for fetching department model.

second One:

  1. Define a function that first fetch ids then inside that function each id is maped to department model.(getEmployeeDepartments)
  2. So I need one FutureBuilder .

Which one is better?? should I let FutureBuilders handle it or I should put pressure on dbHelper to habdle it?

If I use the first approach then I have to(as far as I can imagine!) put the the second future call(the one that fetch Department Object(model) based on it's id(getDepartment)) on build function and it's recommended no to do so.

And the problem with second one is that it does a lot of nested call in dbHelper.

I used ListView.builder for performance.

I checked both with some data but couldn't figure out which one is better. I guess it depends both on flutter and sqlite(sqflite).

which one is better or is there any better approach?

解决方案

Given that I don't see too much code on this example, I'll do a high-level answer on your questions.

Evaluate Approach One

  1. Right off the bat this part sticks out: "returns all dep_id from Employee table"
  2. I would say scratch that, since "return all" is typically never a good solution, especially since you mention your tables have a lot of rows.

Evaluate Approach Two

  1. I'm not sure what the difference in performance this has compared to the first approach, seems also bad for the same reasons. I think this one just changes your UI logic a big is all.

Typical 'Endless' List Approach

  1. You would do a query on the Employees table with a join to the Departments table.
  2. You would implement Pagination on your UI and pass in your values to the query from step one.
  3. At a basic level you'll need these variables: Take, Skip, HasMore
  4. Take: The count # of items to request each query
  5. Skip: The count # of items to skip on the next query, this will be the size of the number of items you currently have in your List in memory driving your UI.
  6. HasMore: You can set this on the response of each query, to let the UI know if there are still more items or not.
  7. As you scroll down the list, when you get to the bottom, you will request more items .

Initially issue a query for example: Take: 10, Skip: 0 Next query when you hit the bottom of the UI: Take: 10, Skip: 10 etc..

Example sql query:

SELECT * 
FROM Employees E
   JOIN Departments D on D.id = E.dept_id
order by E.employee_name  
offset {SKIP#} rows
FETCH NEXT {TAKE#} rows only

Hopefully, this helps, I'm not fully sure what you're trying to do actually - in terms of Code.

相关文章