Subsonic query, i suck!

Question

Hi all,

I'm using subsonic 2.2 in one of my projects. I'm have a comment section where i query one table called Comments. First I query all records with ParentId=0, and then in the foreach statement I query all records with ParentId=currentRecord.Id. Now I know this is a bad habit but I don't know how to get around this in a single query using SubSonic, maybe I'm missing something important here.

I hope someone can point me in the right direction. Thank you for your time!

Kind regards, Mark

     [WebMethod]
    public List<Comment> GetComments(int aid)
    {          

        DAL.CommentCollection coll = new DAL.CommentCollection();
        SubSonic.Query qry = new SubSonic.Query(DAL.Comment.Schema);
        qry.AddWhere(DAL.Comment.Columns.ArticleID, aid);
        qry.AddWhere(DAL.Comment.Columns.ParentID, 0);
        qry.AddWhere(DAL.Comment.Columns.IsActive, true);
        qry.AddWhere(DAL.Comment.Columns.IsDeleted, false);
        qry.ORDER_BY(DAL.Comment.Columns.CreatedOn, "Asc");
        qry.PageSize = Classes.Settings.Controls.Comments.GetCommentsPerPage();
        coll.LoadAndCloseReader(qry.ExecuteReader());
        foreach (DAL.Comment item in coll)
        {
            Comment c = new Comment();
            c.Date = Convert.ToDateTime(item.CreatedOn).ToLongDateString();
            c.UserName = item.User.UserName;
            c.FullText = item.FullText;
            c.Gravatar = Classes.Data.HashString(item.User.GravatarId);
            c.IsSub = false;
            c.CommentId = (int)item.CommentID;
            comments.Add(c);

            //Get replies
            GetReplies((int)item.CommentID, aid);
        }

        return comments;
    }

    private void GetReplies(int CommentId, int aid)
    {
        DAL.CommentCollection coll = new DAL.CommentCollection();
        SubSonic.Query qry = new SubSonic.Query(DAL.Comment.Schema);
        qry.AddWhere(DAL.Comment.Columns.ArticleID, aid);
        qry.AddWhere(DAL.Comment.Columns.ParentID, CommentId);
        qry.AddWhere(DAL.Comment.Columns.IsActive, true);
        qry.AddWhere(DAL.Comment.Columns.IsDeleted, false);
        qry.ORDER_BY(DAL.Comment.Columns.CreatedOn, "Asc");
        qry.PageSize = Classes.Settings.Controls.Comments.GetCommentsPerPage();
        coll.LoadAndCloseReader(qry.ExecuteReader());
        foreach (DAL.Comment item in coll)
        {
            Comment c = new Comment();
            c.Date = Convert.ToDateTime(item.CreatedOn).ToLongDateString();
            c.UserName = item.User.UserName;
            c.FullText = item.FullText;
            c.Gravatar = Classes.Data.HashString(item.User.GravatarId);
            c.IsSub = true;
            c.CommentId = (int)item.CommentID;
            comments.Add(c);
        }
    }

Answer 1

If all items are tied together by article ID, just load them all once and sort them in memory.

//Load all article comments from the Database
DAL.CommentCollection dbComments = new Select()
    .From(DAL.Comment.Schema)
        .Where(DAL.Comment.ArticleIDColumn).IsEqualTo(aid)
            .And(DAL.Comment.IsActiveColumn).IsEqualTo(true)
            .And(DAL.Comment.IsDeletedColumn).IsEqualTo(false)
        .OrderBy(DAL.Comment.Columns.ParentID, DAL.Comment.Columns.CreatedOn)
    .ExecuteAsCollection<DAL.CommentCollection>();

//Now sort all comments however needed
DAL.CommentCollection parents = new DAL.CommentCollection();
foreach (DAL.Comment parent in dbComments)
{
    if (parent.ParentID == 0)
    {
        parents.add(parent);
    }
    else
        break; //because of the way we ordered the items above, all parents should now be processed
}

//now loop through the parent collection and add children beneath parents to the final collection
foreach (DAL.Comment parent in parents)
{
    comments.add(parent);
    foreach (DAL.Comment possibleChild in dbComments)
    {
        if (possibleChild.ParentID > 0 && possibleChild.ParentID == parent.ID)
        {
            comments.add(possibleChild);
        }
    }
 }

 return comments;