0

I am implementing notification system. My table structure is somewhat like this..

id (auto increment primary key)
user_id (user id of the user whom the notification should be shown, int)
triggered_by (user who triggered the notification, int)
post_id (post for which the notification exists, int)
type (type of the notification, int)
seen (has the notification been read, bool)

When I am marking a notification as seen, I am using post_id and type, that means we can safely assume that if the row with largest id is seen, all the previous rows for that post_id and type would be seen.

now, I want to fetch rows combined with previous entries for that post_id and type, plus a count of rows registered for that post_id and type. My current query is

select max(x.id) as id, 
   x.post_id as post_id, 
   x.seen as seen, 
   x.user_id as user_id, 
   x.triggered_by as triggered_by, 
   x.type as type, 
   x.count as count
from (SELECT notification.id, 
       notification.post_id, 
       notification.user_id, 
       notification.triggered_by, 
       notification.type, 
       c.count,  notification.seen 
     FROM `notification`
         join (select post_id, type, count(id) as count 
               from notification group by post_id, type) c 
           on c.type=notification.type 
             and c.post_id=notification.post_id 
     Where notification.user_id=1) x 
Group by post_id 
Order by id desc limit 10

The problem with this query is that the 'group by' clause of the outer most query is returning any random row's 'seen' column where as I want it to return data other then count from the row with the largest id.

Charles Bretana
  • 143,358
  • 22
  • 150
  • 216
Sourabh
  • 1,757
  • 6
  • 21
  • 43
  • Please first check your query, you query doesn't look right. You have the group by in the end, but the top selective list has many column which is not in group by list, are you sure this query can run? – ljh Mar 20 '13 at 18:56
  • yes this query runs well. – Sourabh Mar 21 '13 at 03:09

1 Answers1

0

Try this:

 Select id, post_id, seen,
    user_id, triggered_by, type, 
   (Select Count(*) From notification
    Where type = N.Type
      And post_id = n.post_id) Count
 From Notification n
 where user_id = 1
 Order by id desc limit 10

Except what do you mean by "combined with previous entries for that post_id and type" ?? Are you saying that you want this query to combine in one row of output, values from different rows in the source table ? Like get the post_id from one row, but get the Triggered_By from some different earlier row?

Charles Bretana
  • 143,358
  • 22
  • 150
  • 216
  • By combined with previous entries is that all the previous entry for that post_id and type should be grouped and that count should be in column. – Sourabh Mar 21 '13 at 03:08