Badges tab of the Activity section on network profiles is broken

Question

The 'badges' tab of the 'activity' section on network profiles is broken; it says

no activity found

Answer 1

I noticed this issue today, and investigated the query responsible for populating the Badges tab. We essentially do this (a quite simplified version of the query):

SELECT TOP (100) <stuff>
  FROM NetworkActivity AS NA
  LEFT JOIN NetworkPosts AS NP
  ON <same site and user>
  WHERE <NA.ActivityType is a badge>
  ORDER BY <newest>;

In January, a filter was added to make sure we only include posts that are in a specific state (published) - the reason was to exclude things like drafts and Staging Ground posts in various states (keep in mind, the logic that generates queries for the activity tab involves more than just badges). When the filter was added, the query became:

SELECT TOP (100) <stuff>
  FROM NetworkActivity AS NA
  LEFT JOIN NetworkPosts AS NP
  ON <same site and user>
  WHERE <NA.ActivityType is a badge>

    /* filter added here */
    AND NP.PostState = <published> 

  ORDER BY <newest>;

In SQL Server, if you add a WHERE clause to an outer joined table, you essentially turn that join into an inner join. Since most badges don't have an associated post, this means that for most users, the 100 most recent badges get filtered out, and the page is empty.

The fix is to move that new filter to the join criteria:

SELECT TOP (100) <stuff>
  FROM NetworkActivity AS NA
  LEFT JOIN NetworkPosts AS NP
  ON <same site and user>

    /* filter moved here */
    AND NP.PostState = <published> 

  WHERE <NA.ActivityType is a badge>
  ORDER BY <newest>;

I had a two-minute chat with the developer who introduced the change, and they fast-tracked a fix that should be working soon (it was deployed, then rolled back due to an unrelated change that broke network-wide search, and should be working again later today).