Multi-column sort with redis-om-node?

[peterkmurphy]

Good day, and thank you very much for this package! It has allowed our team to embrace Redis for our project within a couple of weeks. Now I have a question about the redis-om-node, and if someone could assist, that would be appreciated.

For our project, we have a TypeScript interface and schema with quite a few sortable fields in it. A simplified representation would be:

export interface ContactData {
  contactId: number;
  firstName: string;
  lastName: string;
  email: string;
}

export const ContactDataSchema = new Schema(
  ContactData,
  {
    contactId: { type: 'number', sortable: true }, 
    firstName: { type: 'string', sortable: true },
    lastName: { type: 'string', sortable: true },
    email: { type: 'string' }, // Not needed to be sortable.
  },
  {
    dataStructure: 'HASH',
  }
);

My question is: is there any way that redis-om-node can sort by multiple fields at the same time? I would like to express something like the SQL ORDER BY firstName ASC, contactId ASC, where the value of the contactId field is used as a tie-breaker if multiple records share the same value of the firstName field. (For "reasons", the order is important when fetching the records in chunks for consumption by a front end application.)

For example, if the user chooses sorting by firstName (or lastName) ascending, I basically want a 'John Smith' record with a contactId of 2 to come after a 'John Smith' record with a contactId of 1, but before a 'John Smith' record with a contactId of 3.

Alternately, if the user chooses sorting by firstName (or lastName) descending, I basically want a 'John Smith' record with a contactId of 2 to come after a 'John Smith' record with a contactId of 3, but before a 'John Smith' record with a contactId of 1.

Is this functionality built into redis-om-node (or for that matter, Redis)?

At the moment, I have written some work-arounds: a createSortingString function for storing first names and last names with the zero padded contactIds (so that Redis sorts the name fields), and a uncreateSortingString function to reverse this.

/*
// According to the Default Unicode Collation Element Table (DUCET)
//
// https://www.unicode.org/Public/UCA/latest/allkeys.txt
//
// For the Unicode Collation Algorithm, the Unicode null Character occurs before _every_ other
// character in Unicode. This is great for unanbiguous sorting. It also has the advantage that
// nobody in their right mind are going to enter it in the UI.
*/

const UNICODE_NULL_CHARACTER = '\u0000';

// We expect ids to be 32 digits, so the values can range from 0 to 429496725 (a ten digit
// number in decimal). So that gives us an anticipated padding length.

const SORT_PAD_LENGTH = 10;

export function createSortingString(
  strIn: string,
  itemId: number,
  paddingCharacters = SORT_PAD_LENGTH
) {
  return `${strIn}${UNICODE_NULL_CHARACTER}${itemId.toString().padStart(paddingCharacters, '0')}`;
}

export function uncreateSortingString(strIn: string, paddingCharacters = SORT_PAD_LENGTH) {
  if (strIn[strIn.length - paddingCharacters - 1] !== UNICODE_NULL_CHARACTER) {
    return strIn;
  }
  return strIn.slice(0, -paddingCharacters - 1);
}

When it comes to store the records in Redis, we have:

      const createAndSaveResult = await ContactClass.ContactRepository.createAndSave({
        contactId: contactId,
        firstName: createSortingString(contactRecord.firstName, contactId),
        lastName: createSortingString(contactRecord.lastName, contactId),
        email: contactRecord.email,
      });

When it comes to extract records in a sorted order, we use:

static async getContactRecords(
    listId: number,
    offset: number,
    limit: number,
    sortColumn: keyof ContactData, // 'firstName' / 'lastName' / 'contactId'
    isColumnAscending: boolean,
  ) {
    const search = ContactClass.ContactRepository.search();
    const searchPart = isColumnAscending
      ? search.sortAscending(sortColumn)
      : search.sortDescending(sortColumn);
    const results = await searchPart.return.page(offset, limit);
    // Remove the entityId, that is redis specific and we don't need it
    return results.map((x) => {
      const y = x.toJSON();
      delete y.entityId;
      y.firstName = uncreateSortingString(y.firstName);
      y.lastName = uncreateSortingString(y.lastName);

      return y;
    });
  }

My work-around works - it fetches data from a database, caches it, and passes it on to the user in the correct sorted order. It passes tests. I'm happy to keep this. But I am wondering if I am missing something about Redis, and writing over-complicated code to compensate.

Thank you very much.

[guyroyse]

Ya. We should do that. RediSearch supports it, we're just not implementing it. I'll mark this as an enhancement. Thanks for point it out!