如何有效解析模型对象中的一对多关系? [英] How do I parse efficiently one to many relation in model objects?

问题描述

我在表中有 1:n 关系:

表Aid、名称、foo、bar

表Bid, x, y, z, id_A

在我的域模型中，表 A 的每一行都是一个对象，包含 B 的集合.

我该如何解析?

我可以先查询 A，然后在解析循环中，对每一行查询 B.但这看起来效率不高.

我也尝试过加入查询，但这会为 B 的每个条目添加一个新行，然后我无法将这些行中的每一行映射到 1 个模型对象.

是否有一个查询可以将连接的额外行减少"为一个聚合结果?

看起来像关系数据库的标准案例，但我找不到有关它的信息.

谢谢.

解决方案

在 SQLite 等嵌入式数据库中，没有客户端/服务器通信开销，因此执行许多这样的小查询实际上是可行的:

>

SELECT id, x, y, z FROM TableB WHERE id_A = ?

如果您真的想使用单个查询来加载所有 B 子对象，您可以使用两个单独的查询，如下所示:

SELECT id, name, foo, bar FROM TableA ORDER BY id;SELECT id_A, id, x, y, z FROM TableB ORDER BY id_A;

然后您将并行遍历两个游标，并为每个 A 记录收集具有相同 ID 的所有 B 记录；ORDER BY 子句确保您以正确的顺序看到它们:

cursorA = db.query(...);cursorB = db.query(...);cursorB.moveToFirst();而 (cursorA.moveToNext()) {id = cursor.getInt(0);a = new ObjectA(id, ...);while (!cursorB.isAfterLast() &&cursorB.getInt(0) == id) {b = new ObjectB(...);a.addB(b);cursorB.moveToNext();}}

请注意，这不太可能比对每个 A 进行单独查询更有效，因为对 id_A 列上的所有记录进行排序并不比在同一列.(但是，如果该列没有索引，查找将比排序慢得多.)

I have 1:n relationship in tables:

Table A id, name, foo, bar

Table B id, x, y, z, id_A

In my domain model each of the rows of table A is an object and contains a collection of B.

How do I parse this?

I could first do query for A and then, in the parsing loop, for each row do a query to B. But this looks not very efficient.

I also tried join query, but this adds a new row for each entry of B, and then I can't map each of these rows to 1 model object.

Is there maybe a query where I can "reduce" the extra rows of join to one aggregated result?

Looks like a standard case of relational databases, but I can't find information about it.

Thanks.

解决方案

In an embedded database such as SQLite, there is no client/server communication overhead, so it is actually feasible to execute lots of little queries like this:

SELECT id, x, y, z FROM TableB WHERE id_A = ?

If you really want to use a single query for the loading of all B subobjects, you could use two separate queries like this:

SELECT id,   name, foo, bar FROM TableA ORDER BY id;
SELECT id_A, id, x, y, z    FROM TableB ORDER BY id_A;

You would then step through the two cursors in parallel, and for each A record, collect all the B records with the same ID; the ORDER BY clauses ensure that you see them in the correct sequence:

cursorA = db.query(...);
cursorB = db.query(...);
cursorB.moveToFirst();
while (cursorA.moveToNext()) {
    id = cursor.getInt(0);
    a = new ObjectA(id, ...);
    while (!cursorB.isAfterLast() &&
           cursorB.getInt(0) == id) {
        b = new ObjectB(...);
        a.addB(b);
        cursorB.moveToNext();
    }
}

Please note that this is not likely to be much more efficient than doing a separate query for each A, because sorting all records on the id_A column is not much more efficient than doing many lookups on the same column. (However, without an index on that column, the lookups would be much slower than the sorting.)

这篇关于如何有效解析模型对象中的一对多关系?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！