How to efficiently load data from a self related table

Question

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

Answer 1

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.