Questions & Help>Questions

Dynamic Queries in Ecto

#ecto#ecto-query
fireproofsocks

fireproofsocks

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 Ecto.Query — Ecto v3.14.0

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!

14 comments
#ecto#ecto-query
8223 14

Showing Posts 1 to 10

LostKobrakai

LostKobrakai 

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

  1. There’s where: … and or_where: … or where: (p.published == true or p.preview == true) and p.id > 100
    Ecto.Query — Ecto v3.14.0
    Ecto.Query — Ecto v3.14.0

  2. There are bindingless operations and dynamic does probably also handle it’s own “binding”.
    Ecto.Query — Ecto v3.14.0

  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”.

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
Post #1
fireproofsocks

fireproofsocks OP

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?

0
Post #2
LostKobrakai

LostKobrakai

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

0
Post #3
fireproofsocks

fireproofsocks OP

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!

0
Post #4
LostKobrakai

LostKobrakai

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

0
Post #5
fireproofsocks

fireproofsocks OP

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…

0
Post #6
benwilson512

benwilson512

Author of Craft GraphQL APIs in Elixir with Absinthe

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
Post #7
fireproofsocks

fireproofsocks OP

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
Post #8
benwilson512

benwilson512

Author of Craft GraphQL APIs in Elixir with Absinthe

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
Post #9
peerreynders

peerreynders 

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
Post #10

Where Next? Top

View thread on forum (has 14 replies!)
ecto
ecto-query
Home Questions & Help>Questions
23 8178 14
Last post
REPLY VIEW THREAD WITH 14 POSTS

Trending in Questions Top

stjefim
Questions & Help>Questions

Durable Workflow Execution (temporal alternative for elixir)
Hello! Suppose you are building workflow (order / task / payment) processing system with the following requirements: Each workflow con...
/oban#workflow#durable-execution
22 4198 10
New
Blokh
Questions & Help>Questions

Huge CSV processing that needs to be processed hourly - any tips?
Hey guys, I’ve got a huge CSV ( around 10 GB ) that needs to be processed hourly Do you guys have any suggestions what is the best prac...
/phoenix
10 1191 5
New
kszambelanczyk
Questions & Help>Questions

Delete file on S3 with Waffle
Hello! Could someone please give me a help/sample code, how to delete a file from s3 using waffle/waffle_ecto from Phoenix app. I creat...
/phoenix#waffle#waffle-ecto
1 695 1
New
Onor.io
Questions & Help>Questions

How To Create A Select Control From A Lookup Table?
I have what I’ve heard referred to as a “lookup table” in my database. This is a way of assigning codes to common values. One common lo...
/phoenix
3 243 5
New
jaybe78
Questions & Help>Questions

How to sync chats after dropped or broken connections in an online chat system?
Hello, I’m developing a online persistent chat system (what’s app) like using elixir/dynamodb/aws for a mobile app(flutter). The diffic...
#ets#troubleshooting#learning-elixir#chat#dynamodb
0 311 6
New
Trolleger
Questions & Help>Questions

Proper way to send channel events to multiple different people for live updates?
What approach to take when sending live updates to “random” users Hi! I have a question, I have a little chat app, and when I create a DM...
/phoenix#channels
2 689 2
New
widianto
Questions & Help>Questions

How to make first contribution to Phoenix project?
I think I’ve found a small improvement I could contribute to &lt;%= web_namespace %&gt;.CoreComponents (installer/templates/phx_web/compo...
/phoenix#contributing#open-source
5 353 5
New

Other Trending Topics Top

garrison
News>Announcing

Hobbes - a low-level distributed database for the Elixir programming language
Hobbes is a low-level distributed database for the Elixir programming language. Hobbes provides a simple, safe, and scalable storage lay...
#library#database#distributed#hobbes
302 4824 87
New
mcass19
News>Announcing

ExRatatui - Elixir bindings for the Rust ratatui terminal UI library
ExRatatui lets you cook up rich terminal UIs in Elixir, powered by Rust’s ratatui via Rustler NIFs. Build interactive terminal applicatio...
#library#rust#terminal#tui
58 2534 15
New
Damirados
News>Announcing

Emerge & Solve - a GUI framework for Elixir
Hello everyone. After busy few months I am happy to announce v0.1.0 of Emerge &amp; Solve. They are GUI (Emerge) and State management (S...
/nerves#library#frameworks#gui#native#state
59 2300 23
New
netoum
News>Announcing

Corex - Accessible and unstyled UI Phoenix components
Corex is an accessible, unstyled UI component library for Phoenix that integrates Zag.js state machines using Vanilla JavaScript and Live...
/phoenix#library#ui#liveview#hooks#accessibility#component#tableau
61 2202 19
New
wintermeyer
News>Announcing

Vutuv.de - an open-source LinkedIn alternative built in Phoenix
There are three potential reasons for members of this forum to have a look at https://vutuv.de You are tired or annoyed of LinkedIn. Yo...
/phoenix#fediverse#project
76 1722 40
New
webofbits
News>Announcing

Aludel - LLM prompt evaluation workbench
Aludel - LLM Evaluation Workbench Aludel is an embeddable Phoenix LiveView dashboard for evaluating and comparing LLM prompts across mult...
/phoenix#library#liveview#ai
27 1754 40
New
Back to top

Questions & Help>Questions

Questions Questions ❯

Latest on Elixir Forum

Elixir Forum ❯

Sponsor Spotlight

Manning

Master modern tools, write cleaner code, and tackle real-world challenges with Manning books, videos, and courses. Start building today.

Our Sponsors

Dashbit

Build Elixir applications with speed and confidence.

EEF

Supporting innovation across the BEAM ecosystem.

Coders51

We build reliable cloud platforms for business-critical systems.

AppSignal

Catch errors, track performance, monitor hosts and more.

Honeybadger

Error tracking, logs, uptime, and Just Enough APM. Built for developers.

Defacto

Bringing learning, work and compliance together since 1987.

WyeWorks

Enabling companies to succeed by building software people love.

Potions

The deployment platform built for Elixir: PaaS ease, VPS control.

Software Mansion

The team behind Membrane, Popcorn, LiveDebugger. Available for hire.

Pragmatic Studio

Courses that'll move you from confusion to "Aha, now I get it!"

View all our sponsors ❯

Categories:

Sub Categories:

Forums

Popular Tags

Our Sponsors

Dashbit

Build Elixir applications with speed and confidence.

EEF

Supporting innovation across the BEAM ecosystem.

Coders51

We build reliable cloud platforms for business-critical systems.

AppSignal

Catch errors, track performance, monitor hosts and more.

Honeybadger

Error tracking, logs, uptime, and Just Enough APM. Built for developers.

Defacto

Bringing learning, work and compliance together since 1987.

WyeWorks

Enabling companies to succeed by building software people love.

Potions

The deployment platform built for Elixir: PaaS ease, VPS control.

Software Mansion

The team behind Membrane, Popcorn, LiveDebugger. Available for hire.

Pragmatic Studio

Courses that'll move you from confusion to "Aha, now I get it!"

View all our sponsors ❯

We're in Beta

About us Mission Statement

Options

Thread Display Mode




Thread Preview

Skip Thread Previews