Questions & Help>Questions

Dynamic Queries in Ecto

#ecto#ecto-query
fireproofsocks

fireproofsocks

Dynamic Queries in Ecto

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!

14 comments
#ecto#ecto-query
23 7895 14

Most Liked

fireproofsocks

fireproofsocks

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 #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 #11
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 #8
josevalim

josevalim

Creator of Elixir

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

3
Post #12
LostKobrakai

LostKobrakai

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

Where Next?

View thread on forum (has 14 responses!)
ecto
ecto-query
Home Questions & Help>Questions
#ecto#ecto-query
23 7897 14
Last post

Popular in Questions Top

pgiesin
Questions & Help>Questions

Ecto :database Not Found
This should be a simple problem but I just can’t seem to figure it out. I have a standalone Elixir app that won’t find the database. Dep...
#ecto#postgres#troubleshooting#ecto-migration
7 14214 22
New
ycv005
Questions & Help>Questions

BEAM file compliation error after degrading Erlang version from 22 to 21
I have followed this StackOverflow post to install the specific version of Erlang. And When I am running mix ecto.setup then getting fol...
/phoenix#erlang
33 15699 4
New
Werner
Questions & Help>Questions

Mix local.hex warning - ‘Authenticity is not established by certificate path validation’
Hi, I’m using Ubuntu 18.04 and after updating to OTP-24.0 yesterday i have this warning when I run “mix local.hex”: 14:57:30.512 [warn] ...
#troubleshooting
47 14042 14
New
SoCreat
Questions & Help>Questions

What's the best ide/editor for elixir in 2021?
i’m a new one to elixir which editor can i use vs code? or atom? Thanks! :smiley:
#ide
68 37349 27
New
mgjohns61585
Questions & Help>Questions

Convert a string to an integer?
Could someone help me? I'm making my first elixir program, number guessing game. I can't figure out how to convert the user's guess from ...
#how-to-question
3 14200 6
New
electic
Questions & Help>Questions

DateTime Format to string?
Hi, I am new to Elixir. I am trying to use the DateTime component to insert a date into MySQL however the there seems to be no way to fo...
#datetime
19 24687 16
New
chrisalley
Questions & Help>Questions

How to describe many contexts in ExUnit without a hierarchy
ExUnit now has describe blocks which is a welcome addition coming from RSpec. In the docs, it states that nested hierarchies of describe ...
#testing#exunit
66 16773 4
New
Qqwy
Questions & Help>Questions

Updating structs: Map.put vs %Foo{oldfoo | new: value} vs put_in
Original source of discussion: This topic on the Pragmatic Programmers' Functional Web Development with Elixir, OTP, and Phoenix forum. ...
#maps#structs#pipeline#access
115 28410 31
New
romenigld
Questions & Help>Questions

Docker run Error loading shared library libstdc++.so.6 and libgcc_s.so.1
I am trying to run a deploy with docker and I successfully runned with this command: docker build -t romenigld/blog-prod . but when I t...
/phoenix#deployment#docker-compose
30 19932 15
New
siddhant3030
Questions & Help>Questions

How can I write a raw sql query?
Hi, I have to write a raw query for one of my project. But till now I have used ecto queries and don’t have much experience writing raw ...
/phoenix#ecto
13 19061 20
New

Other popular topics Top

JorisKok
Questions & Help>Questions

How is it possible to get 2 million websocket connections when you have 65536 available ports?
I have a server on AWS, and was running a load test using artillery. When looking at the Phoenix dashboard I see the Ports going to 100% ...
/phoenix
20 17308 4
New
Trevoke
Questions & Help>Questions

(EXIT) no process: the process is not alive or there's no process currently associated with the given name, possibly because its application isn't started
I have an umbrella app. Some of the apps inside depend on other apps in the umbrella, unsurprisingly. I'm writing a test for one of t...
#otp#umbrella#testing#exunit#errors
26 21431 11
New
nsuchy
Questions & Help>Questions

IEX in Windows Powershell?
Hi. I’ve noticed that Windows Powershell has it’s own IEX command and you cannot access Elixir’s IEX due to the conflict. This isn’t a cr...
#iex#microsoft-windows#windows#powershell
15 29309 4
New
vertexbuffer
Questions & Help>Questions

Deleting item from a list
Hello, can anybody help here..? I have a list of players and I what to delete an element, but every for loop the list is reverting to ori...
7 24097 4
New
lastday4you
Questions & Help>Questions

How to check Elixir version
I wanted to check elixir version in phoenix because i found that my elixir is 1.5 but when i use Enum.chunk_by it said the function is un...
#how-to-question
11 18647 3
New
myronmarston
Questions & Help>Questions

Typespecs - best way to spec keyword lists?
The Elixir Typespec docs show the following syntax for keyword lists in typespecs: # ... | [key: type] # keyword lis...
#dialyzer
52 14258 3
New
ashish173
Questions & Help>Questions

Difference in between :utc_datetime and :naive_datetime in Ecto
I am using Ecto timestamps with postgres, I can see the timestamps() use the :naive_dateime but for my use case I wanted to store the ti...
#ecto#timestamps
79 27907 15
New
josevalim
Official Elixir>Proposals

Behaviours, defoverridable and implementations
Hi everyone, One of the features added to Elixir early on to help integration with Erlang code was the idea of overridable function defi...
#official-elixir-proposal
111 22687 35
New
romenigld
Questions & Help>Questions

Docker run Error loading shared library libstdc++.so.6 and libgcc_s.so.1
I am trying to run a deploy with docker and I successfully runned with this command: docker build -t romenigld/blog-prod . but when I t...
/phoenix#deployment#docker-compose
30 19932 15
New
Nvim
Chat & Discussions>Discussions

Elixir vs Python
Elixir appears to be a superior language to Python. I don’t see any advantage of Python over Elixir. Are there any?
#discussion#python#elixir
82 17872 30
New
Back to top

Questions & Help>Questions

Questions Questions ❯

Latest on Elixir Forum

Elixir Forum ❯

Sponsor Spotlight

WyewWorks

Enabling companies to succeed by building software people love.

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.

Pragmatic Bookshelf

Practical resources that improve the lives of professional developers.

Manning

Develop your skills with books, videos, and courses.

ElixirCasts

Producing high quality Elixir screencasts since 2017.

Pragmatic Studio

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

View all our sponsors ❯

Categories:

Sub Categories:

Forums

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.

Pragmatic Bookshelf

Practical resources that improve the lives of professional developers.

Manning

Develop your skills with books, videos, and courses.

ElixirCasts

Producing high quality Elixir screencasts since 2017.

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