Aller au contenu principal

Table Order Aggregation Documentation

Overview​

This document explains the MongoDB aggregation pipeline used in BaseController.aggregateDefault() to retrieve and sort items according to their position in a tableOrder document.

Problem Statement​

When displaying items from a collection, we need to:

  1. Sort items by their position in a custom order array stored in a separate tableorders collection
  2. Support search/filtering while maintaining the custom order
  3. Handle pagination correctly
  4. Extract metadata (like pinned status) from the order array

Solution Architecture​

Data Structure​

Items Collection (e.g., products, articles, etc.)

{
  _id: ObjectId("..."),
  module_id: ObjectId("..."),
  title: "Item Title",
  // ... other fields
}

TableOrders Collection

{
  _id: ObjectId("..."),
  ref: ObjectId("..."), // References module_id
  items: [
    {
      item_id: ObjectId("..."), // References item._id
      pinned: Boolean
    },
    // ... more items in desired order
  ]
}

Aggregation Pipeline Breakdown​

Stage 1: Match Items by Module​

{
  $match: { module_id: ObjectId("...") }
}

Filters items to only those belonging to the specified module.

Stage 2: Filter by Translation Reference (Optional)​

{
  $match: {
    "translation.ref_item": ObjectId("...")
  }
}

If a translation reference is provided, further filter items to a specific translation group.

Stage 3: Lookup Table Order​

{
  $lookup: {
    from: "tableorders",
    localField: "module_id",
    foreignField: "ref",
    as: "tableOrder"
  }
}

Joins the tableorders collection to get the custom ordering for this module.

Stage 4: Unwind Table Order​

{
  $unwind: {
    path: "$tableOrder",
    preserveNullAndEmptyArrays: true
  }
}

Converts the tableOrder array (containing one document) into a single object. Uses preserveNullAndEmptyArrays to handle cases where no table order exists.

Stage 5: Apply Search Filters (Optional)​

{
  $match: { $or: [/* search conditions */] }
}

If search criteria are provided, filter items based on regex matches or exact values.

Stage 6: Calculate Order Index and Pinned Status​

{
  $addFields: {
    orderInfo: {
      $arrayElemAt: [
        {
          $map: {
            input: {
              $filter: {
                input: { $range: [0, { $size: "$tableOrder.items" }] },
                as: "idx",
                cond: {
                  $eq: [
                    { $arrayElemAt: ["$tableOrder.items.item_id", "$$idx"] },
                    "$_id"
                  ]
                }
              }
            },
            as: "matchedIdx",
            in: {
              index: "$$matchedIdx",
              pinned: { $arrayElemAt: ["$tableOrder.items.pinned", "$$matchedIdx"] }
            }
          }
        },
        0
      ]
    }
  }
}

This is the critical stage. Here's how it works:

  1. $range: [0, { $size: "$tableOrder.items" }] - Creates an array of indices: [0, 1, 2, ..., n-1]
  2. $filter - Filters this array to find which index has an item_id matching the current document's _id
  3. $map - For the matched index, creates an object containing:
    • index: The position in the array
    • pinned: The pinned status at that position
  4. $arrayElemAt: [..., 0] - Extracts the first (and only) match

Why this approach?

  • Avoids double $unwind which loses array position information
  • Directly calculates the index position without unwinding the items array
  • Preserves the original order defined in tableOrder.items

Stage 7: Extract Order Fields​

{
  $addFields: {
    orderIndex: { $ifNull: ["$orderInfo.index", 999999] },
    pinned: { $ifNull: ["$orderInfo.pinned", false] }
  }
}

Extracts the index and pinned values from orderInfo. Uses $ifNull to handle items not found in the table order (assigns them a high index so they appear last).

Stage 8: Sort by Order Index​

{
  $sort: {
    // Optional: sort by pinned first if requested
    pinned: -1, // (if "pinned:desc" in sort params)
    orderIndex: 1 // Always sort by index ascending
  }
}

Sorts items by:

  1. pinned status (if requested) - pinned items first
  2. orderIndex - maintains the custom order from tableOrder.items

Stage 9: Pagination​

{ $skip: (page - 1) * limit }
{ $limit: limit }

Applies pagination after sorting to get the correct page of results.

Stage 10: Project Output Fields​

{
  $project: {
    // Include requested output fields
    title: 1,
    description: 1,
    // ...
    pinned: 1
  }
}

Selects only the fields requested by the client, plus the pinned field.

Usage Example​

const aggregation = await this.aggregateDefault(
  moduleId,        // "507f1f77bcf86cd799439011"
  page,            // 1
  limit,           // 20
  sort,            // ["pinned:desc"]
  outputFields,    // ["title", "description", "createdAt"]
  search,          // [{ title: "search term" }]
  translationRef   // Optional: "507f1f77bcf86cd799439012"
);

Performance Considerations​

  1. Index Requirements

    • module_id should be indexed on the items collection
    • ref should be indexed on the tableorders collection
    • Consider compound index on (module_id, translation.ref_item) if using translations

  2. Array Size

    • The $range operation scales with the size of tableOrder.items
    • For very large arrays (>10,000 items), consider alternative approaches

  3. Search Performance

    • Search filters are applied before order calculation
    • Regex searches can be slow without proper indexes

Edge Cases Handled​

  1. Item not in table order - Assigned index 999999, appears last
  2. No table order exists - Returns empty array
  3. Missing pinned field - Defaults to false
  4. Empty search array - Skips search filtering
  5. No translation reference - Includes all items for the module

Alternative Approaches Considered​

Double $unwind (Original Approach)​

{ $unwind: "$tableOrder" }
{ $unwind: "$tableOrder.items" }

Problem: Loses array index information, making it impossible to sort by original position.

$indexOfArray with $literal​

{
  $addFields: {
    orderIndex: {
      $indexOfArray: [{ $literal: orderItemIds }, "$_id"]
    }
  }
}

Problem: Cannot pass JavaScript arrays into aggregation pipeline; requires pre-fetching data.

Current Approach: $range + $filter​

Advantages:

  • Preserves array index
  • Pure aggregation pipeline (no pre-fetching)
  • Handles all edge cases
  • Efficient for moderate array sizes

Troubleshooting​

Items appear in wrong order​

  • Verify tableOrder.items array has correct order
  • Check that item_id values match document _id values
  • Ensure orderIndex is being calculated (not all 999999)

Missing items in results​

  • Check if items exist in tableOrder.items array
  • Verify module_id matches between collections
  • Check search filters aren't too restrictive

Performance issues​

  • Add indexes on module_id and ref fields
  • Consider caching table order data
  • Reduce tableOrder.items array size if possible
  • /src/controllers/base.controller.ts - Contains the aggregation implementation
  • /src/controllers/tableOrder.controller.ts - Manages table order updates
  • /src/models/tableOrder.model.ts - TableOrder schema definition