Badge Backfill Code

I’m looking at the badge backfill process happening here:

In particular, the following query seems to have some issues (see Long-Running Sidekiq Jobs)

    sql = <<~SQL
      DELETE FROM user_badges
        WHERE id IN (
          SELECT ub.id
          FROM user_badges ub
          LEFT JOIN (
            #{badge.query}
          ) q ON q.user_id = ub.user_id
          #{post_clause}
          WHERE ub.badge_id = :id AND q.user_id IS NULL
        )
    SQL

This query does a LEFT JOIN so all results from user_badges will be returned regardless if there is a match, but this part seems very confusing:

ON q.user_id = ub.user_id
...
AND q.user_id IS NULL

The query joins on rows where the user_id matches, but then discards those rows via the WHERE clause, making the join there kind of moot. So I’m wondering:

  1. What is the purpose of the backfill function? It appears to completely wipe the badge being processed and then rebuilds it all at once. Is this necessary?

  2. What is the difference between a badge that targets posts and one that does not with regard to this function? With a LEFT JOIN, does this even matter when finding the correct badge_id to wipe for the rebuild?

2 Likes