[buddypress-trac] [BuddyPress Trac] #3794: Deleted activity items remain favourited

[buddypress-trac]

#3794: Deleted activity items remain favourited
--------------------------+--------------------
 Reporter:  ewebber       |       Owner:
     Type:  defect (bug)  |      Status:  new
 Priority:  normal        |   Milestone:  2.2
Component:  Activity      |     Version:  1.5.1
 Severity:  normal        |  Resolution:
 Keywords:  has-patch     |
--------------------------+--------------------

Comment (by boonebgorges):

 > To be completely sure, maybe we could compare what's best in terms of
 query performance between calculating the number of favorites querying the
 user's 'activity_favorite' type VS using a usermeta for the count and
 making sure once an activity is deleted,

 Yes, I think we need some actual benchmarks here. Take the two cases:

 1. We store the count in usermeta. The activity item is deleted. We now
 have to do this:

 {{{
 foreach ( $users_who_favorited as $user_id ) {
     reset_favorite_count_for_user( $user_id );
 }
 }}}

 If the item has been favorited by 100 or 1000 people, this is an extremely
 expensive operation. (Though, granted, it only happens rarely.)

 2. We get favorite counts dynamically from the activity table (or wherever
 it's stored) when the page is rendered. We no longer have to do the
 occasional very-resource-intensive task described in 1, but now we have
 added overhead to every page: instead of automatically fetching all
 relevant usermeta when the page is loaded and having the cached count as
 part of that (one query), we have at least one query for each user (twenty
 queries).

 I would like to see real numbers for these cases on sites with user counts
 in the tens of thousands and activity tables in the hundreds of thousands
 or millions.

 Side note - I think we can do a bit better with 2: a function that
 prefetches counts for all users in the stream with a single DB query.
 Something like: "SELECT item_id, user_id FROM wp_bp_activity WHERE type =
 'activity_favorite' AND user_id IN (1,6,10,30)", then parse it out and add
 it to the cache. I'm fairly certain this will be faster than what imath's
 currently doing, but again, it would be helpful to benchmark a bit.

 As for the question of storage, we seem to be talking about three options:

 1. Standalone table (wp_bp_favorites or something like that)
 2. Activity table (as in imath's patch)
 3. Generic relationship table

 Like r-a-y and jjj, I like option 3 from the point of flexibility and
 elegance. But I am also concerned about duplicating effort, both with what
 WP may one day create and what we already have. We already have a couple
 of relationship tables: `wp_bp_groups_members`, `wp_bp_friends`,
 `wp_bp_user_blogs`, and - most importantly - `wp_bp_activity`. Our
 activity table is already quite generic (it has a `type` column! and
 `item_id` and `secondary_id`!). We should only go to something even more
 generic if we have evidence that wp_bp_activity is, say, getting too
 bloated - but I don't think we have that.

--
Ticket URL: <https://buddypress.trac.wordpress.org/ticket/3794#comment:21>
BuddyPress Trac <http://buddypress.org/>
BuddyPress Trac