How to specify the order of enums in a live query

[literaleigh]

I have enums on my collection

const listings: {
    // ...
    id: string;
    verifiedStatus: "unverified" | "semi_verified" | "fully_verified";
    createdAt: Date;
    // ...
}[]

query.orderBy(
  ({ listing }) => listing.verifiedStatus,
)

and if i make a livequery and orderBy the enum property, they get sorted alphabetically. How do I order them to mirror the database enum ordering behavior?

[KyleAMathews]

We use standard schema to drive our schemas and they don't expose ordered enums as a type so can't do this automatically. So your best bet is to add another field that maps the enum ordering to an integer so you can sort on that.

[literaleigh]

Hi! i tried this approach but it seems to add another problem. where i have a on-demand mode collection and if i made an extra mapper field in my collection, then order by that mapper field, the parsed subsetOpts.sorts will send this orderby:

[{"expression":{"type":"ref","path":["verifiedStatusIndex"]},"direction":"desc","nulls":"first"}]

which will error our because that field does not exist on my database

this is my query for ref:

const listings = await db.query.listing.findMany({
  where,
  orderBy,
  extras: {
    verifiedStatusIndex:
      sql<number>`array_position(enum_range(NULL::verified_status), ${listing.verifiedStatus}) - 1`.as(
        'verified_status_index',
      ),
  },
})

[KyleAMathews]

Translate the query on the backend as well?

[literaleigh]

Translate the query on the backend as well?

this is whats happening on my api

const { filters: rawFilters, sorts: rawSorts } = data
const orderBy = rawSorts.map((s) => parseSortToDrizzle(s, listing))

const listings = await db.query.listing.findMany({
  where,
  orderBy,
  extras: {
    verifiedStatusIndex:
      sql<number>`array_position(enum_range(NULL::verified_status), ${listing.verifiedStatus}) - 1`.as(
        'verified_status_index',
      ),
  },
})

export function parseSortToDrizzle(sort: ParsedOrderBy, table: Table) {
  const tableCols = getTableColumns(table)
  const col = tableCols[sort.field[0]]

  if (!col) {
    throw new Error(`Column "${sort.field[0]}" does not exist on table`)
  }

  if (sort.direction === 'desc') {
    return desc(col)
  } else {
    return asc(col)
  }
}

my guess is you want me to add a check here and to map the index field to the back to the field that exists in my database?

i'd like to avoid that because i want to reuse this function to other collections as well. i hope we solve this on the library level because enums are very common and its unexpected for the ordering to not mirror the database functionality. 🙏