Count Unread Message

[needsomehelp]

EDITED:
I realise that what I was looking for is not practical or very easy to do. So will have to do this in two separate queries.

CREATE TABLE IF NOT EXISTS `itemComments` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `dateAdded` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `itemid` text NOT NULL,
  `userid` text NOT NULL COMMENT 'comment author',
  `comment` text NOT NULL,
  `ownerRead` tinyint(1) NOT NULL DEFAULT '0',
  `emailNotified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `reportedby` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=90 ;

INSERT INTO `itemComments` (`id`, `dateAdded`, `itemid`, `userid`, `comment`, `ownerRead`, `emailNotified`, `reportedby`) VALUES
(74, '2012-11-16 23:39:22', '383', 'ABC4EF', 'Great for parties and disco\\''s too!!!', 0, '0000-00-00 00:00:00', ''),
(88, '2012-12-08 09:46:41', '383', '1', '1', 0, '0000-00-00 00:00:00', ''),
(89, '2012-12-08 09:47:48', '383', '100', '2', 0, '0000-00-00 00:00:00', '');

What I wish to now have in the results is the userid's that own the items and the total number of unread comments for all of their items, in the first query.

Then in the second I would like returned userid's of all that have made comments and the total number of comments made after the posters last message. so if userA posts a message for item1 and userB posts a message for item1 then userA would have one message to view. if userC posts another message for item1 then userA would have two messages to view and userB would have one message to view, userC would have none for item1 as they posted last.

I think thats what I am after. but think this may be so complex if not impossible to query?

Can anyone suggest a better method/logic to do this in.

I am wanting to have a cronjob run every hour and if there are messages to view then each user will get an email to state this and a field in the users table called `commentEmailNotify` will be set to the last time() email sent. My code will then check before sending the email if they have had one already sent in the last 24 hours. no need to remind them of each message every hour!

Its just that query to get the userid and the total number of messages for each item they have commented in and if there are any replies made after their last message.
The total count is not really that important, but would be great to have, but even if i just get the user id to notify of replies that is a start.

[Old Pedant]

Well, for starters, you table design needs work.

NEVER use a TEXT column unless you *MUST*.

Certainly never for something like itemid or userid.

TEXT columns are VERY expensive to store in the DB *and* they are even worse for querying against. They can't be indexed, for example.

Anyway, are you looking for a list by both itemid and userid of how many have not been read? Or just for one specific itemid/userid?

[Old Pedant]

Oh...and don't be afraid of NULL columns!

It makes much more sense to have the [b]emailNotified[b] column there be a NULLable column that to have to provide an ugly '0000-00-00' fake date value!

It's faster to test a NULL value, too.

THat is:

SELECT * FROM table WHERE field IS NULL

is going to be a lot faster than

SELECT * FROM table WHERE field = '0000-00-00 00:00:00'

The latter is really ugly.

[Old Pedant]

I think I have your answer. You'll need to try it with some real data. After you fix your table design, one hopes.

I created a simplified version of your table with only the relevant fields.

mysql> select itemid, userid, dateAdded from itemComments order by itemid, dateAdded;
+--------+--------+---------------------+
| itemid | userid | dateAdded           |
+--------+--------+---------------------+
| 383    | ABC4EF | 2012-11-16 23:39:22 |
| 383    | 1      | 2012-12-08 09:46:41 |
| 383    | 100    | 2012-12-08 09:47:48 |
| 777    | 1      | 2011-01-01 00:00:00 |
| 777    | ABC4EF | 2012-01-01 00:00:00 |
| 777    | 2      | 2013-01-01 00:00:00 |
+--------+--------+---------------------+

And then here is the the query I came up with and its results:

mysql> SELECT C.itemid, C.userid, COUNT(*)
    -> FROM itemComments AS C,  (
    ->      SELECT itemid, userid, MAX(dateadded) AS lastComment
    ->      FROM itemComments
    ->      GROUP BY itemid, userid ) AS M
    -> WHERE C.itemid = M.itemid
    -> AND C.dateadded < M.lastcomment
    -> GROUP BY C.itemid, C.userid
    -> ORDER BY C.itemid, C.userid
    -> ;
+--------+--------+----------+
| itemid | userid | COUNT(*) |
+--------+--------+----------+
| 383    | 1      |        1 |
| 383    | ABC4EF |        2 |
| 777    | 1      |        2 |
| 777    | ABC4EF |        1 |
+--------+--------+----------+

As you can see, that is saying that (to pick one example);:

For itemid 777, there are two comments made by others after userid 1's last comment.

No?

[Old Pedant]

Whoops...code does not work if one user makes more than one comment on a single itemid.

Nuts. It counts the user's own later posts.

Will think on it more.

The queryy is trivial for a single itemid and single userid.

For example:

mysql> select COUNT(*) FROM itemcomments AS C,
    ->   (SELECT Max(dateadded) AS lastcomment FROM itemcomments where itemid = 383 and userid = 100 ) AS M
    -> WHERE C.itemid = 383 AND C.userid <> 100 AND C.dateadded > lastcomment;
+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+

[Old Pedant]

Well, I still haven't come up with a way to show you all users and all items.

But you *can* get a report on all itemid's for a single user.

I added some more data to my test table and then ran the query you will see below.

mysql> select itemid, userid, dateAdded from itemComments order by itemid, dateAdded;
+--------+--------+---------------------+
| itemid | userid | dateAdded           |
+--------+--------+---------------------+
| 383    | ABC4EF | 2012-11-16 23:39:22 |
| 383    | 1      | 2012-12-08 09:46:41 |
| 383    | 100    | 2012-12-08 09:47:48 |
| 383    | ABC4EF | 2012-12-09 00:00:00 |
| 383    | 1      | 2012-12-10 00:00:00 |
| 383    | 33     | 2012-12-13 00:00:00 |
| 383    | 44     | 2012-12-13 00:00:00 |
| 777    | 1      | 2011-01-01 00:00:00 |
| 777    | ABC4EF | 2012-01-01 00:00:00 |
| 777    | 2      | 2013-01-01 00:00:00 |
+--------+--------+---------------------+
10 rows in set (0.00 sec)

mysql> select C.itemid, COUNT(*) FROM itemcomments AS C,
    ->   (SELECT itemid, Max(dateadded) AS lastcomment FROM itemcomments where userid = 'ABC4EF' group by itemid ) AS M
    -> where C.itemid = M.itemid AND C.userid <> 'ABC4EF' AND C.dateadded > M.lastcomment
    -> group by C.itemid;
+--------+----------+
| itemid | COUNT(*) |
+--------+----------+
| 383    |        3 |
| 777    |        1 |
+--------+----------+
2 rows in set (0.02 sec)

It's correct. For userid ABC4EF there are indeed 3 comments after his last comment on itemid 383 but only 1 after his last comment on itemid 777.

Is this good enough? Or do you really need it for all userid's and all itemid's??

[Old Pedant]

And just to prove the point, here is the same thing for userid 100:

mysql> select C.itemid, COUNT(*) FROM itemcomments AS C,
    ->   (SELECT itemid, Max(dateadded) AS lastcomment FROM itemcomments where userid = '100' group by itemid ) AS M
    -> where C.itemid = M.itemid AND C.userid <> '100' AND C.dateadded > M.lastcomment
    -> group by C.itemid;
+--------+----------+
| itemid | COUNT(*) |
+--------+----------+
| 383    |        4 |
+--------+----------+
1 row in set (0.01 sec)