how best to count comments on items.

[needsomehelp]

I have been playing around with code and queries and now think i have the writeup that will help explain what i was after. I have previously posted and it is not getting replies due to the complex replies i was giving. this post should explain exactly what i am after.



// return userid of all users

first check for comments which the itemOwner will be notified of.

// count all comments for all of the itemOwner items, but only count comments after their own comment or reply, if any, for each item they own. if no comment by the itemOwner then count all messages. Add this count to the ownerCommentCount.

// count all comments where a user, (NOT itemOwner), has taken part in any items comment conversation, but only count comments after their own reply for all items that they have taken part in. Add this count to the array for that notOwnerCommentCount.

// echo results to make sure they are correct.

the items table has these fields...

id, itemid, userid



lets say this is the table of comments...
userid here is the author of the comment.

id     dateAdded                            itemid                                                                 userid           comment
73     2012-11-15 09:47:48     383                               100                  owner emma(ABC3EF) comment by jason(100)
88     2012-12-08 09:46:41     383                               1                   owner emma(ABC3EF) comment by peter(1)

90     2012-12-09 09:38:52     1637ebf4b8ad591226e830c932a2a1a9    SDF5gH         owner emma(ABC3EF) comment by simon(SDF5gH)

91     2012-12-09 09:50:26     3efcefcc77242c439591d4d2f6ce9168    ABC4EF         owner jason(100) comment by emma(ABC3EF)
92     2012-12-09 09:59:26     3efcefcc77242c439591d4d2f6ce9168    1                    owner jason(100) comment by peter(1)
93     2012-12-09 10:00:37     3efcefcc77242c439591d4d2f6ce9168    100              owner jason(100) comment by jason(100)
94     2012-12-09 10:20:50     3efcefcc77242c439591d4d2f6ce9168    100              owner jason(100) comment by jason(100)
96     2012-12-09 11:00:50     3efcefcc77242c439591d4d2f6ce9168    SDF5gH         owner jason(100) comment by simon(SDF5gH)

the userid and which message they would get counted in to be told of.
‘nc’ means that that comment is not counted as the itemOwner made the comment, so the owner will not get notified of their own comments.

commentid      emma      jason       simon          peter

73              1           -          -              -
88              1           1         -              -

90              1           1          -             -

91             -             nc          -             -
92             1             nc         -             -
93             1             nc          -             1 
94             1            nc         -             1
96             1            1          -             1

should output something like...

    userid        ownerCommentCount    notOwnerCommentCount
    1                 0                        3
    100               1                        1
    ABC4EF            3                        4
    SDF5gH            0                        0

[Old Pedant]

Your comments table is not complete.

You have a line like this:

73     2012-11-15 09:47:48     383    100       owner emma(ABC3EF) comment by jason(100)

But WHERE in that data does it say the Emma (or userid ABC3EF) is the owner of that itemid???

In your prior posts, the "owner" of an itemid could be determined. Can't be done with this limited data.

Back to the drawing board.

[Old Pedant]

Actually, if I go look at your prior post, I guess the problem is just that you forgot to put in the ORIGINAL post for each itemid in this forum thread.

I've added on comment per itemid, to establish an original post. I also shortened your crazy itemid values to 3 characters for readability.

And got rid of your totally useless commentid field (at least I sure can't find any use for it).

SO now I have this:

mysql> select * from comments order by itemid,dateAdded;
+--------+---------------------+--------+---------------------------------------------+
| itemid | dateAdded           | userid | comment                                     |
+--------+---------------------+--------+---------------------------------------------+
| 163    | 2012-12-09 01:00:00 | ABC3EF | originsl comment so owner is emma           |
| 163    | 2012-12-09 09:38:52 | SDF5gH | owner emma(ABC3EF) comment by simon(SDF5gH) |
| 383    | 2012-11-10 00:00:00 | ABC3EF | original comment so owner is emma           |
| 383    | 2012-11-15 09:47:48 | 100    | owner emma(ABC3EF) comment by jason(100)    |
| 383    | 2012-12-08 09:46:41 | 1      | owner emma(ABC3EF) comment by peter(1)      |
| 3ef    | 2012-01-01 00:00:00 | 100    | original comment so ownere is jason         |
| 3ef    | 2012-12-09 09:50:26 | ABC4EF | owner jason(100) comment by emma(ABC3EF)    |
| 3ef    | 2012-12-09 09:59:26 | 1      | owner jason(100) comment by peter(1)        |
| 3ef    | 2012-12-09 10:00:37 | 100    | owner jason(100) comment by jason(100)      |
| 3ef    | 2012-12-09 10:20:50 | 100    | owner jason(100) comment by jason(100)      |
| 3ef    | 2012-12-09 11:00:50 | SDF5gH | owner jason(100) comment by simon(SDF5gH)   |
+--------+---------------------+--------+---------------------------------------------+

Will this work?

[Old Pedant]

So the first task is to find the OWNER of each itemid.

This is easy enough:

SELECT C.itemid, C.userid 
FROM comments AS C,
     ( SELECT itemid, MIN(dateAdded) AS firstpost FROM comments GROUP BY itemid ) AS M
WHERE C.itemid = M.itemid
AND C.dateAdded = M.firstpost;

Which gives us:

+--------+--------+
| itemid | userid |
+--------+--------+
| 383    | ABC3EF |
| 163    | ABC3EF |
| 3ef    | 100    |
+--------+--------+

You might even want to turn that into a pair of views:

mysql> CREATE VIEW firstPosts AS
    -> SELECT itemid, MIN(dateAdded) AS firstpost FROM comments GROUP BY itemid;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE VIEW getItemOwners AS
    -> SELECT C.itemid, C.userid
    -> FROM comments AS C, firstPosts AS F
    -> WHERE C.itemid = F.itemid
    -> AND C.dateAdded = F.firstPost
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from getItemOwners;
+--------+--------+
| itemid | userid |
+--------+--------+
| 383    | ABC3EF |
| 163    | ABC3EF |
| 3ef    | 100    |
+--------+--------+

But this is just a start.

You now have to *ALSO* find the *LAST* POST by each userid for each itemid. Because, of course, you only want to count comments that occur *AFTER* the last post by each user.

Truthfully, part of the problem is that your database organization is probably not ideal for something like this.

[needsomehelp]

i have even tried to scrap the query only method and try a basic counter system using PHP and simple-ish queries.

but again just trying to understand the right way to do this is very difficult.

[Old Pedant]

Well, for starters, you could add a column to your table that holds the "owner" of the thread. Presumably, the first person to post in the thread.

That gets rid of the need for the code I had to write in post #4, above.

As I noted in your prior thread, doing all this for a *single* userid seems possible. In fact, I think I had the answer for same in that other thread.

Doing it for all users is not so easy.

Where is your priority, here?