Sequelize: findAndCountAll(), but with a specific row as the first entry

Question

I've a table that I'm querying using findAndCountAll()

I'm basically using it for pagination/ordering using the order, limit and index properties. Something simple like this:

exports.getApplications = async(req, res, next) => {
    const findOne = req.query.indexId;
    const index = req.query.index || 0;
    const limit = req.query.limit || 10;
    const orderField = req.query.orderField || 'createdAt';
    const orderDirection = req.query.orderDirection || 'DESC';
    const order = [ orderField, orderDirection ];

    const applications = await Application.findAndCountAll({   
        order: [order],      
        limit: parseInt(limit, 10), 
        offset: parseInt(index)
     
        // ...
    }
}

But I'd also like to be able to specify a particular indexId (findOne), and have that single entry appear before the paginated/ordered list (*edit: The row may or may not be included in the paginated list that's returned)

Is that possible using findAndCountAll? Or would I have to run a separate query using findByPk(), reduce the limit by 1, then unshift the entry to the front of the results array?

Thanks,

Nick

---

*Edit - Implementation in case anyone is interested. Now just have to make the queries run in parallel :)

try {
    // Find a specific row to highlight if needed
    if(findOne) {
        topRow = await Application.findByPk(findOne, {
            include: [...],
        });
    }

    const applications = await Application.findAndCountAll({
        include: [...],
        order: [order],      
        limit: parseInt(limit, 10), 
        offset: parseInt(index)
    });

    // If there's a row to be highlighted
    if(topRow) {
        const visible = applications.rows.filter(application => application.id === topRow.id);
        const index = applications.rows.findIndex(application => application.id === topRow.id);

        // 1 too many results in the array
        if(applications.rows.length + 1 > limit) {
            // If the row appears in the array to be returned, remove it
            if(visible) {
                applications.rows.splice(index, 1);
            } else {
                // Remove the last element instead
                applications.rows.pop();
            }
        } else { 
            if(visible) applications.rows.splice(index, 1);
        }
        
        // Add the highlighted topRow to the array
        applications.rows.unshift(topRow) 
    }

Answer 1

Try to do this, assuming your findOne value is safe to use:

order: [
  [sequelize.literal(`id = ${findOne}`), 'DESC'],
  [orderField, orderDirection]
]

This will return true for the row you want, false for others, so it will first sort it to the top, then continue the rest of the ordering.