Dynamic Queries in Ecto

Questions / Help
,
11 
iex(1)> alias MusicDB.{Repo,Album,Track}
[MusicDB.Repo, MusicDB.Album, MusicDB.Track]
iex(2)> import Ecto.Query
Ecto.Query
iex(3)> dynamic_where = fn (album_id, params) ->
...(3)>   default = dynamic([t], t.album_id == ^album_id)
...(3)>   case params do
...(3)>     %{name: name, value: value, op: :gt} ->
...(3)>       dynamic([t], field(t, ^name) > ^value and ^default)
...(3)>     %{name: name, value: value} ->
...(3)>       dynamic([t], field(t, ^name) <= ^value and ^default)
...(3)>     _ ->
...(3)>       default
...(3)>   end
...(3)> end
#Function<12.127694169/2 in :erl_eval.expr/5>
iex(4)> album_id = 2
2
iex(5)> from(t in Track, where: ^dynamic_where.(album_id, %{})) |> Repo.all()

17:49:20.001 [debug] QUERY OK source="tracks" db=2.9ms decode=2.3ms
SELECT t0."id", t0."title", t0."duration", t0."index", t0."number_of_plays", t0."inserted_at", t0."updated_at", t0."album_id" FROM "tracks" AS t0 WHERE (t0."album_id" = $1) [2]
[
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 2,
    duration: 1061,
    duration_string: nil,
    id: 10,
    index: 5,
    inserted_at: ~N[2018-06-16 20:29:41.480612],
    number_of_plays: 0,
    title: "No Blues",
    updated_at: ~N[2018-06-16 20:29:41.480618]
  },
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 2,
    duration: 754,
    duration_string: nil,
    id: 9,
    index: 4,
    inserted_at: ~N[2018-06-16 20:29:41.480045],
    number_of_plays: 0,
    title: "Miles",
    updated_at: ~N[2018-06-16 20:29:41.480051]
  },
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 2,
    duration: 896,
    duration_string: nil,
    id: 8,
    index: 3,
    inserted_at: ~N[2018-06-16 20:29:41.479460],
    number_of_plays: 0,
    title: "Walkin'",
    updated_at: ~N[2018-06-16 20:29:41.479465]
  },
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 2,
    duration: 774,
    duration_string: nil,
    id: 7,
    index: 2,
    inserted_at: ~N[2018-06-16 20:29:41.478857],
    number_of_plays: 0,
    title: "Stella By Starlight",
    updated_at: ~N[2018-06-16 20:29:41.478864]
  },
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 2,
    duration: 1006,
    duration_string: nil,
    id: 6,
    index: 1,
    inserted_at: ~N[2018-06-16 20:29:41.478296],
    number_of_plays: 0,
    title: "If I Were A Bell",
    updated_at: ~N[2018-06-16 20:29:41.478302]
  }
]
iex(6)> from(t in Track, where: ^dynamic_where.(album_id, %{name: :duration, value: 800})) |> Repo.all()

17:49:20.013 [debug] QUERY OK source="tracks" db=2.0ms
SELECT t0."id", t0."title", t0."duration", t0."index", t0."number_of_plays", t0."inserted_at", t0."updated_at", t0."album_id" FROM "tracks" AS t0 WHERE ((t0."duration" <= $1) AND (t0."album_id" = $2)) [800, 2]
[
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 2,
    duration: 754,
    duration_string: nil,
    id: 9,
    index: 4,
    inserted_at: ~N[2018-06-16 20:29:41.480045],
    number_of_plays: 0,
    title: "Miles",
    updated_at: ~N[2018-06-16 20:29:41.480051]
  },
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 2,
    duration: 774,
    duration_string: nil,
    id: 7,
    index: 2,
    inserted_at: ~N[2018-06-16 20:29:41.478857],
    number_of_plays: 0,
    title: "Stella By Starlight",
    updated_at: ~N[2018-06-16 20:29:41.478864] 
  }
]
iex(7)> from(t in Track, where: ^dynamic_where.(album_id, %{name: :duration, value: 800, op: :gt})) |> Repo.all()

17:49:20.016 [debug] QUERY OK source="tracks" db=1.8ms
SELECT t0."id", t0."title", t0."duration", t0."index", t0."number_of_plays", t0."inserted_at", t0."updated_at", t0."album_id" FROM "tracks" AS t0 WHERE ((t0."duration" > $1) AND (t0."album_id" = $2)) [800, 2]
[
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 2,
    duration: 1061,
    duration_string: nil,
    id: 10,
    index: 5,
    inserted_at: ~N[2018-06-16 20:29:41.480612],
    number_of_plays: 0,
    title: "No Blues",
    updated_at: ~N[2018-06-16 20:29:41.480618]
  },
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 2,
    duration: 896,
    duration_string: nil,
    id: 8,
    index: 3,
    inserted_at: ~N[2018-06-16 20:29:41.479460],
    number_of_plays: 0,
    title: "Walkin'",
    updated_at: ~N[2018-06-16 20:29:41.479465]
  },
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 2,
    duration: 1006,
    duration_string: nil,
    id: 6,
    index: 1,
    inserted_at: ~N[2018-06-16 20:29:41.478296],
    number_of_plays: 0,
    title: "If I Were A Bell",
    updated_at: ~N[2018-06-16 20:29:41.478302]
  }
] 
iex(8)>

Though frankly:

iex(1)> alias MusicDB.{Repo,Album,Track}
[MusicDB.Repo, MusicDB.Album, MusicDB.Track]
iex(2)> import Ecto.Query
Ecto.Query
iex(3)> make_where = fn (query, album_id, params) ->
...(3)>   case params do
...(3)>     %{name: name, value: value, op: :gt} ->
...(3)>       from(p in query, where: field(p, ^name) > ^value and p.album_id == ^album_id) 
...(3)>     %{name: name, value: value} ->
...(3)>       from(p in query, where: field(p, ^name) <= ^value and p.album_id == ^album_id) 
...(3)>     _ ->
...(3)>       from(p in query, where: p.album_id == ^album_id) 
...(3)>   end
...(3)> end
#Function<18.127694169/3 in :erl_eval.expr/5>
iex(4)> album_id = 2
2
iex(5)> from(t in Track) |> make_where.(album_id, %{}) |> Repo.all()

18:08:35.201 [debug] QUERY OK source="tracks" db=2.7ms decode=2.0ms
SELECT t0."id", t0."title", t0."duration", t0."index", t0."number_of_plays", t0."inserted_at", t0."updated_at", t0."album_id" FROM "tracks" AS t0 WHERE (t0."album_id" = $1) [2]
[
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 2,
    duration: 1061,
    duration_string: nil,
    id: 10,
    index: 5,
    inserted_at: ~N[2018-06-16 20:29:41.480612],
    number_of_plays: 0,
    title: "No Blues",
    updated_at: ~N[2018-06-16 20:29:41.480618]
  },
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 2,
    duration: 754,
    duration_string: nil,
    id: 9,
    index: 4,
    inserted_at: ~N[2018-06-16 20:29:41.480045],
    number_of_plays: 0,
    title: "Miles",
    updated_at: ~N[2018-06-16 20:29:41.480051]
  },
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 2,
    duration: 896,
    duration_string: nil,
    id: 8,
    index: 3,
    inserted_at: ~N[2018-06-16 20:29:41.479460],
    number_of_plays: 0,
    title: "Walkin'",
    updated_at: ~N[2018-06-16 20:29:41.479465]
  },
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 2,
    duration: 774,
    duration_string: nil,
    id: 7,
    index: 2,
    inserted_at: ~N[2018-06-16 20:29:41.478857],
    number_of_plays: 0,
    title: "Stella By Starlight",
    updated_at: ~N[2018-06-16 20:29:41.478864]
  },
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 2,
    duration: 1006,
    duration_string: nil,
    id: 6,
    index: 1,
    inserted_at: ~N[2018-06-16 20:29:41.478296],
    number_of_plays: 0,
    title: "If I Were A Bell",
    updated_at: ~N[2018-06-16 20:29:41.478302]
  }
]
iex(6)> from(t in Track) |> make_where.(album_id, %{name: :duration, value: 800}) |> Repo.all()

18:08:35.213 [debug] QUERY OK source="tracks" db=2.0ms
SELECT t0."id", t0."title", t0."duration", t0."index", t0."number_of_plays", t0."inserted_at", t0."updated_at", t0."album_id" FROM "tracks" AS t0 WHERE ((t0."duration" <= $1) AND (t0."album_id" = $2)) [800, 2]
[
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 2,
    duration: 754,
    duration_string: nil,
    id: 9,
    index: 4,
    inserted_at: ~N[2018-06-16 20:29:41.480045],
    number_of_plays: 0,
    title: "Miles",
    updated_at: ~N[2018-06-16 20:29:41.480051]
  },
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 2,
    duration: 774,
    duration_string: nil,
    id: 7,
    index: 2,
    inserted_at: ~N[2018-06-16 20:29:41.478857],
    number_of_plays: 0,
    title: "Stella By Starlight",
    updated_at: ~N[2018-06-16 20:29:41.478864] 
  }
]
iex(7)> from(t in Track) |> make_where.(album_id, %{name: :duration, value: 800, op: :gt}) |> Repo.all()

18:08:35.216 [debug] QUERY OK source="tracks" db=2.1ms
SELECT t0."id", t0."title", t0."duration", t0."index", t0."number_of_plays", t0."inserted_at", t0."updated_at", t0."album_id" FROM "tracks" AS t0 WHERE ((t0."duration" > $1) AND (t0."album_id" = $2)) [800, 2]
[
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 2,
    duration: 1061,
    duration_string: nil,
    id: 10,
    index: 5,
    inserted_at: ~N[2018-06-16 20:29:41.480612],
    number_of_plays: 0,
    title: "No Blues",
    updated_at: ~N[2018-06-16 20:29:41.480618]
  },
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 2,
    duration: 896,
    duration_string: nil,
    id: 8,
    index: 3,
    inserted_at: ~N[2018-06-16 20:29:41.479460],
    number_of_plays: 0,
    title: "Walkin'",
    updated_at: ~N[2018-06-16 20:29:41.479465]
  },
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 2,
    duration: 1006,
    duration_string: nil,
    id: 6,
    index: 1,
    inserted_at: ~N[2018-06-16 20:29:41.478296],
    number_of_plays: 0,
    title: "If I Were A Bell",
    updated_at: ~N[2018-06-16 20:29:41.478302]
  }
] 
iex(8)>

Ecto queries are composable. Ecto.Query.dynamic/2 comes in handy when you need your where conditions to be composable.

Conditions are composed with and or or:

  • when you compose with or and you don’t need the condition, you default to false instead.
  • when you compose with and and you don’t need the condition, you default to true instead.

Furthermore if you are simply anding the conditions you need, you probably don’t need Ecto.Query.dynamic/2:

iex(1)> alias MusicDB.{Repo,Album,Track}
[MusicDB.Repo, MusicDB.Album, MusicDB.Track]
iex(2)> import Ecto.Query
Ecto.Query
iex(3)> make_where = fn (query, album_id, params) ->
...(3)>   default = from(q in query, where: q.album_id == ^album_id)
...(3)>   case params do
...(3)>     %{name: name, value: value, op: :gt} ->
...(3)>       from(p in default, where: field(p, ^name) > ^value) 
...(3)>     %{name: name, value: value} ->
...(3)>       from(p in default, where: field(p, ^name) <= ^value) 
...(3)>     _ ->
...(3)>       default
...(3)>   end
...(3)> end
#Function<18.127694169/3 in :erl_eval.expr/5>
iex(4)> album_id = 2
2
iex(5)> from(t in Track) |> make_where.(album_id, %{}) |> Repo.all()

18:47:30.295 [debug] QUERY OK source="tracks" db=2.8ms decode=2.2ms
SELECT t0."id", t0."title", t0."duration", t0."index", t0."number_of_plays", t0."inserted_at", t0."updated_at", t0."album_id" FROM "tracks" AS t0 WHERE (t0."album_id" = $1) [2]
[
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 2,
    duration: 1061,
    duration_string: nil,
    id: 10,
    index: 5,
    inserted_at: ~N[2018-06-16 20:29:41.480612],
    number_of_plays: 0,
    title: "No Blues",
    updated_at: ~N[2018-06-16 20:29:41.480618]
  },
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 2,
    duration: 754,
    duration_string: nil,
    id: 9,
    index: 4,
    inserted_at: ~N[2018-06-16 20:29:41.480045],
    number_of_plays: 0,
    title: "Miles",
    updated_at: ~N[2018-06-16 20:29:41.480051]
  },
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 2,
    duration: 896,
    duration_string: nil,
    id: 8,
    index: 3,
    inserted_at: ~N[2018-06-16 20:29:41.479460],
    number_of_plays: 0,
    title: "Walkin'",
    updated_at: ~N[2018-06-16 20:29:41.479465]
  },
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 2,
    duration: 774,
    duration_string: nil,
    id: 7,
    index: 2,
    inserted_at: ~N[2018-06-16 20:29:41.478857],
    number_of_plays: 0,
    title: "Stella By Starlight",
    updated_at: ~N[2018-06-16 20:29:41.478864]
  },
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 2,
    duration: 1006,
    duration_string: nil,
    id: 6,
    index: 1,
    inserted_at: ~N[2018-06-16 20:29:41.478296],
    number_of_plays: 0,
    title: "If I Were A Bell",
    updated_at: ~N[2018-06-16 20:29:41.478302]
  }
]
iex(6)> from(t in Track) |> make_where.(album_id, %{name: :duration, value: 800}) |> Repo.all()

18:47:30.307 [debug] QUERY OK source="tracks" db=2.0ms
SELECT t0."id", t0."title", t0."duration", t0."index", t0."number_of_plays", t0."inserted_at", t0."updated_at", t0."album_id" FROM "tracks" AS t0 WHERE (t0."album_id" = $1) AND (t0."duration" <= $2) [2, 800]
[
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 2,
    duration: 754,
    duration_string: nil,
    id: 9,
    index: 4,
    inserted_at: ~N[2018-06-16 20:29:41.480045],
    number_of_plays: 0,
    title: "Miles",
    updated_at: ~N[2018-06-16 20:29:41.480051]
  },
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 2,
    duration: 774,
    duration_string: nil,
    id: 7,
    index: 2,
    inserted_at: ~N[2018-06-16 20:29:41.478857],
    number_of_plays: 0,
    title: "Stella By Starlight",
    updated_at: ~N[2018-06-16 20:29:41.478864] 
  }
]
iex(7)> from(t in Track) |> make_where.(album_id, %{name: :duration, value: 800, op: :gt}) |> Repo.all()

18:47:30.311 [debug] QUERY OK source="tracks" db=1.9ms
SELECT t0."id", t0."title", t0."duration", t0."index", t0."number_of_plays", t0."inserted_at", t0."updated_at", t0."album_id" FROM "tracks" AS t0 WHERE (t0."album_id" = $1) AND (t0."duration" > $2) [2, 800]
[
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 2,
    duration: 1061,
    duration_string: nil,
    id: 10,
    index: 5,
    inserted_at: ~N[2018-06-16 20:29:41.480612],
    number_of_plays: 0,
    title: "No Blues",
    updated_at: ~N[2018-06-16 20:29:41.480618]
  },
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 2,
    duration: 896,
    duration_string: nil,
    id: 8,
    index: 3,
    inserted_at: ~N[2018-06-16 20:29:41.479460],
    number_of_plays: 0,
    title: "Walkin'",
    updated_at: ~N[2018-06-16 20:29:41.479465]
  },
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 2,
    duration: 1006,
    duration_string: nil,
    id: 6,
    index: 1,
    inserted_at: ~N[2018-06-16 20:29:41.478296],
    number_of_plays: 0,
    title: "If I Were A Bell",
    updated_at: ~N[2018-06-16 20:29:41.478302]
  }
] 
iex(8)>

For relatively simple condition composition (more like “chaining” really) with or Ecto.Query.or_where/3 can be used in exactly the same manner.

dynamic/2 becomes necessary when you are composing deeply nested, optional conditions

WHERE
  (... AND ... AND ... AND (... OR (... AND ...)))
  OR (.. AND ((... AND ...) OR ...) AND ...)
  OR (... AND ... AND ...)
  OR ((... OR (... AND ...)) AND ...)
5 Likes