Sequelize Query Ordering Practice

Understanding the Problem

We need to implement three Express routes that retrieve and order data from a database using Sequelize:

1. Get all bands ordered by creation date (newest first)
2. Get all musicians ordered alphabetically by last name then first name
3. Get all bands with their musicians, ordered by band name and then musician names

Devising a Plan

1. For /bands/latest:
   • Use findAll with order option
   • Order by createdAt in descending order
2. For /musicians/alphabetic:
   • Use findAll with order option
   • Create array of ordering criteria
   • Order by lastName then firstName
3. For /bands/alphabetic-musicians:
   • Use findAll with include and order options
   • Include Musicians model
   • Order bands by name
   • Order musicians by lastName, firstName

Implementing the Solution

Basic Solution

// STEP 1: Order by one attribute
app.get('/bands/latest', async (req, res, next) => {
    const bands = await Band.findAll({ 
        order: [['createdAt', 'DESC']] // Orders by creation date, newest first
    });
    res.json(bands);
});

// STEP 2: Order by multiple attributes
app.get('/musicians/alphabetic', async (req, res, next) => {
    const musicians = await Musician.findAll({ 
        order: [
            ['lastName', 'ASC'],  // Primary sort by last name
            ['firstName', 'ASC']  // Secondary sort by first name
        ]
    });
    res.json(musicians);
});

// STEP 3: Order by nested attributes
app.get('/bands/alphabetic-musicians', async (req, res, next) => {
    const bands = await Band.findAll({ 
        include: { model: Musician },
        order: [
            ['name', 'ASC'],              // Order bands by name
            [Musician, 'lastName', 'ASC'], // Order musicians by last name
            [Musician, 'firstName', 'ASC'] // Then by first name
        ]
    });
    res.json(bands);
});
    

Understanding the Code

The order Option

In Sequelize, the order option accepts an array of arrays. Each inner array specifies:

• First element: The column to sort by
• Second element: The direction ('ASC' for ascending or 'DESC' for descending)
• For nested models, include the model name before the column

Real World Example

Think of this like sorting a library:

• Step 1 is like sorting books by publication date (newest first)
• Step 2 is like sorting books by author's last name, then first name
• Step 3 is like sorting books by genre (band), then author (musician)

Further Examples

Alternative ways to write the ordering:

// Using literal SQL
order: [[sequelize.literal('createdAt DESC')]]

// Using an object notation
order: [
    {
        column: 'lastName',
        order: 'ASC'
    }
]

// Multiple nested associations
order: [
    ['name', 'ASC'],
    [{ model: Musician }, { model: Instrument }, 'type', 'ASC']
]
    

Related Concepts

• Sequelize eager loading (include option)
• SQL ORDER BY clause
• Database indexing for optimized sorting
• Pagination with ordered results

Common Pitfalls

• Forgetting to wrap order criteria in arrays
• Not including the model for nested ordering
• Using incorrect column names
• Not considering performance with large datasets