Dynamic SQL Queries With Elixir

Building a MongoDB-style Query API for Postgres with Ecto

A Quick Word on Designing a Querying API

db.inventory.find( { $or: [ { status: "A" }, { qty: { $lt: 30 } } ] } )

Dynamic Queries with Ecto

filter = %{"artist" => "ABBA", "year" => 1974}def filter(filter) do
from(s in Songs)
|> maybe_filter_artist(filter)
|> maybe_filter_year(filter)
|> Repo.all()
end
defp maybe_filter_artist(query, %{"artist" => artist}),
do: where(query, [s], s.artist == ^artist)
defp maybe_filter_artist(query, _), do: querydefp maybe_filter_year(query, %{"year" => year}),
do: where(query, [s], s.year == ^year)
defp maybe_filter_year(query, _), do: query

field/2

filter = %{
"artist" => "ABBA",
"year" => 1974,
"language" => "English"
}
def filter(filter) do
from(s in Songs)
|> maybe_filter("artist", filter["artist"])
|> maybe_filter("year", filter["year"])
|> maybe_filter("language", filter["language"])
|> Repo.all()
end
@allowed_fields ~w{artist year language}
defp maybe_filter(query, _field, nil),
do: query
defp maybe_filter(query, field, value)
when field in @allowed_fields do
field = to_existing_atom(field)
query
|> where([s], field(s, ^field) == ^value)
end

dynamic/3

filter = %{
"$or" => [
%{"artist" => "ABBA", "year" => 1974},
%{"language" => "Swedish"}
]
}
condition1 = dynamic([s], s.artist == "ABBA" and s.year == 1974)
condition2 = dynamic([s], s.language == "Swedish")
or_condition = dynamic([s], ^condition1 or ^condition2)
from(s in Songs)
|> where([s], ^or_condition)
|> Repo.all()
filter = %{
"$or" => [
%{"artist" => "ABBA", "year" => 1974},
%{"language" => "Swedish"}
]
}
def filter(filter) do
from(s in Songs)
|> filter(filter)
|> Repo.all()
end
defp filter(query, filter) do
# The top level of the query is always an AND condition
conditions = build_and(filter)
from(q in query, where: ^conditions))
end
# Building a group of AND-connected conditions
defp build_and(filter) do
Enum.reduce(filter, nil, fn
{k, v}, nil ->
build_condition(k, v)
{k, v}, conditions ->
dynamic([c], ^build_condition(k, v) and ^conditions)
end)
end
# Building a group of OR-connected conditions
defp build_or(filter) do
Enum.reduce(filter, nil, fn
filter, nil ->
build_and(filter)
filter, conditions ->
dynamic([c], ^build_and(filter) or ^conditions)
end)
end
@allowed_fields ~w{artist year language}
defp build_condition(field_or_operator, filter)
defp build_condition("$or", filter),
do: build_or(filter)
defp build_condition(field, filter)
when field in @allowed_fields,
do: build_condition(String.to_existing_atom(field), filter)
defp build_condition(field, value)
when is_atom(value),
do: dynamic([c], field(c, ^field) == ^value)
defp build_condition(field, %{"$gt" => value}),
do: dynamic([c], field(c, ^field) > ^value)
defp build_condition(field, %{"$in" => value}) when is_list(value),
do: dynamic([c], field(c, ^field) in ^value)

I make software. Passionate about Elixir. User of C++, Ruby & JavaScript. Current project: https://www.dblsqd.com

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store