Add last_updated timestamp when updating count in flag_counts_table

Sounds good to me, we'll also need to build out the Views implementation to work with this also.

subscribing

#976974: Can I get the "last flagged" time for a node or only the "first flagged" time? too wants this feature.

(It's very possible that I misunderstood @ajayg and that the "last_flagged" column I had in mind is different than his proposed "last_updated" column.)

Here's my attempt on adding in the last_updated column. Very rudimentary tests w/Views work as expected.

Thanks jaydub! I'll take a look when I get the chance. Right now is the perfect time for such requests, as I've cleaned up most of the bugs for 2.x and we need to consider what will be in the 2.0 release.

And now adding the drupal 7 version of the patch

These patches are great and work fantastic too. I did a few minor changes:

- D7 doesn't need $ret in its update functions.
- I changed the description on the field in Views to "The time a piece of content was most recently flagged by any user." instead of "Display the time content was most recently flagged by a user." This makes it clear that it can be any user's flagging that will update the time. I removed the "Display..." part because the description didn't make sense when using the column as a sort or filter (since it's not getting displayed unless its a field).

I also thought that perhaps we should make the update hook populate all the "last_updated" values from the flag_content table, but I figured the trade-off here probably isn't worth it. Writing an update function for this would require a lot of queries and make a potentially very-long update process. Considering the most common use would be to list "recently flagged" content, sorting DESC, I think we can just let this information populate as it goes without a problem.

I've committed these changes as represented in the attached patches. Thanks for the suggestion and patches jaydub!

I'm re-opening this, because I would strongly suggest making the index on this field not just "last_updated" but "fid + last_updated".

The reason is the same I am discussing in this issue: #1217314: Fix flag_count indexes to add fid to "count" and "last_updated"

It is far more common to sort items by the latest updates of a specific flag than just of any flag count change.

I could be wrong but I would think that the existing indexes that already include the fid would be in use in a query for latest updates of a specific flag as an index on the WHERE clause and the index on last_updated would provide the index to use during the sort.

I could be wrong but I would think that the existing indexes that already include the fid would be in use in a query for latest updates of a specific flag as an index on the WHERE clause and the index on last_updated would provide the index to use during the sort.

I agree with @ralf.strobel. MySQL can only use one index on any given query. So since you need to filter by FID and then sort/filter by last_updated, you need both of these columns in the same index for it to actually get used. The only time the current index would be helpful would be if you're sorting/filtering by the last_updated column alone, but since all of our Views integrations requires an FID (e.g. a Flag relationship) first, it's unlikely that we'll ever have such a query.

Let's tackle both of these indexes in #1217314: Fix flag_count indexes to add fid to "count" and "last_updated", for which I've provided a patch.