Dynamic Queries in Ecto

Questions / Help
,
1

I have once again nose-dived into the treeline while attempting to follow the official docs… I’m trying to stay positive here, but I am really feeling malnourished when it comes to nutritious examples. I would love some help clarifying the mystery presented on https://hexdocs.pm/ecto/Ecto.Query.html#dynamic/2

dynamic = false

dynamic =
  if params["is_public"] do
    dynamic([p], p.is_public or ^dynamic)
  else
    dynamic
  end

dynamic =
  if params["allow_reviewers"] do
    dynamic([p, a], a.reviewer == true or ^dynamic)
  else
    dynamic
  end

from query, where: ^dynamic

  1. First off, how might we actually pass in a value from the parameters into the where condition? The examples conveniently sidesteps that critical use-case. Like what if you want to filter a list of posts by the author? Would that be something like this?

    dynamic =
    if params[“author”] do
    dynamic([p], p.author = params[“author”] or ^dynamic)
    else
    dynamic
    end

  2. What’s up with the or and and in these? If I assume all the filtering parameters I provide must be fulfilled, doesn’t that mean that I should always use “and”? What’s up with this or ^dynamic? Can someone explain how and/or affects this and can someone explain this bizarre syntax? The only toe-hold my brain can get on this at present comes from some old-school query-string concatenation where the select criteria would be WHERE 1 – then each subsequent clause could always begin with “AND condition=something”. Is that what’s going on?

  3. What’s up with the naked from query, where: ^dynamic ? What’s going on there? How can I actually use that in a query? I’m used to seeing something like query = from p in Post – I don’t understand that at all, really, but at least it’s pervasive throughout the Ecto Query docs.

  4. How do we use this in a query that involves a join? It seems that it trips over the “where” clause that defines the join?

If I can get some help wrapping my head around this I can put together a PR that provides some examples that are easier to follow. Thanks for any guidance!

2 Likes
2 
author = params[“author”]
author_selection = dynamic([p], p.author == ^author)

https://hexdocs.pm/ecto/Ecto.Query.html#module-interpolation-and-casting

  1. There’s where: … and or_where: … or where: (p.published == true or p.preview == true) and p.id > 100
    https://hexdocs.pm/ecto/Ecto.Query.html#where/3
    https://hexdocs.pm/ecto/Ecto.Query.html#or_where/3

  2. There are bindingless operations and dynamic does probably also handle it’s own “binding”.
    https://hexdocs.pm/ecto/Ecto.Query.html#module-bindingless-operations

  3. join does not use where: …, but on: to determine the join condition. where in dynamic does work on joined resources just like without dynamic:

from a in Article.
 join: c in Comment, on: a.id == c.article_id
 where: c.author_id == ^id

or

Article 
|> join(:inner, [a], c in Comment, a.id == c.article_id) 
|> where([_, c], c.author_id == ^id)

The a.id == c.article_id part could by replaced with a “dynamic”.
https://hexdocs.pm/ecto/Ecto.Query.html#join/5

I’m aware that ecto queries are complex, especially as they support two syntaxes, but all of the things you asked about seem to be already documented in that module. I can certainly understand that this example might not be optimal, but you should keep in mind that those are a ongoing effort and especially as someone knowing the system it’s sometimes hard to anticipate the difficulties someone less knowledgeable might have reading any of those examples.

1 Like
3

Thank you, this is vastly more useful than what is currently in the docs.

The join examples on the Query page frequently use where… there must be cases when it is equivalent to on?

The “or” that was (is) specifically bothering me is in here: dynamic([p], p.is_public or ^dynamic) – why are we interpolating the dynamic variable again? Especially into a condition that will always be false?

4

where is like in SQL just where. The on part in joins can be skipped for join: c in assoc(a, :comments), because by using the association between schemas ecto does already know how to join things.

I doubt the false being a condition in that example, but rather a “blank slate”. If you join …, where: ^dynamic and dynamic = false then it’s probably just not adding a where clause at all. dynamic([p], p.is_public or ^dynamic) this is then just “take the prev. dynamic and add onto it”. Though to be honest I’ve not worked that much with dynamic by now and might be wrong on on that one

5

Interesting. Thanks for the further clarifications! I’ve got it working now, it just ends up being pretty long.

Have you ever tried to use a loop to define all filterable columns? Something like this:

dynamic = false

    for column <- ["id", "state", "country"] do
      dynamic =
          if filter[column] do
            dynamic([a], a.column == ^filter[column] or ^dynamic)
          else
            dynamic
          end

Although that doesn’t work because of the a.column isn’t interpreted as a variable, but that was the idea. Any suggestions? Thanks!

6

Enum.reduce can do that quite nicely (you need to aggregate the dynamic not overwrite it on each iteration).

https://hexdocs.pm/ecto/Ecto.Query.API.html#field/2

7

Nice. Ok, I see how to use the field() function now, thanks! Is there any way to dynamically set the operator? For flexibility, it would be really useful for CRUD searches and the like to be able to have a function that would allow the user to specify which operator to use for each field – the default would be =, but it would be cool to dynamically specify gt for > or trigger a LIKE match of some sort…

8

I’m not entirely sure what the point of commentary like this is. The Elixir community is still relatively small, and the docs and code that exist are almost always the work of a limited number of people doing the best they can with the time they have available.

That isn’t to say that they’re perfect or even close. I’m confident that you’re right, that the docs could use more examples. Nonetheless, as the recipient of code, docs (even if flawed), personalized help, starting a post with a complaint just depresses those you’re seeking help from.

3 Likes
9

Sorry, you’re right: there’s nothing positive in commentary like that. I apologize. I was writing from a point of extreme frustration and exhaustion. I would like to submit some examples once I can get things working.

6 Likes
10

We’ve definitely all been there, and I understand. Thanks for the apology, and I’m sure that the docs will benefit greatly from your examples!

1 Like
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
12

Such clarifications to dynamic/2 docs would be really welcome too. :slight_smile:

3 Likes
13

This passage is very informative. Assuming that it is accurate, it should be part of the dynamic/2 section in the docs. Also, the general advice (part of the docs) that “one should never need to invoke [Query.API functions] directly” seems to be misleading unless the ubiquity of field/2 contravenes best practice.

(This is my first post, I hope that this is the right place for stimulating improvements in the documentation.)

14

Ecto is open source. You could add those parts yourself and submit a pull request on github.

1 Like
15

Hello people,

I started learning Elixir and was trying to create a dynamic filter to send an object with non-mandatory properties, so I came up with this solution.

I hope it helps.

def search filter do

    query = from user in User, where: 1==1

    filterByName = fn (query, filter) ->
      unless is_nil Map.get(filter, :name) do
        from(user in query, where: user.name == ^filter.name)
      else
        query
      end
    end

    filterByEmail = fn (query, filter) ->
      unless is_nil Map.get(filter, :email) do
        from(user in query, where: user.email == ^filter.email)
      else
        query
      end
    end

    filterByBirth = fn (query, filter) ->
      unless is_nil Map.get(filter, :birth) do
        from(user in query, where: user.birth == ^filter.birth)
      else
        query
      end
    end

    query = filterByName.(query, filter)
    query = filterByEmail.(query, filter)
    query = filterByBirth.(query, filter)

    Repo.all query
  end
1 Like