Mastering Complex Aggregation in Sequelize

Understanding Complex Aggregation

Imagine you're a school administrator who needs to understand how well each classroom is performing overall. You don't just want to know how many students are in each class - you want to know their average grades, how many are struggling, and how this compares to the classroom's capacity. This is where complex aggregation comes in.

When we aggregate data across related tables, we're like a detective piecing together different pieces of evidence to form a complete picture. We'll take information from multiple sources (students, classrooms, and their relationships) and combine it in meaningful ways to help make informed decisions.

Calculating Average Grades Per Classroom

Let's start with implementing average grade calculations for each classroom. This is similar to how a teacher might calculate class averages, but we'll do it for all classrooms at once using database aggregation.

const getClassroomsWithAverages = async (req, res) => {
    try {
        const classrooms = await Classroom.findAll({
            attributes: [
                'id',
                'name',
                'studentLimit',
                // Calculate average grade using Sequelize's aggregation
                [
                    sequelize.literal(`(
                        SELECT AVG(grade)
                        FROM StudentClassrooms
                        WHERE StudentClassrooms.classroomId = Classroom.id
                    )`),
                    'avgGrade'
                ]
            ],
            group: ['Classroom.id'],
            having: sequelize.literal('avgGrade IS NOT NULL'),
            order: [['name', 'ASC']]
        });

        // Format the results and add performance indicators
        const enhancedClassrooms = classrooms.map(classroom => {
            const plainClassroom = classroom.get({ plain: true });
            return {
                ...plainClassroom,
                avgGrade: Number(plainClassroom.avgGrade).toFixed(2),
                performanceIndicator: getPerformanceLevel(plainClassroom.avgGrade)
            };
        });

        res.json(enhancedClassrooms);
    } catch (error) {
        console.error('Error calculating classroom averages:', error);
        res.status(500).json({
            error: 'Failed to calculate classroom averages'
        });
    }
};

Counting Students Per Classroom

Next, let's implement student counting for each classroom. This is more complex than it might seem because we need to account for the many-to-many relationship between students and classrooms.

const getClassroomsWithCounts = async (req, res) => {
    try {
        const classrooms = await Classroom.findAll({
            attributes: [
                'id',
                'name',
                'studentLimit',
                // Count total students
                [
                    sequelize.literal(`(
                        SELECT COUNT(*)
                        FROM StudentClassrooms
                        WHERE StudentClassrooms.classroomId = Classroom.id
                    )`),
                    'numStudents'
                ],
                // Add the previously calculated average grade
                [
                    sequelize.literal(`(
                        SELECT AVG(grade)
                        FROM StudentClassrooms
                        WHERE StudentClassrooms.classroomId = Classroom.id
                    )`),
                    'avgGrade'
                ]
            ],
            group: ['Classroom.id'],
            // Include capacity utilization information
            having: sequelize.literal('numStudents > 0'),
            order: [['name', 'ASC']]
        });

        // Enhance the results with derived calculations
        const enhancedClassrooms = classrooms.map(classroom => {
            const plainClassroom = classroom.get({ plain: true });
            const utilizationRate = (plainClassroom.numStudents / plainClassroom.studentLimit) * 100;
            
            return {
                ...plainClassroom,
                numStudents: Number(plainClassroom.numStudents),
                avgGrade: Number(plainClassroom.avgGrade).toFixed(2),
                utilizationRate: utilizationRate.toFixed(1) + '%',
                status: getClassroomStatus(utilizationRate)
            };
        });

        res.json(enhancedClassrooms);
    } catch (error) {
        console.error('Error calculating classroom statistics:', error);
        res.status(500).json({
            error: 'Failed to calculate classroom statistics'
        });
    }
};

{
    "id": 16,
    "name": "Mr. Collins",
    "studentLimit": 30,
    "numStudents": 29,
    "avgGrade": "2.97",
    "utilizationRate": "96.7%",
    "status": "Near Capacity"
}

Understanding Aggregation Methods

When working with aggregations in Sequelize, we have several powerful tools at our disposal. Think of these like different types of mathematical calculations we might do when analyzing grades:

// Example of using multiple aggregation methods
const stats = await StudentClassroom.findAll({
    attributes: [
        'classroomId',
        [sequelize.fn('AVG', sequelize.col('grade')), 'avgGrade'],
        [sequelize.fn('MIN', sequelize.col('grade')), 'minGrade'],
        [sequelize.fn('MAX', sequelize.col('grade')), 'maxGrade'],
        [sequelize.fn('COUNT', sequelize.col('id')), 'totalStudents']
    ],
    group: ['classroomId']
});

Performance Optimization for Aggregations

When implementing complex aggregations, performance becomes especially important. Here are key strategies to keep your queries efficient:

// Example index creation in a migration
queryInterface.addIndex('StudentClassrooms', ['classroomId', 'grade']);

Practical Applications

These aggregation techniques enable powerful analysis capabilities for school administrators:

Exercise: Enhanced Aggregations