I'm having a bit of a problem when it comes to my database and query design. Here is what I'd like to have (Honestly, a lot like stack overflow).:
在涉及到我的数据库和查询设计时,我遇到了一些问题。这是我想要的(老实说,很像堆栈溢出):
- An item has many posts.
- A post has many comments.
- Comments can be flagged, liked, and disliked.
- Comments cannot have subcomments.
一个项目有很多帖子。
一篇文章有很多评论。
评论可以被标记,喜欢和不喜欢。
评论不能有子评论。
The table structure is as follows:
表结构如下:
Items
-----
iid
desc
...
Posts
-----
pid
iid
uid
date
desc
...
Comments
-----
cid
pid
uid
date
desc
...
The logic for all of this is: get all posts for item -> for each post, get all comments. Would it be best to do this all in one query? Is it best to do one query to get all posts then a separate query for each set of comments on each post?
所有这一切的逻辑是:为每个帖子获取项目的所有帖子 - >,获取所有评论。最好是在一个查询中执行此操作吗?最好是进行一次查询以获取所有帖子,然后针对每个帖子的每组评论单独查询?
If I do one query, then I'll potentially have a 100 row behemoth where I'll have a ton of duplicate data. If I do a separate call for each post, then I'll have far too many queries. Adivce?
如果我做一个查询,那么我可能会有一个100行的庞然大物,我将有大量的重复数据。如果我为每个帖子单独调用,那么我会有太多的查询。书于?
3 个解决方案
#1
2
I imagine your system is going to have 3 distinctive views and I would structure it as follows:
我想你的系统将有3个不同的视图,我将按如下方式构建它:
- A list of items - showing the item description and the number of posts attached to that item. e.g.
SELECT i.desc, ...., COUNT(p.pid) FROM items i LEFT JOIN posts p ON i.iid = p.iid GROUP BY i.iid ORDER BY i.date DESC - A list of posts for a selected item - showing the posts and number of comments made for each post e.g
SELECT p.desc, ...., COUNT(c.cid) FROM posts p LEFT JOIN comments c ON p.pid = c.pid WHERE p.iid = $iid GROUP BY p.pid ORDER BY p.date DESC - A post page which shows the post and then comments underneath. This page would require two queries. A query to collect the post information, returning exactly 1 database row. And then a query to collect each individual comment attached to this post. E.g.
SELECT p.desc, ... FROM posts p WHERE p.pid = $pid LIMIT 1andSELECT c.desc, ... FROM comments c WHERE c.pid = $pid ORDER BY c.date DESC. You could alter the ordering here to mimic how * orders the data by oldest, votes, etc.
项目列表 - 显示项目描述和附加到该项目的帖子数量。例如SELECT i.desc,....,COUNT(p.pid)FROM items i LEFT JOIN posts p ON i.iid = p.iid GROUP BY i.iid ORDER BY i.date DESC
所选项目的帖子列表 - 显示每个帖子的帖子和评论数量,例如SELECT p.desc,....,COUNT(c.cid)FROM posts p LEFT JOIN comments c ON p.pid = c .pid WHERE p.iid = $ iid GROUP BY p.pid ORDER BY p.date DESC
一个帖子页面,显示帖子,然后是下面的评论。此页面需要两个查询。用于收集帖子信息的查询,正好返回1个数据库行。然后查询收集此帖子附带的每条评论。例如。 SELECT p.desc,... FROM posts p WHERE p.pid = $ pid LIMIT 1和SELECT c.desc,... FROM comments c WHERE c.pid = $ pid ORDER BY c.date DESC。您可以在此处更改排序,以模仿*如何按最旧,投票等顺序排列数据。
I would say this is scalable model and I would always advise writing lean MySQL queries that only retrieve the information needed each displayed entity.
我会说这是可扩展的模型,我总是建议编写精益MySQL查询,只检索每个显示实体所需的信息。
To mimic this page I would use the following code ...
为了模仿这个页面,我将使用以下代码...
itemRs = mysql_query("SELECT i,iid, i.desc, ... FROM items i WHERE i.iid = $iid LIMIT 1");
// all posts relating to the item
postsRs = mysql_query("SELECT p.pid, p.desc, .... FROM posts p LEFT JOIN comments c ON p.pid = c.pid WHERE p.iid = $iid ORDER BY p.date DESC");
// all comments for all posts relating to the item
commentsRs = mysql_query("SELECT c.pid, c.cid, c.desc, .... FROM comments c INNER JOIN posts p ON p.pid = c.pid INNER JOIN items i ON p.iid = i.iid WHERE p.iid = $iid ORDER BY p.date DESC");
You then would need to create a method to display this data. Perhaps encapsulate all this functionality into a DAO (Data Access Object) called Item that retrieves all this required data before calling a display() method to show the data in the format you want.
然后,您需要创建一个方法来显示此数据。也许将所有这些功能封装到一个名为Item的DAO(数据访问对象)中,在调用display()方法以显示所需格式的数据之前,它会检索所有这些必需的数据。
#2
0
I would do this in two queries: one query for to get all the posts of a particular item and other query for to get all the comments of all posts of a particular item. Some ActiveRecord implementations work the same.
我会在两个查询中执行此操作:一个查询以获取特定项目的所有帖子以及其他查询以获取特定项目的所有帖子的所有评论。一些ActiveRecord实现的工作方式相同。
#3
0
Assigning auto id to post and comment and also item and retrieving post and comment belongs to that item id will be best, i suppose.
我认为,将自动ID分配给帖子和评论以及项目和检索帖子和评论属于该项目ID是最好的。
#1
2
I imagine your system is going to have 3 distinctive views and I would structure it as follows:
我想你的系统将有3个不同的视图,我将按如下方式构建它:
- A list of items - showing the item description and the number of posts attached to that item. e.g.
SELECT i.desc, ...., COUNT(p.pid) FROM items i LEFT JOIN posts p ON i.iid = p.iid GROUP BY i.iid ORDER BY i.date DESC - A list of posts for a selected item - showing the posts and number of comments made for each post e.g
SELECT p.desc, ...., COUNT(c.cid) FROM posts p LEFT JOIN comments c ON p.pid = c.pid WHERE p.iid = $iid GROUP BY p.pid ORDER BY p.date DESC - A post page which shows the post and then comments underneath. This page would require two queries. A query to collect the post information, returning exactly 1 database row. And then a query to collect each individual comment attached to this post. E.g.
SELECT p.desc, ... FROM posts p WHERE p.pid = $pid LIMIT 1andSELECT c.desc, ... FROM comments c WHERE c.pid = $pid ORDER BY c.date DESC. You could alter the ordering here to mimic how * orders the data by oldest, votes, etc.
项目列表 - 显示项目描述和附加到该项目的帖子数量。例如SELECT i.desc,....,COUNT(p.pid)FROM items i LEFT JOIN posts p ON i.iid = p.iid GROUP BY i.iid ORDER BY i.date DESC
所选项目的帖子列表 - 显示每个帖子的帖子和评论数量,例如SELECT p.desc,....,COUNT(c.cid)FROM posts p LEFT JOIN comments c ON p.pid = c .pid WHERE p.iid = $ iid GROUP BY p.pid ORDER BY p.date DESC
一个帖子页面,显示帖子,然后是下面的评论。此页面需要两个查询。用于收集帖子信息的查询,正好返回1个数据库行。然后查询收集此帖子附带的每条评论。例如。 SELECT p.desc,... FROM posts p WHERE p.pid = $ pid LIMIT 1和SELECT c.desc,... FROM comments c WHERE c.pid = $ pid ORDER BY c.date DESC。您可以在此处更改排序,以模仿*如何按最旧,投票等顺序排列数据。
I would say this is scalable model and I would always advise writing lean MySQL queries that only retrieve the information needed each displayed entity.
我会说这是可扩展的模型,我总是建议编写精益MySQL查询,只检索每个显示实体所需的信息。
To mimic this page I would use the following code ...
为了模仿这个页面,我将使用以下代码...
itemRs = mysql_query("SELECT i,iid, i.desc, ... FROM items i WHERE i.iid = $iid LIMIT 1");
// all posts relating to the item
postsRs = mysql_query("SELECT p.pid, p.desc, .... FROM posts p LEFT JOIN comments c ON p.pid = c.pid WHERE p.iid = $iid ORDER BY p.date DESC");
// all comments for all posts relating to the item
commentsRs = mysql_query("SELECT c.pid, c.cid, c.desc, .... FROM comments c INNER JOIN posts p ON p.pid = c.pid INNER JOIN items i ON p.iid = i.iid WHERE p.iid = $iid ORDER BY p.date DESC");
You then would need to create a method to display this data. Perhaps encapsulate all this functionality into a DAO (Data Access Object) called Item that retrieves all this required data before calling a display() method to show the data in the format you want.
然后,您需要创建一个方法来显示此数据。也许将所有这些功能封装到一个名为Item的DAO(数据访问对象)中,在调用display()方法以显示所需格式的数据之前,它会检索所有这些必需的数据。
#2
0
I would do this in two queries: one query for to get all the posts of a particular item and other query for to get all the comments of all posts of a particular item. Some ActiveRecord implementations work the same.
我会在两个查询中执行此操作:一个查询以获取特定项目的所有帖子以及其他查询以获取特定项目的所有帖子的所有评论。一些ActiveRecord实现的工作方式相同。
#3
0
Assigning auto id to post and comment and also item and retrieving post and comment belongs to that item id will be best, i suppose.
我认为,将自动ID分配给帖子和评论以及项目和检索帖子和评论属于该项目ID是最好的。