MySQL: Join number of rows of occurance along with result

Question

I am trying to build a simple private messaging system to my app. The way I have it set up each message has a thread_hash associated with it. Messages that are related to one another have the same thread_hash (messages that are replys to other messages all have the same thread_hash)

I am able to run a query that selects the row that was entered last from each group (of thread_hash's). What I want to accomplish is to return another column with the number of rows that have that particular thread_hash without making a separate query

I've created an SQL Fiddle with the query I'm using to generate the rows: http://sqlfiddle.com/#!2/1d8bd/4

The only information I have is the user's ID. I do not have the thread_hash, so this must be generated from the ID. I've tried playing around with some queries to generate the number of rows but my brain isn't working properly at this hour.

Answer 1

You can write your query this way:

SELECT
  thread_hash,
  from_user_id,
  mark_read,
  subject,
  SUBSTRING(message, 1, 65) as message,
  messages.time_stamp,
  cnt
FROM
  `messages`
  JOIN (SELECT MAX(messages.id) thead_id, COUNT(*) cnt
        FROM messages
        WHERE messages.to_user_id = 28
        GROUP BY thread_hash) thread_head
  ON `messages`.`id` = `thread_head`.`thead_id`
WHERE `to_user_id` =  '28'
ORDER BY `messages`.`time_stamp` ASC
LIMIT 20

Fiddle is here.

But I'm not sure if you need to count only the messages of user 28, or all messages. If you need to count all messages, you can rewrite your subquery this way:

(SELECT MAX(CASE WHEN messages.to_user_id = 28 THEN messages.id END) thead_id,
        COUNT(*) cnt
 FROM messages
 GROUP BY thread_hash) thread_head

Please see fiddle here.