Can I pass Ecto query parameters to a function

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.

4 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())
2 Likes

I think that this will solve the current need that I have. Thank you for the response.