How to sort by a String field in a case insensitive way?

[MaccaCHAN]

With the default way of sorting, it is case sensitive. If I would like to sort case insensitively, what is the most direct way to do this?

I can achieve the result I wanted by join fields like below. I am wondering if there are some more direct way to achieve this. Thanks.

@derive {
    Flop.Schema,
    filterable: [],
    sortable: [:lower_first_name, :lower_last_name],
    adapter_opts: [
      join_fields: [
          lower_first_name: [
          binding: :extra_fields,
          field: :lower_first_name,
          ecto_type: :string
        ],
     lower_last_name: [
          binding: :extra_fields,
          field: :lower_last_name,
          ecto_type: :string
        ],
      ],
    ]
  }

params = %Flop{
      order_by: ["lower_first_name", "lower_last_name"],
      order_directions: ["asc", "desc"]
    }

   extra_fields_query =
      from(u1 in User,
        where: u1.id == parent_as(:user).id,
        select: %{
          lower_first_name: fragment("LOWER(?)", u1.first_name),
          lower_last_name: fragment("LOWER(?)", u1.last_name)
        }
      )

    from(u in User, 
      as: :user)
    |> join(:inner_lateral, [user: u], f in subquery(extra_fields_query),
      on: true,
      as: :extra_fields
    )
    |> Flop.validate_and_run!(
      params,
      for: User
    )

[woylie]

Your solution is looks good. There are some other solutions you could consider:

• If you use Postgres, you can change the column type to citext. That way, you don't have to consider casing in any queries at all.
• alias field - Requires you to select the lower case name, doesn't work with filters or cursor pagination.
• Change the collation to a _ci variant for the fields in the query. This doesn't save you from the fragment and the sub query or alias field, though.

For the solutions that require fragments, there isn't anything that Flop could do for you, unfortunately, since I don't want to use any adapter-specific fragments.

[MaccaCHAN]

Thanks for the reply. For alias field method, could you give me an example? Thanks.

I just feel strange that I have to use a sub query and lateral join for a field that is in the same table.

[woylie]

I just feel strange that I have to use a sub query and lateral join for a field that is in the same table.

I know it's strange, but Postgres is usually able to optimize such lateral joins on simple sub queries. Please confirm the query plan.

There's an example for alias fields in the documentation section I linked above.

[aglassman]

@MaccaCHAN I think citext is a good option as well.

For either approach, you'll want to consider how querying on lowercase will impact performance if have an index on fields you're trying to do a case insensitive search (Even with citext). https://www.postgresql.org/docs/current/citext.html#CITEXT-LIMITATIONS

I think custom fields would also be a good solution, and probably very reusable with less custom query code as you need to reuse the approach. I haven't tried compiling or running the code below, but I think it would work.

    adapter_opts: [
      custom_fields: [
        ci_last_name: [
          filter: {__MODULE__, :ci_equals, [field: :last_name]},
          ecto_type: :string,
          operators: [:==]
        ]
      ]
    ]

# CustomFilters
  def ci_search(q, %Flop.Filter{value: value, op: :==}, opts) do
    with schema_field when is_atom(schema_field) <- Keyword.get(opts, :field),
         {:ok, value} <- Ecto.Type.cast(:string, value) do
        where(q, [r], fragment("LOWER(?) == LOWER(?)", field(r, ^schema_field), ^value))

      _error ->
        q
    end
  end

params = %Flop{
      order_by: ["lower_first_name", "lower_last_name"],
      order_directions: ["asc", "desc"],
      filters: [%{field: :ci_last_name, op: :==, value: "smith"}]
    }