Don't display duplicate entries in River

[mikehedman]

For example: New users often update their profile a number of times on their first day - and a River entry shows up for each time they save. I would like the River to only show one "has updated their Profile" message per day.

My suggestion is extract only one unique row per day (the most recent) in the SELECT query in the get_river_items() function. Something like:

$sql = "select r.id,r.type,r.subtype,r.action_type,r.access_id,r.view,r.subject_guid,r.object_guid,r.annotation_id,r.posted" .
" from {$CONFIG->dbprefix}river r" .
" inner join (" .
"   select type,subtype,action_type,access_id,view,subject_guid,object_guid,annotation_id,max(posted) as max_posted from {$CONFIG->dbprefix}river" .
"   group by type,subtype,action_type,access_id,view,subject_guid,object_guid,annotation_id" .
" ) rr on r.type=rr.type and r.subtype=rr.subtype and r.action_type=rr.action_type and r.access_id=rr.access_id and r.view=rr.view and r.subject_guid=rr.subject_guid and r.object_guid=rr.object_guid and r.annotation_id=rr.annotation_id and r.posted=rr.max_posted" .
" where {$whereclause} order by posted desc limit {$offset},{$limit}";

Sorry, it's a bit verbose :)

Mike

[mikehedman]

Thanks Brett for the cleanup ;)

I had neglected in my original code to include the "one per day" part - it was only doing the most recent ever. I think it's good to show one per day: if a user changes their profile on Tuesday, and then again on Wednesday, I think the river should show both records. Here's a revised query with this enacted, with an additional " date(from_unixtime(posted)) " in the GROUP BY clause:

     $sql = "select
     r.id,r.type,r.subtype,r.action_type,r.access_id,r.view,r.subject_guid,r.object_guid,r.annotation_id,r.posted" .
     " from {$CONFIG->dbprefix}river r" .
     " inner join (" .
     "   select type,subtype,action_type,access_id,view,subject_guid,object_guid,annotation_id,max(posted) as max_posted from {$CONFIG->dbprefix}river" .
     "   group by type,subtype,action_type,access_id,view,subject_guid,object_guid,annotation_id,date(from_unixtime(posted))" .
     " ) rr on r.type=rr.type and r.subtype=rr.subtype and  r.action_type=rr.action_type and r.access_id=rr.access_id and  r.view=rr.view and r.subject_guid=rr.subject_guid and  r.object_guid=rr.object_guid and r.annotation_id=rr.annotation_id and  r.posted=rr.max_posted" .
     " where {$whereclause} order by posted desc limit {$offset},{$limit}";

And a disclaimer - this has been tested for about 4 minutes only, and only with a few kinds of river entries.
Mike

[cash]

So I haven't read through that query - and I'm not sure I want to ;)

What we did on our site is identified the messages that should only appear once (like update profile) and we delete the old ones.

There is also a riverdashboard plugin in the plugins repository that rolls up river activity by entity but I think it has issues.

[mikehedman]

Cash is right - better to turn the water off, than keep mopping up the floor. I will look into how hard it would be to modify the add to river functionality to give it the capability to go whack prior related river entries. The query would be MUCH simpler this time :) since at that moment we have access to the components of the specific river items we're looking to delete.
Mike

[cash]

To summarize:

• Currently you can delete older entries (such as profile updates). This doesn't solve the issue of a person friending 30 people and clogging up the river
• There is a prototype implementation of a roll-up capability in the riverdashboard plugin in the plugins repository but it has some odd side effects. There is a ticket for that.

I'm closing this for now in favor of #2415