73 replies

Join tables query / paginate

An important limitation in Convex that affects how we need to structure queries, especially for paginated results with joins. Let me explain the problem and show hacky workarounds:

The Problem

In my current code, i am trying to:
1. Paginate products
2. Then fetch variations for those products
3. Then filter based on those variations (colors, sizes)

This approach is problematic because:
1. The pagination happens BEFORE the joins and filters on variations
2. This means I might get fewer results than requested after filtering
3. I can't do true SQL-like JOINs in a single query

For example, if I request 10 products:
- I might get 10 products from the initial pagination
- After filtering by variations, I might end up with only 3-4 products that actually match
- The user sees fewer items than expected

Possible Solutions

1. Denormalization Approach

// Store available colors and sizes directly on the product document
interface Product {
  _id: Id<"products">;
  // ... other fields ...
  availableColors: Id<"productColors">[];
  availableSizes: Array<{
    sizeCategoryId: Id<"sizeCategories">;
    values: string[];
  }>;
  hasInventory: boolean;
}

// Store available colors and sizes directly on the product document
interface Product {
  _id: Id<"products">;
  // ... other fields ...
  availableColors: Id<"productColors">[];
  availableSizes: Array<{
    sizeCategoryId: Id<"sizeCategories">;
    values: string[];
  }>;
  hasInventory: boolean;
}

// Store available colors and sizes directly on the product document
interface Product {
  _id: Id<"products">;
  // ... other fields ...
  availableColors: Id<"productColors">[];
  availableSizes: Array<{
    sizeCategoryId: Id<"sizeCategories">;
    values: string[];
  }>;
  hasInventory: boolean;
}

// Store available colors and sizes directly on the product document
interface Product {
  _id: Id<"products">;
  // ... other fields ...
  availableColors: Id<"productColors">[];
  availableSizes: Array<{
    sizeCategoryId: Id<"sizeCategories">;
    values: string[];
  }>;
  hasInventory: boolean;
}

2. Materialized Views Approach

// Create a separate table that pre-joins product data
interface ProductView {
  _id: Id<"productViews">;
  productId: Id<"products">;
  availableColors: Id<"productColors">[];
  availableSizes: Array<{
    sizeCategoryId: Id<"sizeCategories">;
    values: string[];
  }>;
  // ... other fields needed for filtering
}

// Create a separate table that pre-joins product data
interface ProductView {
  _id: Id<"productViews">;
  productId: Id<"products">;
  availableColors: Id<"productColors">[];
  availableSizes: Array<{
    sizeCategoryId: Id<"sizeCategories">;
    values: string[];
  }>;
  // ... other fields needed for filtering
}

// Create a separate table that pre-joins product data
interface ProductView {
  _id: Id<"productViews">;
  productId: Id<"products">;
  availableColors: Id<"productColors">[];
  availableSizes: Array<{
    sizeCategoryId: Id<"sizeCategories">;
    values: string[];
  }>;
  // ... other fields needed for filtering
}

// Create a separate table that pre-joins product data
interface ProductView {
  _id: Id<"productViews">;
  productId: Id<"products">;
  availableColors: Id<"productColors">[];
  availableSizes: Array<{
    sizeCategoryId: Id<"sizeCategories">;
    values: string[];
  }>;
  // ... other fields needed for filtering
}

3. Batch Loading Approach

// Load more products than needed initially, then filter
const BATCH_MULTIPLIER = 3; // Load 3x more products than requested
const batchSize = paginationOpts.numItems * BATCH_MULTIPLIER;

// Modified pagination options
const batchPaginationOpts = {
  ...paginationOpts,
  numItems: batchSize
};

// Load more products than needed initially, then filter
const BATCH_MULTIPLIER = 3; // Load 3x more products than requested
const batchSize = paginationOpts.numItems * BATCH_MULTIPLIER;

// Modified pagination options
const batchPaginationOpts = {
  ...paginationOpts,
  numItems: batchSize
};

// Load more products than needed initially, then filter
const BATCH_MULTIPLIER = 3; // Load 3x more products than requested
const batchSize = paginationOpts.numItems * BATCH_MULTIPLIER;

// Modified pagination options
const batchPaginationOpts = {
  ...paginationOpts,
  numItems: batchSize
};

// Load more products than needed initially, then filter
const BATCH_MULTIPLIER = 3; // Load 3x more products than requested
const batchSize = paginationOpts.numItems * BATCH_MULTIPLIER;

// Modified pagination options
const batchPaginationOpts = {
  ...paginationOpts,
  numItems: batchSize
};

Join tables query / paginate

The Problem

Possible Solutions

Similar Threads

Similar Threads

Similar Threads