CODEWITHSUNDEEP
elixirphoenix-frameworkecto
HDanh
HDanh

Reputation: 225

Ecto dynamic query params in phoenixframework

I'm new to Elixir and Phoenix framework. How can I dynamically add parameter to ecto query ? For example:

def index(conn, _params) do
  departments = Repo.all(Department)
  render(conn, "index.json", data: departments)
end

Is it possible to use the _params in Ecto query ? Something like: 

Repo.get_by(Department, _params)

Upvotes: 1

Views: 1392

Answers (3)

Gabriel Giordano Medina
Gabriel Giordano Medina

Reputation: 428

I know this thread is old, but one can also make use of Ecto.Changeset.cast, and also benefit of its validation. For example, to select all:

with %Ecto.Changeset{valid?: true} = changeset <- Ecto.Changeset.cast(%Department{}, params, [:department_name]) do
  clauses = Enum.to_list(changeset.changes)

  query =
    from e in Department,
      where: ^clauses,
      order_by: [desc: e.inserted_at]

  {:ok, Repo.all(query)}
else
  changeset -> {:error, changeset}
end

Upvotes: 0

coderVishal
coderVishal

Reputation: 9079

you can get stuff dynamically ofcourse, Repo.get_by in the second argument expects Keyword.t | Map.t, either a keyword or a map. You can set it directly from params into a Map or keyword, and pass that into the query. Example

def index(conn, _params) do
  query_conditions = [id: params["id"], name: params["department_name"] ]
  departments = Repo.get_by(Department, query_conditions)
  render(conn, "index.json", data: departments)
end

As @Dogbert stated in the comments that get_by will get you 0 or 1 result, more results will raise an error. I only used it because you used get_by in the question.

Upvotes: 1

Dogbert
Dogbert

Reputation: 222040

You can use "bindingless queries" for this, but there's a problem. Ecto expects the column names to be atoms and the argument to be a keyword list. The latter is easy, but converting arbitrary user input into atoms may result in the Erlang VM crashing. Here's a safe way to do this using a whitelist of column names:

# user input
params = %{"first_name" => "John", "last_name" => "Smith", "age" => 28}
filtered_params =
  params
  |> Map.take(~w(first_name last_name age))
  |> Enum.map(fn {k, v} -> {String.to_atom(k), v} end)

from(MyApp.Person, where: ^filtered_params)
|> MyApp.Repo.all
|> IO.inspect

Output:

[debug] SELECT p0."id", p0."first_name", p0."last_name", p0."age", p0."inserted_at", p0."updated_at" FROM "people" AS p0 WHERE (((p0."age" = ?) AND (p0."first_name" = ?)) AND (p0."last_name" = ?)) [28, "John", "Smith"] OK query=6.5ms queue=14.8ms
[%MyApp.Person{__meta__: #Ecto.Schema.Metadata<:loaded>, age: 28,
  first_name: "John", id: 1, inserted_at: #Ecto.DateTime<2016-06-11T16:01:49Z>,
  last_name: "Smith", updated_at: #Ecto.DateTime<2016-06-11T16:01:49Z>}]

Upvotes: 4

Related Questions