使用MongoDB有效地确定层次结构中记录的所有者 [英] Efficiently determine owner of a record in a hierarchy with MongoDB
问题描述
我试图实现以下：
选择我拥有的所有记录，其中所有权是我创建的对象我管理的用户已创建，其中用户管理可以在管理用户的用户层次结构中。
Select all the records that I own, where ownership is objects I have created OR objects a user I manage has created, where user management can be in a hierarchy of users managing users
所有权显然很直接，由对应于所有者的简单ID处理。用户管理的层次结构让我有点难以执行，而不需要大量的ID列表（你可以明显地找到每个用户被管理，列出每个对象创建的任何用户使用IN子句或类似的）。
Ownership is clearly straight forward and could be handled by a simple id corresponding to the owner. The hierarchy of user management has me a little stumped to perform without heavy lifting through large lists of IDs (you can obviously just find every user that is managed and list every object created by any of those users using an IN clause or similar).
理想情况下，这一切都发生在单个查询中，因此可能会发生正常的分页和条件。
Ideally this all happens in a single query so normal paging and conditions can occur.
可能有一些数学来完成它 - 具有可以以某种方式散列以确定它们是否由命令链中的任何人拥有的ID。
I was thinking that there might have been some maths to get it done - having IDs that can somehow be hashed to determine if they are owned by anyone in the chain of command.
这种事情的任何参考资料？
Any references for this sort of thing?
我缺少明显的东西吗？
使用MongoDB，如果这有什么区别，但乐于考虑其他数据库的灵感。
Using MongoDB if that makes a difference, but happy to think about other databases for inspiration.
更新：
已创建了一个包含1,000,000条记录的MongoDB集合，以获取关于查询上IN子句的可管理参数数量的确切数据。当我有一些具体的信息时，会报告回来。
UPDATE: have created a MongoDB collection with 1,000,000 records to get some solid data on exactly what constitutes a manageable number of parameters for an IN clause on a query. Will report back when I have some concrete information.
分析：
使用ruby-mongo-driver和ruby benchmark lib 。
Using ruby-mongo-driver and the ruby benchmark lib.
MongoDB集合，包含1039944条记录
MongoDB Collection with 1039944 records
记录定义为：
{
first_name: String,
last_name: String,
email: String,
phone: String,
company: String,
owner: BSON::ObjectId
}
随机生成的所有字段的值。
With randomly generated values for all fields.
所有者字段有一个索引。
The Owner field has an index.
运行具有以下条件的查询：
Running queries with the following conditions:
conditions = {"owner" => { "$in" => id_list }}
opts = {skip: rand, limit: 100}
结果： / p>
Results:
# 10201 ids
# user system total real
# 0: 0.240000 0.000000 0.240000 ( 0.265148)
# 1: 0.240000 0.010000 0.250000 ( 0.265757)
# 2: 0.240000 0.000000 0.240000 ( 0.267149)
# 3: 0.240000 0.000000 0.240000 ( 0.269981)
# 4: 0.240000 0.000000 0.240000 ( 0.270436)
# Find: 0.240000 0.000000 0.240000 ( 0.266709)
# 5201 ids
# user system total real
# 0: 0.120000 0.000000 0.120000 ( 0.133824)
# 1: 0.120000 0.000000 0.120000 ( 0.134787)
# 2: 0.110000 0.000000 0.110000 ( 0.133262)
# 3: 0.110000 0.000000 0.110000 ( 0.136046)
# 4: 0.120000 0.000000 0.120000 ( 0.141220)
# Find: 0.130000 0.000000 0.130000 ( 0.139110)
# 201 ids
# user system total real
# 0: 0.010000 0.000000 0.010000 ( 0.006044)
# 1: 0.000000 0.000000 0.000000 ( 0.004681)
# 2: 0.010000 0.000000 0.010000 ( 0.004578)
# 3: 0.000000 0.000000 0.000000 ( 0.007048)
# 4: 0.010000 0.000000 0.010000 ( 0.008487)
# Find: 0.000000 0.000000 0.000000 ( 0.005990)
# 1 id (NOT using IN)
# user system total real
# 0: 0.000000 0.000000 0.000000 ( 0.002868)
# 1: 0.000000 0.000000 0.000000 ( 0.004937)
# 2: 0.010000 0.000000 0.010000 ( 0.003151)
# 3: 0.000000 0.000000 0.000000 ( 0.002983)
# 4: 0.000000 0.000000 0.000000 ( 0.003313)
# Find: 0.000000 0.000000 0.000000 ( 0.002742)
10k ids在查询中，性能相当漂亮。
Even with a list of 10k ids in the query, performance is pretty snappy.
推荐答案
如果您试图从MongoDB选择记录基于列值，你需要加入一个用户管理表来确定，然后NoSQL正在对你...
If you are trying to "select" records from MongoDB based on a "column" having a value from a set of possible values that you'd need a join against a user management table to determine, then NoSQL is working against you...
如果用户ID列表仍然可以管理，可以做一个 where ownerId in（？，？，？，？，？...）查询类型（首先确定列表后）：
If the list of user IDs is still manageable you can do a where ownerId in (?,?,?,?,?...) type of query (after having first determined the list):
db.documents.find({owner:{$in: [1234, 2345, 4444, 77777, 99999]}})
NoSQL的方式可能是将事物反规范化，例如不仅包括文档中的ownerId，管理层次结构：
The NoSQL way is probably to denormalize things, for example by including not just the ownerId in the document, but the complete path up the management hierarchy:
{ _id: 'the document A',
owner : 1234,
managers: [ 2345, 4444, 77777, 99999 ]
}
当然，需要在用户层次结构移动时更新。
Of course, that will need to be updated when the user hierarchy gets shifted around.
这篇关于使用MongoDB有效地确定层次结构中记录的所有者的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
