/**
* MongoDB aggregation pipeline builders for investigation queries
* @category Utils
*/
import { InvestigationGroupType, InvestigationSectionType, InvestigationSortBy, SortOrder, } from "@typez/investigation/enums";
// ============================================================================
// SORT FIELD MAPPING
// ============================================================================
/** Map of sortBy enum values to MongoDB field paths */
const SORT_FIELD_MAP = {
[InvestigationSortBy.CREATED_AT]: "createdAt",
[InvestigationSortBy.UPDATED_AT]: "updatedAt",
[InvestigationSortBy.TITLE]: "title.value",
[InvestigationSortBy.ACTIVITY]: "activity",
[InvestigationSortBy.TIME_IN_DEVELOPMENT]: "metadata.dateOfDevelopmentDelivery",
[InvestigationSortBy.VIEWS]: "views",
[InvestigationSortBy.COMPLETIONS]: "completions",
};
// ============================================================================
// URL LOOKUP STAGES
// ============================================================================
/**
* MongoDB aggregation stages for looking up URL data and computing views/completions
*/
export const URL_LOOKUP_STAGES = [
{
$lookup: {
from: "urls",
localField: "_id",
foreignField: "investigationId",
as: "urls",
},
},
{
$addFields: {
views: { $sum: "$urls.counters.visitsTotal" },
completions: { $sum: "$urls.counters.completionsTotal" },
},
},
];
// ============================================================================
// GRADE SORT EXPRESSION
// ============================================================================
/**
* MongoDB expression for grade sorting.
* Converts grade to numeric: K=0, 1-12=1-12, P=13, B=14, C=15, other=99, empty=999
*/
const GRADE_SORT_FIELD = {
_sectionSortGrade: {
$let: {
vars: {
gradeStr: {
$toLower: { $trim: { input: { $toString: { $ifNull: ["$grade.value", ""] } } } },
},
},
in: {
$switch: {
branches: [
{ case: { $eq: ["$$gradeStr", ""] }, then: 999 },
{ case: { $eq: ["$$gradeStr", "k"] }, then: 0 },
{
case: { $regexMatch: { input: "$$gradeStr", regex: "^[0-9]+$" } },
then: { $toInt: "$$gradeStr" },
},
{ case: { $eq: ["$$gradeStr", "p"] }, then: 13 },
{ case: { $eq: ["$$gradeStr", "b"] }, then: 14 },
{ case: { $eq: ["$$gradeStr", "c"] }, then: 15 },
],
default: 99,
},
},
},
},
};
// ============================================================================
// CURRICULUM SORT EXPRESSION
// ============================================================================
/** MongoDB expression for curriculum sorting (empty → "zzz" to sort last) */
const CURRICULUM_SORT_EXPR = {
$let: {
vars: { val: { $ifNull: ["$curriculum.value", ""] } },
in: {
$cond: {
if: { $eq: ["$$val", ""] },
then: "zzz",
else: { $toLower: "$$val" },
},
},
},
};
// ============================================================================
// SUBJECT SORT EXPRESSIONS
// ============================================================================
/**
* MongoDB expressions for subject-based group sorting.
* Order: Physics (1) → Biology (2) → Chemistry (3) → other (50) → N/A (99)
*/
const SUBJECT_SORT_FIELDS = {
_groupSortSubjectType: {
$let: {
vars: {
unitStr: { $ifNull: ["$unitNumberAndTitle.value", ""] },
subjectCode: {
$toLower: {
$arrayElemAt: [
{
$split: [
{
$arrayElemAt: [
{ $split: [{ $ifNull: ["$unitNumberAndTitle.value", ""] }, " "] },
1,
],
},
".",
],
},
0,
],
},
},
},
in: {
$switch: {
branches: [
{ case: { $eq: ["$$unitStr", ""] }, then: 99 },
{ case: { $eq: ["$$subjectCode", "p"] }, then: 1 },
{ case: { $eq: ["$$subjectCode", "b"] }, then: 2 },
{ case: { $eq: ["$$subjectCode", "c"] }, then: 3 },
],
default: 50,
},
},
},
},
_groupSortSubjectValue: {
$toLower: {
$arrayElemAt: [
{
$split: [
{
$arrayElemAt: [{ $split: [{ $ifNull: ["$unitNumberAndTitle.value", ""] }, " "] }, 1],
},
".",
],
},
0,
],
},
},
};
// ============================================================================
// UNIT SORT EXPRESSIONS
// ============================================================================
/** Helper expression to extract unit identifier: " Unit 8.1: Title " → "8.1" */
const UNIT_IDENTIFIER_EXPR = {
$let: {
vars: {
trimmedUnit: { $trim: { input: { $ifNull: ["$unitNumberAndTitle.value", ""] } } },
},
in: {
$let: {
vars: {
match: {
$regexFind: {
input: "$$trimmedUnit",
regex: "^[Uu]nit\\s+([^:\\s]+)",
},
},
},
in: {
$cond: {
if: { $eq: ["$$match", null] },
then: null,
else: { $arrayElemAt: ["$$match.captures", 0] },
},
},
},
},
},
};
/** MongoDB expressions for unit-based group sorting */
const UNIT_SORT_FIELDS = {
_groupSortUnitGradeType: {
$let: {
vars: { unitIdentifier: UNIT_IDENTIFIER_EXPR },
in: {
$let: {
vars: {
gradePrefix: {
$toLower: {
$arrayElemAt: [{ $split: [{ $ifNull: ["$$unitIdentifier", ""] }, "."] }, 0],
},
},
},
in: {
$switch: {
branches: [
{ case: { $eq: ["$$gradePrefix", "k"] }, then: 0 },
{ case: { $regexMatch: { input: "$$gradePrefix", regex: "^[0-9]+$" } }, then: 1 },
],
default: 2,
},
},
},
},
},
},
_groupSortUnitGradeValue: {
$let: {
vars: { unitIdentifier: UNIT_IDENTIFIER_EXPR },
in: {
$let: {
vars: {
gradePrefix: {
$arrayElemAt: [{ $split: [{ $ifNull: ["$$unitIdentifier", ""] }, "."] }, 0],
},
},
in: {
$cond: {
if: { $regexMatch: { input: { $ifNull: ["$$gradePrefix", ""] }, regex: "^[0-9]+$" } },
then: { $toInt: "$$gradePrefix" },
else: { $toLower: { $ifNull: ["$$gradePrefix", "zzz"] } },
},
},
},
},
},
},
_groupSortUnitNumber: {
$let: {
vars: { unitIdentifier: UNIT_IDENTIFIER_EXPR },
in: {
$let: {
vars: {
unitNum: {
$arrayElemAt: [{ $split: [{ $ifNull: ["$$unitIdentifier", ""] }, "."] }, 1],
},
},
in: {
$cond: {
if: { $or: [{ $eq: ["$$unitNum", null] }, { $eq: ["$$unitNum", ""] }] },
then: 0,
else: {
$cond: {
if: { $regexMatch: { input: "$$unitNum", regex: "^[0-9]+$" } },
then: { $toInt: "$$unitNum" },
else: 999,
},
},
},
},
},
},
},
},
};
// ============================================================================
// LESSON SORT EXPRESSIONS
// ============================================================================
/** MongoDB expressions for lesson-based group sorting (extends unit sorting) */
const LESSON_SORT_FIELDS = {
...UNIT_SORT_FIELDS,
_groupSortLessonNumber: {
$let: {
vars: {
lessonStr: { $ifNull: ["$lessonNumberAndTitle.value", ""] },
},
in: {
$cond: {
if: { $eq: ["$$lessonStr", ""] },
then: 999,
else: {
$let: {
vars: {
lessonPart: { $arrayElemAt: [{ $split: ["$$lessonStr", " "] }, 1] },
},
in: {
$let: {
vars: {
numericPart: {
$arrayElemAt: [{ $split: [{ $ifNull: ["$$lessonPart", ""] }, ":"] }, 0],
},
},
in: {
$cond: {
if: {
$regexMatch: {
input: { $ifNull: ["$$numericPart", ""] },
regex: "^[0-9]+$",
},
},
then: { $toInt: "$$numericPart" },
else: 999,
},
},
},
},
},
},
},
},
},
},
};
// ============================================================================
// PUBLIC FUNCTIONS
// ============================================================================
/**
* Determines the default sort order based on the sort field
* @param {InvestigationSortBy|undefined} sortBy - The field to sort by
* @returns {SortOrder} The default sort order for the field
*/
export function getDefaultSortOrder(sortBy) {
if (!sortBy)
return SortOrder.DESC;
// Date fields: newest first (desc)
if (sortBy === InvestigationSortBy.CREATED_AT || sortBy === InvestigationSortBy.UPDATED_AT) {
return SortOrder.DESC;
}
// Text fields: A-Z (asc)
if (sortBy === InvestigationSortBy.TITLE || sortBy === InvestigationSortBy.ACTIVITY) {
return SortOrder.ASC;
}
// Numeric fields: More→Less (desc)
return SortOrder.DESC;
}
/**
* Builds MongoDB $addFields stages for group sorting
* @param {GroupType|null} group - The group type to build sort fields for
* @returns {SectionSortAddFieldsStage[]} Array of MongoDB $addFields stages
*/
export function buildGroupSortFields(group) {
switch (group) {
case InvestigationGroupType.SUBJECTS:
return [{ $addFields: { ...SUBJECT_SORT_FIELDS } }];
case InvestigationGroupType.UNITS:
return [{ $addFields: { ...UNIT_SORT_FIELDS } }];
case InvestigationGroupType.UNITS_LESSONS:
return [{ $addFields: { ...LESSON_SORT_FIELDS } }];
default:
return [];
}
}
/**
* Builds MongoDB $addFields stages for section sorting
* @param {SectionType|null} section - The section type to build sort fields for
* @param {string} [currentUserId] - Optional current user ID for author sorting
* @param {Types.ObjectId[]} [sortedAuthorIds] - Optional pre-sorted author IDs
* @returns {SectionSortAddFieldsStage[]} Array of MongoDB $addFields stages
*/
export function buildSectionSortFields(section, currentUserId, sortedAuthorIds) {
if (!section)
return [];
switch (section) {
case InvestigationSectionType.CURRICULUM_GRADE:
return [
{ $addFields: { _sectionSortCurriculum: CURRICULUM_SORT_EXPR, ...GRADE_SORT_FIELD } },
];
case InvestigationSectionType.GRADE:
return [{ $addFields: { ...GRADE_SORT_FIELD } }];
case InvestigationSectionType.CURRICULUM_STATUS:
return [
{
$addFields: {
_sectionSortCurriculum: CURRICULUM_SORT_EXPR,
_sectionSortStatus: {
$let: {
vars: { val: { $ifNull: ["$status", ""] } },
in: {
$cond: {
if: { $eq: ["$$val", ""] },
then: "zzz",
else: { $toLower: "$$val" },
},
},
},
},
},
},
];
case InvestigationSectionType.AUTHOR:
if (sortedAuthorIds?.length) {
return [
{
$addFields: {
_sectionSortAuthorPosition: {
$let: {
vars: { pos: { $indexOfArray: [sortedAuthorIds, "$metadata.author"] } },
in: { $cond: { if: { $eq: ["$$pos", -1] }, then: 999999, else: "$$pos" } },
},
},
},
},
];
}
return [
{
$addFields: {
_sectionSortIsCurrentUser: {
$cond: {
if: { $eq: [{ $toString: "$metadata.author" }, currentUserId || ""] },
then: 0,
else: 1,
},
},
_sectionSortAuthorId: {
$let: {
vars: { val: { $ifNull: [{ $toString: "$metadata.author" }, ""] } },
in: { $cond: { if: { $eq: ["$$val", ""] }, then: "zzz", else: "$$val" } },
},
},
},
},
];
default:
return [];
}
}
/**
* Builds MongoDB sort specification with section/group-aware sorting
* @param {InvestigationSortBy|undefined} sortBy - The field to sort by
* @param {SortOrder|undefined} sortOrder - The sort order (ASC or DESC)
* @param {boolean} hasSearch - Whether a search term is present
* @param {SectionType|null} section - Optional section type for section-aware sorting
* @param {boolean} hasSortedAuthorIds - Whether pre-sorted author IDs are available
* @param {GroupType|null} group - Optional group type for group-aware sorting
* @returns {Record<string, number>} MongoDB sort specification object
*/
export function buildSortSpec(sortBy, sortOrder, hasSearch, section = null, hasSortedAuthorIds = false, group = null) {
const effectiveSortBy = sortBy || InvestigationSortBy.UPDATED_AT;
const effectiveSortOrder = sortOrder || getDefaultSortOrder(effectiveSortBy);
const direction = effectiveSortOrder === SortOrder.ASC ? 1 : -1;
const sortSpec = {};
// Search relevance first
if (hasSearch) {
sortSpec.relevanceScore = -1;
}
// Section sorting (takes priority over group)
if (section) {
addSectionSortFields(sortSpec, section, hasSortedAuthorIds);
}
else if (group) {
addGroupSortFields(sortSpec, group);
}
// User's sort field + tiebreaker
sortSpec[SORT_FIELD_MAP[effectiveSortBy]] = direction;
sortSpec._id = direction;
return sortSpec;
}
// ============================================================================
// PRIVATE HELPERS
// ============================================================================
function addSectionSortFields(sortSpec, section, hasSortedAuthorIds) {
switch (section) {
case InvestigationSectionType.CURRICULUM_GRADE:
sortSpec._sectionSortCurriculum = 1;
sortSpec._sectionSortGrade = 1;
break;
case InvestigationSectionType.GRADE:
sortSpec._sectionSortGrade = 1;
break;
case InvestigationSectionType.CURRICULUM_STATUS:
sortSpec._sectionSortCurriculum = 1;
sortSpec._sectionSortStatus = 1;
break;
case InvestigationSectionType.AUTHOR:
if (hasSortedAuthorIds) {
sortSpec._sectionSortAuthorPosition = 1;
}
else {
sortSpec._sectionSortIsCurrentUser = 1;
sortSpec._sectionSortAuthorId = 1;
}
break;
}
}
function addGroupSortFields(sortSpec, group) {
switch (group) {
case InvestigationGroupType.SUBJECTS:
sortSpec._groupSortSubjectType = 1;
sortSpec._groupSortSubjectValue = 1;
break;
case InvestigationGroupType.UNITS:
sortSpec._groupSortUnitGradeType = 1;
sortSpec._groupSortUnitGradeValue = 1;
sortSpec._groupSortUnitNumber = 1;
break;
case InvestigationGroupType.UNITS_LESSONS:
sortSpec._groupSortUnitGradeType = 1;
sortSpec._groupSortUnitGradeValue = 1;
sortSpec._groupSortUnitNumber = 1;
sortSpec._groupSortLessonNumber = 1;
break;
}
}
Source