如何将事物组合映射到关系数据库? [英] How to map combinations of things to a relational database?
问题描述
我有一个表,其记录表示某些对象.为了简单起见,我将假定该表只有一列,这是唯一的ObjectId
.现在,我需要一种方法来存储该表中对象的组合.组合必须是唯一的,但可以是任意长度.例如,如果我有ObjectId
s
I have a table whose records represent certain objects. For the sake of simplicity I am going to assume that the table only has one column, and that is the unique ObjectId
. Now I need a way to store combinations of objects from that table. The combinations have to be unique, but can be of arbitrary length. For example, if I have the ObjectId
s
1,2,3,4
我要存储以下组合:
{1,2}, {1,3,4}, {2,4}, {1,2,3,4}
不需要订购.我当前的实现是要有一个将ObjectId
s映射到CombinationId
s的表Combinations
.因此,每个组合都会收到一个唯一的ID:
The ordering is not necessary. My current implementation is to have a table Combinations
that maps ObjectId
s to CombinationId
s. So every combination receives a unique Id:
ObjectId | CombinationId
------------------------
1 | 1
2 | 1
1 | 2
3 | 2
4 | 2
这是上面示例的前两个组合的映射.问题在于,用于查找特定组合的CombinationId
的查询似乎非常复杂.该表的两个主要使用方案是遍历所有组合,并检索特定的组合.该表将创建一次,并且永远不会更新.我正在通过JDBC使用 SQLite .有没有更简单的方法或最佳实践来实现这种映射?
This is the mapping for the first two combinations of the example above. The problem is, that the query for finding the CombinationId
of a specific Combination seems to be very complex. The two main usage scenarios for this table will be to iterate over all combinations, and the retrieve a specific combination. The table will be created once and never be updated. I am using SQLite through JDBC. Is there any simpler way or a best practice to implement such a mapping?
推荐答案
问题在于,用于查找特定组合的CombinationId的查询似乎非常复杂.
The problem is, that the query for finding the CombinationId of a specific Combination seems to be very complex.
应该不会太糟.如果您希望所有包含所选项目的组合(允许添加其他项目),则类似于:
Shouldn't be too bad. If you want all combinations containing the selected items (with additional items allowed), it's just something like:
SELECT combinationID
FROM Combination
WHERE objectId IN (1, 3, 4)
GROUP BY combinationID
HAVING COUNT(*) = 3 -- The number of items in the combination
如果您只需要特定的组合(不允许有多余的物品),它可能会更像:
If you need only the specific combination (no extra items allowed), it can be more like:
SELECT combinationID FROM (
-- ... query from above goes here, this gives us all with those 3
) AS candidates
-- This bit gives us a row for each item in the candidates, including
-- the items we know about but also any 'extras'
INNER JOIN combination ON (candidates.combinationID = combination.combinationID)
GROUP BY candidates.combinationID
HAVING COUNT(*) = 3 -- Because we joined back on ALL, ones with extras will have > 3
您还可以在此处(或在原始查询中)使用NOT EXISTS,这似乎更容易解释.
You can also use a NOT EXISTS here (or in the original query), this seemed easier to explain.
最后,您也可以选择一个简单的查询
Finally you could also be fancy and have a single, simple query
SELECT combinationID
FROM Combination AS candidates
INNER JOIN Combination AS allItems ON
(candidates.combinationID = allItems.combinationID)
WHERE candidates.objectId IN (1, 3, 4)
GROUP BY combinationID
HAVING COUNT(*) = 9 -- The number of items in the combination, squared
换句话说,如果我们正在寻找{1,2},并且与{1,2,3}结合使用,我们将得到{candidates,allItems} JOIN
结果为:
So in other words, if we're looking for {1, 2}, and there's a combination with {1, 2, 3}, we'll have a {candidates, allItems} JOIN
result of:
{1, 1}, {1, 2}, {1, 3}, {2, 1}, {2, 2}, {2, 3}
额外的3个结果导致COUNT(*)
在GROUP
之后是6行,而不是4行,因此我们知道这不是我们要遵循的组合.
The extra 3 results in COUNT(*)
being 6 rows after GROUP
ing, not 4, so we know that's not the combination we're after.
这篇关于如何将事物组合映射到关系数据库?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!