According to your comment, you can suggest suggestions for improving your current database schema, in which you usually use the post_id and child_post_id to perform hierarchical relationships.
So go on:
What is the best way to get this data using SQL?
I would recommend you take a look at the following article , which illustrates a very good way to efficiently manage such hierarchical data. It uses a nested set model in which you define sets with left and right nodes, and then you can build the whole tree with a single SQL query:
Is there a better way to load this data using ORM?
There are ways to do this with ORM like NHibernate and EF, but I will leave it next time. You might consider dividing your questions into several questions, since the topic is quite broad. If you learn how to do this using simple ADO.NET, you will understand a much better understanding of the basic techniques that are involved so that tomorrow you decide to use such an ORM, you already know what to look for in order of effective queries.
How about displaying this kind of data using razor syntax for view
Once you have built your hierarchical model, it is very simple. All you have to do is define a custom display template for the Post type, in which you will reference the display template for all child messages.
So, suppose 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 hardcoded the values, but after reading the tutorial that I contacted at the beginning of my post, you can build 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 will have the view ~/Views/Home/Index.cshtml :
@model Post <ul> @Html.DisplayForModel() </ul>
and, of course, the corresponding display template ( ~/Views/Home/DisplayTemplates/Post.cshtml ), which in our case will be recursive to display the full tree:
@model Post <li> @Html.DisplayFor(x => x.PostTitle) <ul> @Html.DisplayFor(x => x.ChildPosts) </ul> </li>
and, of course, the end result is what you would expect:
UPDATE:
As indicated in the comments section, here is an example of how you can populate the Post model. Suppose you have implemented a nested recruitment model to design your database table:
CREATE TABLE posts (id int primary key, left int, right int, title nvarchar(100));
and that you filled it with messages:
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 can get them.
But, as always, before you do something, you describe what you want. That is: you define the contract:
public interface IPostsRepository { Post GetPost(); }
Now you get to the point. In this case, we will use simple ADO.NET to query the database and build the Post object. We will use an iterative algorithm with a stack to build a tree, but you can 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 can collect the fragments:
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 set up your favorite Injection Dependency framework to introduce the desired repository implementation, and since we only have one that would be PostsRepositoryAdoNet . And if tomorrow you decide to switch to ORM, all you have to do is write the appropriate repository that implements the IPostsRepository interface.