[SOLVED] Sort order mismatch between SQLite and Javascript

Sort order mismatch between SQLite and Javascript

I have this better-sqlite-3 statement that returns a list of products, first sorted by relevance (for search feature, it's 0 for all in this case), then by favorite and then by name A-Z.

    ORDER BY
        relevance DESC,
        preferito DESC,
        des_art   ASC

The products are displayed like so:

"ACCIUGA RIPIENA CREUZA"

"ACCIUGA RIPIENA baccalà"

I have a javascript function that I use to restore the list in case the favorite status changes, but then the products are displayed in alphabetical order ignoring the uppercase.

allProducts.sort((a, b) => {
    
    if (b.relevance !== a.relevance) {
        return b.relevance - a.relevance;
    }
    
    if (b.preferito !== a.preferito) {
        return b.preferito - a.preferito;
    }
    
    return a.des_art.localeCompare(b.des_art);
});

"ACCIUGA RIPIENA baccalà"

"ACCIUGA RIPIENA CREUZA"

How do I keep the same SQL behavior? I tried using return a.des_art.localeCompare(b.des_art, undefined, { sensitivity: 'case', caseFirst: 'upper' }); but without changes.

Solution

To keep the same as SQLite's binary collation ordering, you should compare the strings using < and >. This ensures uppercase comes before lowercase, as their UTF-16 code units are smaller (e.g., 'A' is 65, 'a' is 97).

allProducts.sort((a, b) => {

    if (b.relevance !== a.relevance) {
        return b.relevance - a.relevance;
    }

    if (b.preferito !== a.preferito) {
        return b.preferito - a.preferito;
    }

    if (a.des_art < b.des_art) return -1;
    if (a.des_art > b.des_art) return 1;
    return 0;
});