如何有效地从自身相关的表加载数据 [英] How to efficiently load data from a self related table
问题描述
考虑以下要求建立一个论坛，应用
Consider the following requirement for building a forum App
父帖子
- Child Post1
- Child Post1-1
- Child Post1-2
- Child Post1-2-1
- Child Post2
- Child Post
- Child Post3
表结构
tblPost
-
Table Structure
tblPost -
- 帖子ID
- ChildPostId
- 标题
- 张贴内容
- 用户名
=====================
=====================
我可以retreive使用递归CTE这样的数据。我不知道这是最好的办法。
I can retreive this kind of data using a recursive CTE. I am not sure this is the best approach.
问题
-
什么是使用SQL中检索这些数据的最佳方式？
What is the best way to retreive this data using SQL?
有没有一种更好的方式使用ORM加载这些数据？
Is there a better way to load this data using an ORM?
如果我们去的SQL路线，什么是最好的方式来加载这些数据转化为像一个类，如下：
If we go the SQL route, what is the best way to Load this data into a class like shown below:
public class Post {
public int PostId {get;set;}
public string PostTitle {get;set;}
public string PostContent {get;set;}
public string PostedBy {get;set;}
public IEnumerable<Post> ChildPosts {get;set;}
}
如何显示这种数据说使用视图??
How about displaying this kind of data say using the razor syntax for a view??
推荐答案
根据你的评论是开放的关于改善中，你基本上你当前的数据库架构的建议在 POST_ID 和 child_post_id 列执行的层次关系。
According to your comment you are open to suggestions about improving your current database schema in which you basically have a post_id and a child_post_id columns to perform the hierarchical relationship.
因此，让我们继续：
So let's proceed:
什么是使用SQL中检索这些数据的最佳方式？
What is the best way to retreive this data using SQL?
我建议你考虑看看在以下这说明一个很不错的文章技术为在一个非常有效的方式管理这些分层数据。它使用的的嵌套集模型的您在其中定义设置左，右节点，然后你可以用一个SQL查询来构建整个树：
I would recommend you taking a look at the following article which illustrates a very nice technique for managing such hierarchical data in a very efficient way. It uses the The Nested Set Model in which you define sets with left and right nodes and then you are able to build the entire tree with a single SQL query:
有没有一种更好的方式使用ORM加载这些数据？
Is there a better way to load this data using an ORM?
有办法做这个使用ORM如NHibernate和EF但我会离开这个下一次。你可能会考虑拆分您的问题分成多个做题作为主题是相当广阔的。如果你学会如何做到这一点使用普通ADO.NET您将收集更好的了解所涉及的基本技术，这样明天你决定使用这样你就已经知道如何查找在高效的查询的顺序是什么。一个ORM
There are ways doing this using an ORM such as NHibernate and EF but I will leave this for next time. You might consider splitting your questions into multiple SO questions as the subject is quite broad. If you learn how to do this using plain ADO.NET you will gather far better understanding of the underlying techniques that are involved so that tomorrow you decide to use such an ORM you will already know what to look for in order of efficient queries.
如何显示这种数据说使用剃刀语法
视图??
How about displaying this kind of data say using the razor syntax for a view??
一旦你已经构建了您的分层模型这是非常简单的。所有你需要做的是定义为发表键入自定义显示模板中，你会调用显示模板所有子文章。
Once you have constructed your hierarchical model it's extremely simple. All you have to do is to define a custom display template for the Post type in which you would invoke the display template for all child posts.
因此，假设下面的模型：
So assuming the following model:
public class Post
{
public int PostId { get; set; }
public string PostTitle { get; set; }
public IEnumerable<Post> ChildPosts { get; set; }
}
和以下控制器（在我显然已经很难codeD值，但阅读我在文章的开头链接到的教程后，你将能够建立这种模式用一个SQL查询）：
and the following controller (in which I obviously have hardcoded the values but after reading the tutorial I have linked to in the beginning of my post you will be able to construct this model with a single SQL query):
public class HomeController : Controller
{
public ActionResult Index()
{
// Hardcoding the model here, but you could use the
// Nested Set Model technique I have linked to
// in order to build this model from your database
var post = new Post
{
PostId = 1,
PostTitle = "Parent Post",
ChildPosts = new[]
{
new Post
{
PostId = 2,
PostTitle = "Child Post 1",
ChildPosts = new[]
{
new Post
{
PostId = 3,
PostTitle = "Child Post 1-1",
ChildPosts = new[]
{
new Post
{
PostId = 4,
PostTitle = "Child Post 1-2-1"
}
}
},
new Post
{
PostId = 5,
PostTitle = "Child Post 1-2"
},
}
},
new Post
{
PostId = 6,
PostTitle = "Child Post 2",
ChildPosts = new[]
{
new Post
{
PostId = 7,
PostTitle = "Child Post"
}
}
},
new Post
{
PostId = 8,
PostTitle = "Child Post 3"
},
}
};
return View(post);
}
}
，然后你将有一个〜/查看/主页/ Index.cshtml 查看：
@model Post
<ul>
@Html.DisplayForModel()
</ul>
，当然相应的显示模板（〜/查看/主页/ DisplayTemplates / Post.cshtml ），这将是在我们的案例呈现完整的树递归：
and of course a corresponding display template (~/Views/Home/DisplayTemplates/Post.cshtml) which will be recursive in our case to render the full tree:
@model Post
<li>
@Html.DisplayFor(x => x.PostTitle)
<ul>
@Html.DisplayFor(x => x.ChildPosts)
</ul>
</li>
和过程的最终结果是人们所预料的：
and of course the final result is what one might expect:
更新：
由于在评论部分要求这里的人们可能会如何填充Post模型的一个例子。让我们假设你已经跟着组嵌套模式来设计数据库表：
As requested in the comments section here's one example of how one might populate the Post model. Let's assume that you have followed the nested set model to design your database table:
CREATE TABLE posts (id int primary key, left int, right int, title nvarchar(100));
和你已经与职位填补它：
and that you have filled it with the posts:
INSERT INTO posts (id, left, right, title) VALUES (1, 1, 16, 'Parent Post');
INSERT INTO posts (id, left, right, title) VALUES (2, 2, 9, 'Child Post1');
INSERT INTO posts (id, left, right, title) VALUES (3, 3, 4, 'Child Post1-1');
INSERT INTO posts (id, left, right, title) VALUES (4, 5, 8, 'Child Post1-2');
INSERT INTO posts (id, left, right, title) VALUES (5, 6, 7, 'Child Post1-2-1');
INSERT INTO posts (id, left, right, title) VALUES (6, 10, 13, 'Child Post2');
INSERT INTO posts (id, left, right, title) VALUES (7, 11, 12, 'Child Post');
INSERT INTO posts (id, left, right, title) VALUES (8, 14, 15, 'Child Post3');
现在，你可以获取它们。
Now you could fetch them.
然而，总是其实之前的做的东西你描述你想要做什么。那就是：你定义一个合同：
But as always before actually doing something you describe what you want to do. That is: you define a contract:
public interface IPostsRepository
{
Post GetPost();
}
现在你到的做的。在这种情况下，我们会使用纯ADO.NET来查询数据库并建立了邮政对象。我们将使用迭代算法用栈来构建树，但你也可以使用一个递归算法：
Now you get to the doing. In this case we will use plain ADO.NET to query the database and built the Post object. We will use an iterative algorithm with a stack to build the tree but you could also use a recursive algorithm:
public class PostsRepositoryAdoNet: IPostsRepository
{
private readonly string _connectionString;
public PostsRepositoryAdoNet(string connectionString)
{
_connectionString = connectionString;
}
private class Scalar
{
public int Depth { get; set; }
public Post Post { get; set; }
}
public Post GetPost()
{
using (var conn = new SqlConnection(_connectionString))
using (var cmd = conn.CreateCommand())
{
conn.Open();
cmd.CommandText =
@"
SELECT p.id, p.title, (COUNT(parent.title) - 1) AS depth
FROM posts AS p, posts AS parent
WHERE p.left BETWEEN parent.left AND parent.right
GROUP BY p.title
ORDER BY p.left;
";
using (var reader = cmd.ExecuteReader())
{
if (!reader.Read())
{
return null;
}
var nodes = new Stack<Post>();
var scalar = FromDataReader(reader);
var rootNode = scalar.Post;
int currentDepth = 0;
var currentNode = rootNode;
while (reader.Read())
{
var depth = reader.GetInt32(reader.GetOrdinal("depth"));
if (depth > currentDepth)
{
nodes.Push(currentNode);
currentDepth = depth;
}
else if (depth < currentDepth)
{
while (depth < currentDepth)
{
--currentDepth;
nodes.Pop();
}
}
scalar = FromDataReader(reader);
currentNode = scalar.Post;
var p = nodes.Peek();
if (p.ChildPosts == null)
{
p.ChildPosts = new List<Post>();
}
p.ChildPosts.Add(currentNode);
}
nodes.Clear();
return rootNode;
}
}
}
private Scalar FromDataReader(DbDataReader reader)
{
return new Scalar
{
Depth = reader.GetInt32(reader.GetOrdinal("depth")),
Post = new Post
{
PostId = reader.GetInt32(reader.GetOrdinal("id")),
PostTitle = reader.GetString(reader.GetOrdinal("title"))
}
};
}
}
现在，我们有这个资源库，我们可以把拼在一起：
Now that we have this repository we could bring the pieces together:
public class HomeController : Controller
{
private readonly IPostsRepository _repository;
public HomeController(IPostsRepository repository)
{
_repository = repository;
}
public ActionResult Index()
{
var post = _repository.GetPost();
return View(post);
}
}
和最后一部分就是配置自己喜欢的依赖注入框架注入的存储库所需的实现因为我们只有一个，到目前为止，这将是 PostsRepositoryAdoNet 。如果明天你决定要切换到ORM所有你所要做的就是写贯彻 IPostsRepository 界面相应的信息库。
and the last part is to configure your favorite Dependency Injection framework to inject the desired implementation of the repository and since we have only one so far that would be PostsRepositoryAdoNet. And if tomorrow you decide to switch to an ORM all you have to do is to write the corresponding repository implementing the IPostsRepository interface.
这篇关于如何有效地从自身相关的表加载数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
