Thoughts about separation of discussions and comments

R_J

/!\ Warning! Do not read this if you think that there is a hidden question inside of this posting or something like this. Sometimes I have to write my thoughts down to get them clear and that has been the case here...

I've not understood why there is this separation in Vanilla between discussions and comments. There is no need to make a difference between the first posting in the thread and all the others. So it was a design decision and it is one of the very few "features" of Vanilla that I dislike.
I'm no database expert, but I'd assume that this decision is bad for Vanillas perfomance, but that's just a feeling and I wouldn't even know how to proof that.

Well, that's just the preface and this shall be no complaint.

Somebody has asked about a "related-plugin" and I was thinking about how something like this could be achieved. My first thought was that searching only in the discussion and not in the comments would be senseless and so I would have to search in two tables which would make a ranking even harder. So you would have to build a keyword table which handles discussions and comments just the same.

I wasn't sure if I had talked about something like that in a discussion before and so I thought I'd go over the profile and browse my postings. Well, I could only browse my discussions or my comments. There again...

And so I thought how I could access all my discussions and comments (in theory). DiscussionModel->GetWhere(User = myself), I could do that, but there is no CommentModel->GetWhere! Darn, that's no way either. And if there would be a CommentModel->GetWhere, what if I would want to mix discussions and comments because I want to sort them any way? There might be a way to sort two big arrays in a more or less efficient way, but it couldn't be as good as a PostModel->GetWhere which does that job on the db side.

So what's left? A helper function I haven't found and I have to admit that I wouldn't know where to look? I have not seen any mix of comments and discussions except for the search results and there it is a union of two queries, so I don't think there is such a function. Having to drill down each and every discussion to get its comments would be performance horror if it is done by each user.

To me the best solution for this would be a view. Why I do not like it is that the permissions check has to be done by anyone who uses that view. I think permission checking must be part of the framework itself to make it a reliable tool and using your own views are an obstruction for that.

Very, very unsatisfying... :-|

x00

You should look up JOIN in mysql. Just becuase something isn't in a model does mean you can't query it, and model can be extended through magic methods. SQL despite is faults is very versatile, and this is bread an butter stuff.

Also you are wrong about performance, you won't see much difference. In fact you can better performance on discussion excerpts.

I think too often people rush to find a quick solution, when they get frustrated, rather then taking the time to understand.

Note sphinx is very powerful at indexing. "Related" type queries with "Like" or fulltext mysql would be very inefficient if it was being used every time you view a thread.

There is a reason this hasn't been done. That sort of querying is usually reserved fro search engines like sphinx rather than general db. They are different tools that do different things.

On less of course you are relating it to something like shared tags only.

R_J

Join? Do you think there is a function where joining info would return discussions as well as their comments? I think this can only be achieved by building a new query where you have to combine results with a union. And then everything must be copied from Discussion- or CommentModel and existing functions can not be reused. That's a pity. Building something like a PostModel with a simple Get function is no problem for me. I just wish, I didn't have to ;-)

hgtonight

SELECT d.Name AS Title,
  d.Body AS DiscussionBody,
  c.Body AS CommentBody
FROM `gdn_discussion` d
JOIN `gdn_comment` c ON c.DiscussionID = d.DiscussionID

This works for me. Or did I misunderstand the question?

R_J

No question and I already had that SQL ready:
SELECT d.Announce , d.Attributes , d.Body , d.CategoryID , d.Closed , NULL AS CommentID , d.CountBookmarks , d.CountComments , d.CountViews , NULL AS DateDeleted , d.DateInserted , d.DateLastComment , d.DateUpdated , NULL AS DeleteUserID , d.DiscussionID , NULL AS Flag , d.ForeignID , d.Format , d.InsertIPAddress , d.InsertUserID , 0 AS IsComment , 1 AS IsDiscussion , d.LastCommentID , d.LastCommentUserID , d.Name , d.RegardingID , d.Score , d.Sink , d.Tags , d.Type , d.UpdateIPAddress , d.UpdateUserID FROM V20188_Discussion d UNION ALL SELECT dis.Announce , c.Attributes , c.Body , dis.CategoryID , dis.Closed , c.CommentID , dis.CountBookmarks , dis.CountComments , dis.CountViews , c.DateDeleted , c.DateInserted , dis.DateLastComment , c.DateUpdated , c.DeleteUserID , c.DiscussionID , c.Flag , dis.ForeignID , c.Format , c.InsertIPAddress , c.InsertUserID , 1 AS IsComment , 0 AS IsDiscussion , dis.LastCommentID , dis.LastCommentUserID , dis.Name , dis.RegardingID , c.Score , dis.Sink , dis.Tags , dis.Type , c.UpdateIPAddress , c.UpdateUserID FROM V20188_Discussion dis LEFT OUTER JOIN V20188_Comment c ON dis.DiscussionID = c.DiscussionID

But if I use something like that, I'll have to do something like that by myself:
public function DiscussionSummaryQuery($AdditionalFields = array(), $Join = TRUE) { // Verify permissions (restricting by category if necessary)
Verifying permissions shouldn't be done by myself.

Something like that is great:
$this->FireEvent('AfterDiscussionSummaryQuery');
And I wished I could use some Event for my purpose

In short: I'd like to reuse Vanilla for that, but there seems no way for that. That's it already.

x00

Permissions are done on a category basis.

If you use CategoryModel::Categories() you can traverse, each has a PermsDiscussionsView, PermsDiscussionsAdd,PermsDiscussionsEdit and PermsCommentsAdd value.

You can store the relevant ids and use them in your query.

R_J

Sounds promising; I'll look for that