I’m writing a phoenix application and using ecto for the database. I generated the models using “mix phx.gen.schema”. For an example lets say the table is “items” and has a field of “type”. There are two files. One is Item.ex and the other Items.ex. Item.ex has the schema and Items.ex is used to do the general queries. I made a function inside Items.ex of all_where/1. This function takes a keyword list and uses it for the “where” in the query. It works fine but I want to make it more extensible. What I am currently having an issue with is I want to be able to do something like this
defmodule MyApp.Items do
def all_where(kw_params) do
Repo.all(from(item in Item, where: ^params))
end
end
MyApp.Items.all_where(type: "A") # This works fine
MyApp.items.all_where(type in ["A", "B", "C"]) # How can I do this
Is it possible to do the second form of the query? If so how would I go about doing this? Would it be bad practice to do it this way?
The reason I want to do this is because another project I have worked on has a similar file but the original authors wrote a new function for each type of query they wanted to do (Example: Items.where_type_in_list(type_list)). Doing all those functions, among other reasons, caused the file to be thousands of lines long. I want to avoid having a massive file like that when, in my opinion, Items.all_where(type: “A”) is equally as readable as Items.list_all_with_type(type).
In advance, I apologize if this has been asked before but I did not know what to search for in order to find an answer to this question. Thanks for any help.
This should be working…
from(item in Item, where: item.type in ^["A", "B", "C"]) |> Repo.all()
Queries don’t hit DB before Repo is called. You can have a lot of functions taking a query, returning a query chained together.
My preferred way is to pass a keyword of filters…
Example.
filters = [type: ["A", "B", "C"], name: "whatever", another_criteria: "blah"]
MyApp.Items.all_where(filters)
def all_where(args) do
args
|> Enum.reduce(Item, fn
{:type, type}, query when is_list(type) -> from q in query, where: q.type in ^type
{:type, type}, query -> from q in query, where: q.type == ^type
...
end)
|> Repo.all()
end
This allow You to apply multiple filters, reducing the original Item, which is also a queryable, before calling Repo.
5 Likes
Or mixing keywords and expression notation:
# file: music_db/priv/repo/playground.exs
#
# http://www.pragmaticprogrammer.com/titles/wmecto
# https://pragprog.com/titles/wmecto/source_code
# http://media.pragprog.com/titles/wmecto/code/wmecto-code.zip
#
# pg_ctl -D /usr/local/var/postgres start
# mix format ./priv/repo/playground.exs
# mix run ./priv/repo/playground.exs
#
defmodule AppInfo do
def string() do
Application.loaded_applications()
|> Enum.map(&to_app_keyword/1)
|> Enum.sort_by(&map_app_name/1)
|> Enum.map_join(", ", &app_keyword_to_string/1)
end
defp to_app_keyword({app, _, vsn}),
do: {app, vsn}
defp app_keyword_to_string({app, vsn}),
do: Atom.to_string(app) <> ": " <> to_string(vsn)
defp map_app_name({app, _}),
do: app
end
defmodule Playground do
import Ecto.Query
alias MusicDB.Repo
alias MusicDB.{Album, Genre}
def query_ag_album_ids() do
from(g in Genre,
join: ag in "albums_genres",
on: ag.genre_id == g.id,
distinct: ag.album_id,
select: %{album_id: ag.album_id}
)
end
def query_ag_album_ids(genre) when is_binary(genre) do
query_ag_album_ids()
|> where([g, _], g.name == ^genre)
end
def query_ag_album_ids(genre) when is_list(genre) do
query_ag_album_ids()
|> where([g, _], g.name in ^genre)
end
def query(genre) do
from(ag in subquery(query_ag_album_ids(genre)),
join: a in Album,
on: ag.album_id == a.id,
select: a
)
end
def play do
IO.puts(AppInfo.string())
# Ecto.Adapters.SQL.to_sql(:all, Repo, query)
# query("live")
query(["live", "jazz"])
|> Repo.all()
end
end
IO.inspect(Playground.play())
3 Likes
I think that this will solve the current need that I have. Thank you for the response.