I guess I would go for an extra date field, called 'sort-by' or something.

If a comment is posted on a future post, it's sort-by would become the post date of the post + 1 tick. Each subsequent comment would become lastComment.sort-by + 1 tick (to maintain absolute ordering). Comments posted on released posts would have a sort-by equal to posted-at.

When fetching the recent comments you would sort on sort-by, but filter out any comment whose sort-by is in the future. When displaying the recent comments, you would still use the posted-at date.

I think I would create a extra date field, publicationdate for comments.

Then, at first time that post show at the blog(or rss), this field is filled(you just need to see if it is null and set DateTime.Now).

If you reschedule the post, the comment continue with that date and dont show at RSS again. But if you want this behavior, you just need to set null to comment publicationdate at reschedule.

Sorry for my bad english =/

Oh, the "order by" would be, PublicationDate(the new field), OriginallyPosted

You can order on computed values as well, so order on

WasCommentCreatedBeforePublish desc, Comment.CreateDateTime

With WasCommentCreatedBeforePublish = Comment.CreateDateTime < Post.PublishDateTime

Perhaps I'm oversimplifying it, but this seems like this is just a simple conditional order by. If the blog entry was posted publicly AFTER the comment, then the blog post date is used, otherwise use the date the comment was posted. A a secondary sort is needed in order to help sort the comments posted prior to the publish date.

var mostRecentComments = (from c in BlogComments orderby (c.BlogPost.PublishDate > c.CommentDate ? c.BlogPost.PublishDate : c.CommentDate) descending, c.CommentDate descending).Take(5);

You have 3 dates: blog posted, blog published to public, comment posted.

I don't see the need for a double order by. Just filter out any comments that are attached to an unpublished blog post and order the rest by comment posted date desc.

Do you show the unpublished recent comments to those privileged users? If so, then omit the filtering out of unpublished posts based on their rights.

Joe, The problem lies in the fact that at the moment a blog post becomes public, all the comments on it should appear at the top of the lists of recent comments. Event if such comments were actually posted last week.

So just sorting on comment posted date isn't going to cut it, because then all the old comments of the just-became-public blog post will never appear in the recent comments.

ahhh! I thought I had to be missing something.

So there are two groups, public and nonpublic (the group with Mike in?). Can't you just write a query for each group which runs against the set of posts that that group can access?

Well, it is especially confusing since you have my comment as being posted at 7/8/2011, 08:42PM on the screenshot, but then as 7/7/2011, 07:42 in the text, so I have no idea what the proper order should be.

I don't understand what the problem is. In your very last example (after the second screeshot) you just have comments listed chronologically.

So the logic is: if(IsPostHidden) ignore else show comments chronologically (ascending/descending)

Where is the problem?

Nice to see there is a special Ayende Inner Circle :)

The dates towards the end of the post for jdn is confusing as hell, but would this work?

The ordering is based on "how soon" somebody commented when the post was made visible to them. Inner circle guy Mike got to see and comment on the post say 10 mins after it was shared with him, where the less privileged Eager Joe posted 2 mins after it was made public.

So the ordering in SQL would look something like this (skipping SQL date functions but you get the idea)

order by case when c.comment_date < p.publish_date then p.publish_date + (c.comment_date - p.post_date) -- how soon would Mike have commented if he did not belong else c.comment_date end

Ajai

Just kidding about inner circle :) Look forward to see how you ended up solving this...

Isn't it just:

simply filtering out comments from unpublished posts (post.PublishTime < DateTime.Now) and then ordering by max(post.PublishTime, comment.PostedAt). I guess if the DBMS can't do these kind of sorts efficiently then an extra field is needed.

Why not just use a second property in your post document?

post: :unpublishedComments :date :comments :date

Your view can loop through unpublishedComments and then comments. Absolute order preserved. ;)

I am afraid I will have to confess a weakness of mine, but I found quite hard following this post, due its use of the middle endian date format.

I am also sorry to look as the usual preacher, as in my own blog I haven't really cared much about the date formats so far as well, but I am quite sure if my blog would be a technical one, and it is not, or if I would post something about dates, I would probably (and I really should have already) set the dates as YYYY-MM-DD (the ISO 8601 calendar date format), as I usually tend to do on my programming whenever possible.

Seems to me like what you're saying is you want all comments on published posts and if the comment's publish date is before the post's publication date then you want to use the post's publication date. Wouldn't that be:

from c in comments where c.post.isPublished order by max(c.post.publishDate, c.commentDate) take 5