如何有效地从自相关表中加载数据 [英] How to efficiently load data from a self related table

问题描述

请考虑建立论坛的以下要求App

父帖

   -  Child Post1 
 
  -  Child Post1-1 
  -  Child Post1-2 
  -  Child Post1-2-1 
  -  Child Post2 
 -  Child Post 
 
  -  Child Post3 
  

表结构

tblPost
-

• PostId






• 标题


• 发布内容


• 
  
  

  =====================

  
  
  

  我可以检索这种数据使用递归CTE。我不确定这是最好的方法。

  
  
  

  问题

  
  
  
  
  
  
  

  • 有没有更好的方法使用ORM加载这些数据？

    li>
    

  • 如果我们走SQL路线，将这些数据加载到类中的最佳方法如下所示：

      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;} 
    } 
      

  
  

  • 数据说使用剃刀语法查看视图

  
  
  
  

  解决方案

  您对您的改进您的当前数据库模式的建议，其中您基本上有一个 post_id 和一个 child_post_id 列以执行层级关系。

  
  
  

  让我们继续：

  
  

  检索此数据的最佳方法使用SQL？

  
  

  我建议您查看来设计数据库表：

    CREATE TABLE posts（id int primary key，left int，right int，title nvarchar（100））; 
    

  并且您已填写了帖子：

    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'）; 
    

  现在您可以抓取它们了。

  
  
  

  但一如既往地做某事，你描述你想做什么。这是：你定义一个合同：

    public interface IPostsRepository 
   {
   Post Post 
  } 
    

  现在您进入 。在这种情况下，我们将使用纯ADO.NET来查询数据库并构建Post对象。我们将使用迭代算法与堆栈构建树，但您也可以使用递归算法：

    public class PostsRepositoryAdoNet： IPostsRepository 
   {
   private readonly string _connectionString; 
   public PostsRepositoryAdoNet（string connectionString）
   {
   _connectionString = connectionString; 
  } 
   
  私人类标量
   {
   public int Depth {get;组; } 
   public Post Post {get;组; } 
  } 
   
   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，posts AS parent 
   WHERE p.left BETWEEN parent.left和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））
  } 
  }; 
  } 
  } 
    

  现在我们有了这个库，一起：

    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 接口的相应存储库。

  
  

  Consider the following requirement for building a forum App

  Parent Post

  - Child Post1
  
      - Child Post1-1
      - Child Post1-2
          - Child Post1-2-1
  - Child Post2
      - Child Post
  
  - Child Post3
  

  Table Structure

  tblPost -

  • PostId
  • ChildPostId
  • Title
  • Post Content
  • UserName

  =====================

  I can retreive this kind of data using a recursive CTE. I am not sure this is the best approach.

  Questions

  • What is the best way to retreive this data using SQL?

  • Is there a better way to load this data using an ORM?

  • 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??

  解决方案

  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:

  What is the best way to retreive this data using 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:

  Is there a better way to load this data using an 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; }
  }
  

  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);
      }
  }
  

  and then you would have an ~/Views/Home/Index.cshtml view:

  @model Post
  <ul>
      @Html.DisplayForModel()
  </ul>
  

  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:

  ---

  UPDATE:

  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();
  }
  

  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);
      }
  }
  

  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屋！