Case insensitive order_by

[bertyhell]

Is there a way to order_by but case insensitive without having to add a new property?

eg this dataset:

subjects: [
  {
    description: "English"
  },
  {
    description: "math"
  },
  {
    description: "alchemy"
  },
]

We would like to fetch these alphabetically sorted:

{
  subjects(order_by: {description: asc}) {
    description
  }
}

We get:

[
  {
    description: "English"
  },
  {
    description: "alchemy"
  },
  {
    description: "math"
  },
]

Maybe adding an iorder_by property, similar to ilike so we get this result:

[
  {
    description: "alchemy"
  },
  {
    description: "English"
  },
  {
    description: "math"
  },
]

[wwwdepot]

Just make a custom function with ORDER BY lower(description)

[nawgz]

@wwwdepot How can I do that? I do not understand and from looking thru the Hasura API Reference I see no further mention to this. Is it something I can do as part of my GraphQL query or do I have to do something cumbersome like create a view or a custom SQL function?

[idesignpixels]

Also having an issue with this was hoping for something like asc, ciasc (case insensitive ascending) or something similar

[jflambert]

Just make a custom function with ORDER BY lower(description)

How is this helpful? We can't order by custom functions in graphql.

@rikinsk any progress on adding asc/iasc/desc/idesc sort options? Do you have a workaround?

[bertyhell]

currently we solved this by creating a computed column in hasura with the lowercase equivalent of the other column: https://hasura.io/docs/latest/graphql/core/databases/postgres/schema/computed-fields.html

[jflambert]

@bertyhell smart!!! but I'd have to add that to hundreds of fact tables.

[idesignpixels]

Did the same in the end

[adriexnet]

Hi @rikinsk any news about the case insensitive option? it will be really useful

[ian-emsens-shd]

Any update on this? 👀