Get all route with multiple query params

I’m implementing a get all route and adding some query params to filter result.

I don’t want to get the whole list and then filter it with Enum.filter etc… Instead I want to get the list pre-filtered from the database using Ecto.

At the moment the controller and function are :

  def index(conn, _params) do
    params = fetch_query_params(conn).params
    books = BooksHandler.list_books(params)
    render(conn, "index.json", books: books)
  end
  def list_books(params \\ []) do
    Repo.all(Books)
  end

at “list_books” now I have the params as the following map format : %{"date" => "value1", "title" => "value"}

I need to get all books with title = value and date = value1

Thanks

Hey @xgeek116 , quick note that you don’t need to fetch_query_params(conn).params in your controller action. Your query params will be in the params argument that you are ignoring in your function signature.

def index(conn, params) do
  books = BooksHandler.list_books(params)
  render(conn, "index.json", books: books)
end

In your list_books/1 function you can convert a map to a keyword list and pass it directly into the where query option:

def list_books(filters) when is_list(filters) do
  Repo.all(from Book, where: ^filters)
end

def list_books(%{} = filters) do
  keyword_filters =
    Enum.map(filters, fn
      {k, v} when is_binary(k) -> {String.to_existing_atom(k), v}
      pair -> pair
    end)

  list_books(keyword_filters)
end

Ecto.Query#where/3

Thanks @msimonborg , I almost got there but I am now confused how can I add date verification :

I need to send in the query /?created_before=date , or /?created_after=date

but now as we send a keyword list (which is key equals value) I can’t see how we are going to do the > and < comparaison:

filters = [{:creation_date, **HERE_WHAT_TO_PUT**}]

You need to parse the query params into a date struct to compare them and when you put the date in the query params, you need to do that in a way that is going to play nicely with query params and parsing. There is the Date.to/from_iso8601 functions available which might suit.

@cmo this code :

Enum.map(params, fn
          {k, v} when is_binary(k) -> {String.to_existing_atom(k), v}
        end)

Will create a keyword list with all query params and values and then the ecto query will use as it is, so if I pass in the query params, the param “created_before” for example with other params I will have something like :

[{:title, "test_title"}, {:created_before, 22-06-201}]

But in the schema I don’t have “created_before” attribute so it will raise an error. I need a way to separte the date query params (“created_before” and “created_after”) from the other params and how to inject them in my Ecto query (add other where statements dynamically ?)

How about putting the comparator into the param key and using synamic to build the where?
e.g. “inserted_at:lt” ?

def filter_and(query, params) do

    # :eq :gt :lt :gte :lte
    where = Enum.reduce(
      params,
      true,
      fn {key, value}, acc ->

        [key, comparator] = case String.split(key, ":") do
          [k, comp] -> [k, String.to_atom(comp)]
          [k] -> [k, :eq]
        end

        atom_key = String.to_existing_atom(key)

        case comparator do
          :gt -> dynamic([q], field(q, ^atom_key) > ^value and ^acc)
          :lt -> dynamic([q], field(q, ^atom_key) < ^value and ^acc)
          :gte -> dynamic([q], field(q, ^atom_key) >= ^value and ^acc)
          :lte -> dynamic([q], field(q, ^atom_key) <= ^value and ^acc)
          :in -> dynamic([q], field(q, ^atom_key) in ^String.split(value, ",") and ^acc)
          :ne -> dynamic([q], field(q, ^atom_key) != ^value and ^acc)
          _ -> dynamic([q], field(q, ^atom_key) == ^value and ^acc)
        end
      end

    )

    query
    |> where(^where)
  end
1 Like

@mize85 I have this error :

    ** (Protocol.UndefinedError) protocol Enumerable not implemented for #Ecto.Query<from p0 in Book, where: p0.X == ^"Y" and
  (p0.Z == ^"W" and
     )> of type Ecto.Query (a struct)

I have only updated your return :


  def filter_and(params) do
    where = Enum.reduce(
      params,
      true,
      fn {key, value}, acc ->

        [key, comparator] = case String.split(key, ":") do
          [k, comp] -> [k, String.to_atom(comp)]
          [k] -> [k, :eq]
        end

        atom_key = String.to_existing_atom(key)

        case comparator do
          :gt -> dynamic([q], field(q, ^atom_key) > ^value and ^acc)
          :lt -> dynamic([q], field(q, ^atom_key) < ^value and ^acc)
          :gte -> dynamic([q], field(q, ^atom_key) >= ^value and ^acc)
          :lte -> dynamic([q], field(q, ^atom_key) <= ^value and ^acc)
          :in -> dynamic([q], field(q, ^atom_key) in ^String.split(value, ",") and ^acc)
          :ne -> dynamic([q], field(q, ^atom_key) != ^value and ^acc)
          _ -> dynamic([q], field(q, ^atom_key) == ^value and ^acc)
        end
      end

    )
    where
  end

And I call it :

  def fetch_books(filters) do
    try do
      Book
      |> where(^filters)
    rescue
      _ -> raise NotFound, message: "Books not found"
    end
  end

** (Protocol.UndefinedError) protocol Enumerable not implemented for #Ecto.Query<from p0

You reduce over the query and not the params here i guess? (which does not work)

And I call it

you did not call your “filter_and” function there :confused:

if you take the filter_and(query, params) from above you should be able to call it like

Book
|> filter_and(params)