Group By Rows next to current row

[unemployment]

I am in the process of creating a news feed.  The news feed will say something like... You have just updated your profile picture.  Every time a user updates their profile picture, a news feed row is added to the news feed.

 

The problem:

If a user uploads their picture three times in a row it will say...

 

You have just updated your profile picture.

You have just updated your profile picture.

You have just updated your profile picture.

 

Which looks terrible.  I can adjust the group by to concatenate the similar rows into one row, but then if I ever update my profile picture again, even when I don't have repeated actions it will continue to group those actions into one row.

 

Solution I need:

If the actions above or below a give row are the same, then group them together into one row.  If not don't group them. 

 

What I probably need is a way to group actions over a time interval.  If the same action occurs within 5 minutes of the first one, then group by.

 

Current Model:

 

You have just updated your profile picture.

You have just updated your profile picture.

You have just joined XYZ company.

You have just updated your profile picture.

You have just updated your profile picture.

 

New Result I Need:

 

You have just updated your profile picture.

You have just joined XYZ company.

You have just updated your profile picture.

 

Current Query:

 

SELECT

                users.id,

                users.firstname,

                users.lastname,

                users.username,

companies.companyid AS companyid,

                companies.industry AS industry,

                companies.stage AS stage,

                companies.companytag AS companytag,

'' AS gender,

                '' AS accounttype,

                companies.country,

                companies.state,

                companies.city,

                UNIX_TIMESTAMP(`company_actions`.`time`) AS `approve_date`,

                '' AS FeedId,

                companies.companyname AS FeedFirstName,

                '' AS FeedLastName,

                '' AS FeedUserName,

                GROUP_CONCAT(actions.actionsdescription SEPARATOR '~') AS `action_id`,

                GROUP_CONCAT(company_actions.details SEPARATOR '~') AS `details`

                FROM users

                INNER JOIN employees ON employees.userid = users.id AND users.accounttype IN (".implode(',',$user_types).")

                INNER JOIN companies ON employees.companyid = companies.companyid

                INNER JOIN company_actions ON company_actions.company_id = companies.companyid

                INNER JOIN actions ON company_actions.action_id = actions.id

                WHERE company_actions.time < '${time}'

            GROUP BY

users.id,

                users.firstname,

                users.lastname,

                users.username,

companyid,

                industry,

                stage,

                companytag,

                gender,

                accounttype,

                companies.country,

                companies.state,

                companies.city,

                approve_date,

                FeedId,

                FeedFirstName,

                FeedLastName,

FeedUserName

[Muddy_Funster]

what you want to do is a formatting issue, not a query issue.  could you post up your table structures please?

[unemployment]

what you want to do is a formatting issue, not a query issue.  could you post up your table structures please?

 

Tables 1 - actions

 

id, actiondescription

 

Table 2 - Company Actions

 

id, company_id, action_id, time, details

 

Table 3 - User Actions

 

id, user_id, action_id, time, details

 

The only data I need grouped by interval should be the user and company actions.  Everything else is fine.

 

 

[Muddy_Funster]

why do you have id and company_id/user_id fields in tables 2 and 3 and why oh why did you name a field with a reserved word (time)?

 

Basicly what you want to do is pull it out the database without the grouping, and then run conditional formating against your "time" field that only shows actions from either table if it has been within a set period, and is not a duplicate of the last.  There are a couple of ways of doing that in PHP, if that is what you are using.  What are you using to display the results of the query and how comfortable are you with that language?

 

That said, you could possably pull it off using a CASE clause in the select statement if you are really heart set on having it done in the SQL.

[unemployment]

why do you have id and company_id/user_id fields in tables 2 and 3 and why oh why did you name a field with a reserved word (time)?

 

Basicly what you want to do is pull it out the database without the grouping, and then run conditional formating against your "time" field that only shows actions from either table if it has been within a set period, and is not a duplicate of the last.  There are a couple of ways of doing that in PHP, if that is what you are using.  What are you using to display the results of the query and how comfortable are you with that language?

 

That said, you could possably pull it off using a CASE clause in the select statement if you are really heart set on having it done in the SQL.

 

I'd much rather prefer to do it in the sql.  When  I made tables I probably should have just labeled them all id and I didn't know time was a reserved word.... hmm the things you learn. I've never used a case statement and I'd have no idea where to start.  I don't know how you can compare rows and time intervals.  The reason why I'd rather do it in the sql is that my php is already set which I don't really want to touch.  Plus getting the proper sql would be a little more efficient. 

 

Can you give me a good example of how to use a case statement for this?

[Muddy_Funster]

Well the case is fairly complicated compared to what you are using just now.  it will require you to assign some varibles within the SQL.  Oh, and as far as efficiency is concerned, I doubt you would notice any difference at all.  The following code is not tested, and is only given as a guide for you to see how it could work.

<SET @c=0, @id=[value]   /*include this at the top of the SQL code where [value] is the id of the action that is being performed 
   - you will need to get this from the submit form or from an additional SELECT at this point */

/*the following should be put in place of your current field select for the action performed*/
CASE
WHEN TIMEDIFF(NOW(), table2.`time`) <= 00:05:00.000000 AND table1.id = @id THEN @c= @c++
ELSE (SELECT actiondescription FROM table1 WHERE id = @id)
END as commment
/*this should only display different actions that have happened within the last 5 minutes.*/