Case insensitive order_by with dynamic field

Questions / Help
,
1

I’m trying to return list sorted by the give field (attrs.sort_attr), but i need the list to be sorted case-insensitive. I think I may need to use a fragment but I’m not sure how to insert it into my existing code.

list
  |> order_by([b, s, r], {^attrs.sort_dir, field(b, ^attrs.sort_attr)})
  |> group_by([b, s, r], b.id)
2

there is citext (case insensitive text) extension for PostgreSQL, which create a field with a case insensitive index. You can add a migration with…

execute "CREATE EXTENSION IF NOT EXISTS citext", "DROP EXTENSION IF EXISTS citext"

And then declare fields like that…

add :full_name, :citext, null: false
add :last_name, :citext, null: false
add :first_name, :citext

Then You don’t need to worry about case insensitive ordering, the database will do it for You.

2 Likes
3

See order_by_track_lower function

# file: music_db/priv/repo/playground.exs
#
# http://www.pragmaticprogrammer.com/titles/wmecto
# https://pragprog.com/titles/wmecto/source_code
# http://media.pragprog.com/titles/wmecto/code/wmecto-code.zip
#
# pg_ctl -D /usr/local/var/postgres start
# mix format ./priv/repo/playground.exs
# mix run ./priv/repo/playground.exs
#

defmodule AppInfo do
  def string() do
    Application.loaded_applications()
    |> Enum.map(&to_app_keyword/1)
    |> Enum.sort_by(&map_app_name/1)
    |> Enum.map_join(", ", &app_keyword_to_string/1)
  end

  defp to_app_keyword({app, _, vsn}),
    do: {app, vsn}

  defp app_keyword_to_string({app, vsn}),
    do: "#{app}: #{vsn}"

  defp map_app_name({app, _}),
    do: app
end

defmodule Playground do
  import Ecto.Query
  alias MusicDB.Repo
  alias MusicDB.{Artist, Album, Track}

  def order_by_track_lower(query, direction, field),
    do: order_by(query, [_, _, s], {^direction, fragment("lower(?)", field(s, ^field))})

  def play do
    IO.puts(AppInfo.string())

    # Ecto.Adapters.SQL.to_sql(:all, Repo, query)

    direction = :asc
    field = :title

    from(a in Artist,
      join: m in Album,
      on: m.artist_id == a.id,
      join: t in Track,
      on: t.album_id == m.id,
      select: t
    )
    |> order_by_track_lower(direction, field)
    |> Repo.all()
  end
end

IO.inspect(Playground.play())

$ mix run ./priv/repo/playground.exs
asn1: 5.0.7, compiler: 7.2.7, connection: 1.0.4, crypto: 4.3.3, db_connection: 2.0.3, decimal: 1.6.0, ecto: 3.0.5, ecto_sql: 3.0.3, elixir: 1.7.4, hex: 0.18.2, inets: 7.0.2, kernel: 6.1.1, logger: 1.7.4, mix: 1.7.4, music_db: 0.1.0, poison: 3.1.0, postgrex: 0.14.1, public_key: 1.6.3, ssl: 9.0.3, stdlib: 3.6, telemetry: 0.2.0

13:04:31.285 [debug] QUERY OK source="artists" db=4.4ms decode=0.6ms queue=1.4ms
SELECT
  t2."id",
  t2."title",
  t2."duration",
  t2."index",
  t2."number_of_plays",
  t2."inserted_at",
  t2."updated_at",
  t2."album_id"
FROM
  "artists" AS a0
  INNER JOIN "albums" AS a1 ON a1."artist_id" = a0."id"
  INNER JOIN "tracks" AS t2 ON t2."album_id" = a1."id"
ORDER BY lower(t2."title") []
[
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 1,
    duration: 693,
    duration_string: nil,
    id: 4,
    index: 4,
    inserted_at: ~N[2018-11-06 13:40:37],
    number_of_plays: 0,
    title: "All Blues",
    updated_at: ~N[2018-11-06 13:40:37]
  },
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 3,
    duration: 489,
    duration_string: nil,
    id: 20,
    index: 10,
    inserted_at: ~N[2018-11-06 13:40:37],
    number_of_plays: 0,
    title: "All of You",
    updated_at: ~N[2018-11-06 13:40:37]
  },

...

  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 3,
    duration: 337,
    duration_string: nil,
    id: 12,
    index: 2,
    inserted_at: ~N[2018-11-06 13:40:37],
    number_of_plays: 0,
    title: "You Must Believe In Spring",
    updated_at: ~N[2018-11-06 13:40:37]
  }
]
$
4

Thanks, I actually just figured this out and ended up with the same solution, although the postgres extension does sound like something I should look into going forward.

  list
  |> order_by(
    [sr_1, b, sr_2],
    {^attrs.sort_dir, fragment("lower(?)", field(sr_1, ^attrs.sort_attr))}
  )
  |> group_by([sr_1, b, sr_2], [b.id, sr_1.id])
1 Like
5

Would adding :citext change any other aspect?

6

No, but some fields needs to be citext, email for example…