Efficiently Rank Profiles

[charred knoll]

Hi @austere hawk. I'm kapa.ai, your friendly support bot. I will try to answer your question by searching through the knowledge base. :wave:

---

Efficiently Ranking Profiles in Convex

For your nightly ranking job, you have a few options to efficiently rank profiles based on the sum of favoritedCounts from their links.

Using the Aggregate Component

The most efficient approach would be to use Convex's Aggregate component, which was specifically designed for these kinds of operations:

import { aggregate } from "@convex-dev/aggregate";

// Define your aggregate in a separate file
export const profilesByTotalFavorites = aggregate({
  table: "profiles",
  valueGetter: async (ctx, profile) => {
    // Get all links for this profile
    const links = await ctx.db
      .query("links")
      .withIndex("by_profileId")
      .filter(q => q.eq(q.field("profileId"), profile._id))
      .collect();
    
    // Sum up the favoritedCount
    return links.reduce((sum, link) => sum + link.favoritedCount, 0);
  },
});

// In your cron job mutation:
export const updateProfileRanks = mutation({
  handler: async (ctx) => {
    let rank = 1;
    // This will iterate through profiles in descending order of total favorites
    for await (const profile of ctx.db
      .query("profiles")
      .withIndex("by_totalFavorites") // You'd need this index
      .order("desc")) {
      
      await ctx.db.patch(profile._id, {
        rank: rank,
      });
      rank += 1;
    }
  },
});

The Aggregate component provides O(log(n))-time lookups instead of O(n) operations, making it much more efficient for large datasets Aggregate.

Denormalization Approach

Another approach is to maintain a running total of favoritedCounts on each profile document:

1. Add a totalFavoritedCount field to your profiles table
2. Update this field whenever a link's favoritedCount changes
3. Then run a nightly job to assign ranks based on this field:

export const updateProfileRanks = mutation({
  handler: async (ctx) => {
    let rank = 1;
    for await (const profile of ctx.db
      .query("profiles")
      .withIndex("by_totalFavoritedCount")
      .order("desc")) {
      
      await ctx.db.patch(profile._id, {
        rank: rank,
      });
      rank += 1;
    }
  },
});

This approach is very efficient for the ranking job but requires you to keep the totalFavoritedCount field in sync Stack > Operational maturity for production.

Scheduled Function Implementation

You can implement this as a scheduled function (cron job) in Convex:

export const generateRankings = mutation({
  handler: async (ctx) => {
    // 1. Get all profiles with their total favoritedCounts
    const profiles = await ctx.db.query("profiles").collect();
    
    // 2. For each profile, calculate total favoritedCount
    const profilesWithTotals = await Promise.all(
      profiles.map(async (profile) => {
        const links = await ctx.db
          .query("links")
          .withIndex("by_profileId")
          .filter(q => q.eq(q.field("profileId"), profile._id))
          .collect();
        
        const totalFavoritedCount = links.reduce(
          (sum, link) => sum + link.favoritedCount, 
          0
        );
        
        return { profile, totalFavoritedCount };
      })
    );
    
    // 3. Sort by totalFavoritedCount in descending order
    profilesWithTotals.sort((a, b) => b.totalFavoritedCount - a.totalFavoritedCount);
    
    // 4. Assign ranks and update profiles
    for (let i = 0; i < profilesWithTotals.length; i++) {
      const { profile } = profilesWithTotals[i];
      await ctx.db.patch(profile._id, { rank: i + 1 });
    }
  }
});

This approach works but may not scale well with large datasets as it loads all profiles and links into memory Stack > Queries that scale.

For the most efficient implementation, I recommend using the Aggregate component or the denormalization approach with proper indexing.

Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: