Order Group By in Activity Stream by Date MYSQL

Question

I tried a lot of solutions from here, but none of those solved my issue! I have the following table structure:

|Column|Type|
|------
|activity_id| int(10) PRIMARY
|user_id| int(10)
|type| varchar(255)
|activity| varchar(255)
|item_id| int(11)
|secundary_id| int(11)
|date| datetime

I'm building an Activity Stream, in this table i'll record like

"John (user_id) commented (activity) a post (type => item_id) 5 hours ago (date)"

I intend to do the following structure:

"John,Marie, and +2 commented a post 5 hours ago"

So far i was able to do this connection, but i can't successfully order the records by date. I tried some inner joins in the own table, but with that i was unable to retrieve the other activity types that wasn't grouped by (type,activity,item_id), for example:

"John ( user_id) posted (activity) a post(type => item_id ) 5 hours ago (date)"

When i use JOINS i only get the grouped by records. This is my actual query:

SELECT t.*, GROUP_CONCAT(DISTINCT user_id ) AS users FROM (
SELECT *
FROM activity
   ORDER BY date DESC 
)t
GROUP BY type, activity, item_id
ORDER BY date DESC

I already tried some solutions listed here: Select the 3 most recent records where the values of one column are distinct But when i join the tables, i lost the non grouped records! Does anyone have another thoughts? Thanks by now!

/** EDIT - RESULTS

activity_id     user_id     type    activity    item_id     secundary_id    date    users
865         32  update  comment     20  22  2013-10-29 15:03:49     32,34
858         36  update  post    20  NULL    2013-10-29 13:50:59     36
864         32  post    comment     2615    21  2013-10-29 14:55:58     32,34,36
856         36  post    comment     2616    14  2013-10-29 13:50:10     36,39,34,32
872         32  post    comment     2617    28  2013-10-29 15:46:20     32
852         34  post    post    2615    NULL    2013-10-29 13:47:25     34
854         32  post    post    2616    NULL    2013-10-29 13:49:13     32
870         32  post    post    2617    NULL    2013-10-29 15:36:57     32

That's what i get with the query above, but for example the item_id 2616 that have 4 user comments, are not ordering by the last user comment date, for example i have the following record:

activity_id user_id type activity item_id secundary_item date
856     36  post    comentar    2616    14  2013-10-29 14:05:10

His date is 14:05, but on my select i get another older comment date!

-- FIDDLE

http://sqlfiddle.com/#!2/1923d2/1

SOLVED by @Tom

It's a little hard to understand. Can you post some sample data, the results you are getting and the results you would like to get? — Tom, Oct 29 '13 at 18:12

Tom · Accepted Answer · 2013-10-29T19:20:17.253

3

If you want to get the most recent date, try this:

ORIGINAL

SELECT   activity_id, user_id, type, activity, item_id, secundary_id, MAX(date) date, 
         GROUP_CONCAT(DISTINCT user_id ) AS users 
FROM     activity
GROUP BY type, activity, item_id
ORDER BY date DESC

EDITED - Below will give you the most recent record for each grouping.

SELECT   activity_id, user_id, type, activity, item_id, secundary_id, date, 
         users

FROM     activity a

         JOIN ( SELECT   MAX(activity_id) max_activity_id,
                         GROUP_CONCAT(DISTINCT user_id ) AS users
                FROM     activity
                GROUP BY type, activity, item_id
              ) AS grp ON a.activity_id = grp.max_activity_id

GROUP BY date DESC

edited Oct 29 '13 at 19:20

answered Oct 29 '13 at 18:34

Tom

6,593
3
21
42

I'll try! I build this Fiddle with my structure:http://sqlfiddle.com/#!2/1923d2/1 – wiLLiamcastrO Oct 29 '13 at 18:37
That listed right the activity records, but i still geting some older comment (secundary_id) when i display! I mean the order of the activity is write, but not the comments, etc! – wiLLiamcastrO Oct 29 '13 at 18:44
Ok, so for each group you would like to show the values from the most recent record? – Tom Oct 29 '13 at 19:11
Would the record in each group with the MAX(activity_id) always be the most recent record? – Tom Oct 29 '13 at 19:16
Yes, i tried with the activity_id but i can't work around, but that's it! The activity_id will always be the most recent! – wiLLiamcastrO Oct 29 '13 at 19:33
@wiLLiamcastrO, how do you use the users column (last one) with GROUP_CONCAT values? – John Smith Dec 17 '15 at 13:59
It's just a comma separated list. It's informational. – Tom Dec 22 '15 at 15:01

Order Group By in Activity Stream by Date MYSQL

1 Answers1