Mongo 多重查询或数据库规范化
我的数据库使用 MongoDB。我当前正在处理的查询揭示了我的架构中可能存在的缺陷。以下是我收藏的相关布局。 请注意，games.players 是一个由 2 个玩家组成的数组，因为游戏是国际象棋。
users {_id, username, ...}
games {_id, players[], ...}
msgs {_id, username, gameid, time, msg}
我需要的数据是：
All msgs for games which a user is in which is newer than a given timestamp.
在 SQL 数据库中，我的查询类似于：
SELECT * FROM msgs WHERE time>=$time AND gameid IN
(SELECT _id FROM games WHERE players=$username);
但是，Mongo 不是关系数据库，因此不支持子查询或联接。我看到两种可能的解决方案。 在性能和效率方面什么会更好？
- 多个查询
- 选择用户所在的游戏，然后使用 $in 来匹配 msgs.gameid。
- 其他？
- 正常化
- 使 users.games 包含用户所在的所有游戏。
- 通过 msgs.gameid 将 games.players 复制到 msgs.players
- 等等，
I'm using MongoDB for my database. The query that I'm currently working on revealed a possible deficiency in my schema. Below is the relevant layout of my collections. Note that games.players is an array of 2 players since the game is chess.
users {_id, username, ...}
games {_id, players[], ...}
msgs {_id, username, gameid, time, msg}
The data that I need is:
All msgs for games which a user is in which is newer than a given timestamp.
In a SQL database, my query would look similar to:
SELECT * FROM msgs WHERE time>=$time AND gameid IN
(SELECT _id FROM games WHERE players=$username);
But, Mongo isn't a relational database, so doesn't support sub-queries or joins. I see two possible solutions. What would be better performance-wise and efficiency-wise?
- Multiple Queries
- Select games the user is in, then use $in to match msgs.gameid by.
- Other?
- Normalization
- Make users.games contain all games a user is in.
- Copy games.players to msgs.players by msgs.gameid
- etc.,
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我是 MongoDB 的新手，但我发现自己经常结合使用这两种方法。有些东西（例如用户名）经常被重复，以简化用于显示的查询，但每当我需要做的不仅仅是显示信息时，我最终都会编写多个查询，有时是 2 或 3 层深，使用 $in 来收集所有内容我需要处理给定操作的文档。
I'm a relative newbie to MongoDB, but I find my self frequently using a combination of the two approaches. Some things - e.g. user names - are frequently duplicated to simplify queries used for display, but any time I need to do more than display information, I wind up writing multiple queries, sometimes 2 or 3 levels deep, using $in, to gather all the documents I need to work with for a given operation.
你可以让自己“正常化”。我会向用户添加一个数组，列出他所属的游戏；
users {_id, username, games={game1,game2,game3}}
现在您可以对时间>time$ 且 {games._id “is in” users.games} 的消息进行查询
您必须维护每个用户的游戏列表。
You can "normalize" yourself. I would add an array to users that list the games he is a member of;
users {_id, username, games={game1,game2,game3}}
now you can do a query on msgs where the time>time$ and the {games._id "is in" users.games}
You will have to maintain the games list on each user.