Function buildSortFieldClause

apps/sim/lib/table/sql.ts:577–600

* Builds a single ORDER BY clause for a field. * Timestamp fields use direct column access, others use JSONB text extraction. * Numeric and date columns are cast to appropriate types for correct sorting. * * @param tableName - The table name * @param field - The field name to sort by * @param

(
  tableName: string,
  field: string,
  direction: 'asc' | 'desc',
  columnType: ColumnType | undefined
)

 * @param columnType - Optional column type for type-aware sorting
 */
function buildSortFieldClause(
  tableName: string,
  field: string,
  direction: 'asc' | 'desc',
  columnType: ColumnType | undefined
): SQL {
  const escapedField = field.replace(/'/g, "''")
  const directionSql = direction.toUpperCase()

  if (field === 'createdAt' || field === 'updatedAt') {
    return sql.raw(`${tableName}.${escapedField} ${directionSql}`)
  }

  const jsonbExtract = `${tableName}.data->>'${escapedField}'`
  const cast = jsonbCastForType(columnType)

  if (cast === null) {
    // Sort as text (string, boolean, json, or unknown types)
    return sql.raw(`${jsonbExtract} ${directionSql}`)
  }

  // NULLS LAST so rows with null/invalid values sort to the bottom regardless of direction
  return sql.raw(`(${jsonbExtract})::${cast} ${directionSql} NULLS LAST`)
}