How to create a view of unread comments on own nodes for the user's profile page?

Question

I'd like to create a view for the user's profile page that displays all unread comments on the nodes he/she created. When I say 'unread', I mean that the page where the comment is situated (namely, the node's page) hasn't been visited by the user yet. In other words: when the user hasn't read a new comment, it will be displayed in the view; once the user has read it, it will disappear in the view.

I've already set up a view that simply displays all comments on the user's nodes, but I can't find an extra 'Filter criteria' or 'Relationship' that filters out the unread ones.

Can you help me?

---

Edit: I created a flag called 'Read' ('Reactie gelezen' in Dutch) with the Flag module and a rule 'Comment read' that automatically flags comments with 'Read' when they're viewed. I've also granted every user the permission to flag comments with the 'Read' flag (although they won't see the flag links above their comment of course).

My rule works perfectly and the flagging works manually too. The view however, doesn't work properly. These are my settings:

Both uid's were provided the default value 'User ID from URL'.

What did I do wrong?

Answer 1

The Flag module only keeps rows in the database for content where a flag is set. I.e. when something is not flagged, there will be no corresponding row in the database.

The problem is, that views will generate a query which looks something like this (for a user with uid=12 and a flag with fid=1):

SELECT cid FROM comment AS c
INNER JOIN node AS n ON c.nid = n.nid
LEFT JOIN flagging AS f ON c.cid = f.entity_id AND f.fid = '1'
WHERE n.uid = '12' AND f.uid = '12'

However in order to select all comments for a given user excluding those which are not flagged, you need an SQL subquery:

SELECT cid FROM comment AS c
INNER JOIN node AS n ON c.nid = n.nid
WHERE n.uid = '12' AND c.cid NOT IN (
    SELECT f.entity_id FROM flagging AS f
    WHERE f.fid = '1' AND f.uid = '12'
)

AFAIK Views has no built-in support for subqueries. I therefore recommend you to reverse the logic and implement the following:

• Add a comment-flag is_new
• Whenever a new comment is posted, execute a rule which sets this flag on behalf of the content author.
• Whenever a node is viewed by its author, execute a rule which removes all is_new-flags on the associated comments (this is essentially the same rule you already have, except that you need to reverse the flagging-logic).
• Configure the view just as you did before but remove the flagged=false filter, instead configure the flag-relationship such that only flagged content is included.