sort / skip / limit

// 1) Basic — newest first
await db.posts.find({})
    .sort({ createdAt: -1 })
    .limit(20)
    .toArray();

// 2) Multi-key sort — secondary order for ties
await db.users.find({})
    .sort({ city: 1, name: 1 })
    .toArray();
// Compound index that matches both keys: db.users.createIndex({ city: 1, name: 1 })

// 3) Skip + limit (offset pagination — easy, doesn't scale)
const page = 5, size = 20;
await db.posts.find({})
    .sort({ createdAt: -1 })
    .skip(page * size)
    .limit(size)
    .toArray();
// Skipping 1,000,000 docs reads + discards 1,000,000 — slow tail.

// 4) Keyset pagination — O(log n) regardless of page
// Page 1: newest 20
const first = await db.posts.find({})
    .sort({ createdAt: -1, _id: -1 })
    .limit(20)
    .toArray();
const last = first.at(-1);

// Page 2: anything older than `last`
const second = await db.posts.find({
    $or: [
        { createdAt: { $lt: last.createdAt } },
        { createdAt: last.createdAt, _id: { $lt: last._id } },
    ],
})
    .sort({ createdAt: -1, _id: -1 })
    .limit(20)
    .toArray();

// 5) Stable sort needs a tie-break
// Two docs with the same createdAt could swap positions across pages.
// Always include _id (or another unique field) in the sort to make it deterministic.
await db.posts.find({}).sort({ createdAt: -1, _id: -1 });

// 6) Top-N per user — aggregation
await db.orders.aggregate([
    { $match: { status: 'paid' } },
    { $sort:  { user_id: 1, total: -1 } },
    { $group: {
        _id:   '$user_id',
        top:   { $firstN: { input: '$$ROOT', n: 3 } },
    } },
]).toArray();

// 7) Skip in aggregation
await db.posts.aggregate([
    { $match: { status: 'published' } },
    { $sort:  { createdAt: -1 } },
    { $skip:  40 },
    { $limit: 20 },
]).toArray();

// 8) Sort with collation (case-insensitive, locale-aware)
await db.users.find({})
    .sort({ name: 1 })
    .collation({ locale: 'en', strength: 2 })   // 2 = case-insensitive
    .toArray();

// 9) Random sample — $sample
await db.posts.aggregate([
    { $match:  { tags: 'featured' } },
    { $sample: { size: 5 } },
]).toArray();
// O(N) shuffle; for large collections, use a precomputed 'random' field + sort.

// 10) Cursor with batchSize (network-efficient streaming)
const cursor = db.events.find({}).sort({ ts: -1 }).batchSize(1000);
for await (const doc of cursor) {
    await process(doc);
}

// 11) Indexes that serve sort + limit efficiently
// The leading prefix of the index must MATCH the sort.
// Sort { city: 1, name: 1 } needs index { city: 1, name: 1 } — not just { name: 1 }.

// Verify the index helps:
await db.posts.find({ status: 'published' })
    .sort({ createdAt: -1 })
    .limit(20)
    .explain('executionStats');
// Look for: stage 'IXSCAN' with 'sortPattern' aligned, no 'SORT' in executionStages

// 12) Common bugs
//   • Sort + skip on an UNINDEXED field — full collection scan, O(N log N) in-memory sort
//   • Forgetting _id tie-break — duplicates / gaps across pages
//   • Using offset for chat / activity feeds — every read scans more rows
//   • Sorting on a computed field — wrap in aggregation $addFields, but lose the index

db.customers.find().sort({ age: -1 }).skip(20).limit(20);