CODEWITHSUNDEEP
mysqlsqlgroup-by
dav
dav

Reputation: 9267

Conditional count with group by in where clause

I have a simple messaging system - keeping all the messages in a single table. Each message can(and should) be associated with one of the 3 other tables, that represent some sections of the website. Here is the create table statement

CREATE TABLE `messages` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `from_user_id` int(11) DEFAULT NULL,
  `to_user_id` int(11) DEFAULT NULL,
  `message` text COLLATE utf8_bin,
  `table1_id` int(11) DEFAULT NULL,
  `table2_id` int(11) DEFAULT NULL,
  `table3_id` int(11) DEFAULT NULL,
  `is_unread` tinyint(1) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

For each message, from table1_id, table2_id and table3_id if some column has value, it means the rest 2 are null. Here is the sqlfiddle structure and example data: http://sqlfiddle.com/#!9/b98a2/1/0.

So, table1_id, table2_id and table3_id are sort of threads, that I am using when grouping - to show the list of messages. Here is my query

SELECT 
  id,
  table1_id,
  table2_id,
  table3_id,
  message,
  from_user_id,
  to_user_id,
  COUNT(table1_id) AS t1_count,
  COUNT(table2_id) AS t2_count,
  COUNT(table3_id) AS t3_count,
  MAX(CASE WHEN to_user_id = 10 AND is_unread = 1 THEN 1 END) AS is_unread,
  COUNT(CASE WHEN to_user_id = 10 THEN 1 END) AS inbox_count
FROM
  messages
WHERE to_user_id = 10 OR from_user_id = 10
GROUP BY table1_id,
  table2_id,
  table3_id
ORDER BY id DESC

and this is in sqlfiddle http://sqlfiddle.com/#!9/b98a2/2/0

This query works fine when I have to show all the messages, but if e.g. I want to show only inbox of the user with id = 10 I have to check the condition that for each thread there is at least one received message, so for that I tried to apply the condition AND inbox_count > 0, which resulted an error Unknown column 'inbox_count' in 'where clause'.

I am trying to list messages similar to gmail - showing the number of total messages(t1_count, t2_count or t3_count) per thread, that is why I can not remove the OR from_user_id = 10 part.

Why it can not find that column and how I can apply that condition to show the list of received(outgoing) only messages.

Upvotes: 3

Views: 1013

Answers (1)

jpw
jpw

Reputation: 44891

Unless I completely misunderstand your intent... If you want to filter with inbox_count > 0 to then I think you want to add a 

HAVING COUNT(CASE WHEN to_user_id = 10 THEN 1 END) > 0

after the group by clause. This would remove the "threads" that don't have any message with to_user = 10.

See this fiddle for and example.

Upvotes: 2

Related Questions