CODEWITHSUNDEEP
elixirecto
Joe Eifert
Joe Eifert

Reputation: 1387

Ecto Query composition with nil values

I have a query with a where condition. Some of the values in the where condition can be nil and should therefore not be queried. I came up with pattern matching and custom functions like this:

defp add_country(query, nil), do: query
defp add_country(query, country_id), do: from a in query, where: a.country_id == ^country_id
defp add_port(query, nil), do: query
defp add_port(query, port_id), do: from a in query, where: a.port_id == ^port_id

Is there an easier / cleaner solution? Can I do it in the query itself? Maybe with an option?

FYI: The query function:

def list_ads(%{"country_id" => country_id, "port_id" => port_id, "category_id" => category_id}) do
today = Date.utc_today()
query = (from a in Ad,
              where: a.category_id == ^category_id,
              join: c in Contract,
              where: a.contract_id == c.id and
                     c.payment_status == "PAID" and
                     c.expiration >= ^today,
              distinct: [a.user_id, a.plan],
              order_by: a.plan)
        |> add_country(country_id)
        |> add_port(port_id)
Repo.all(query)
end

Please don't be discouraged by the word "Ad" in there. These are purely informational, displayed only on the platform and people come looking for them.

Upvotes: 1

Views: 2015

Answers (1)

bla
bla

Reputation: 1870

I had a fairly similar problem. I needed to add filters to my query if the variable holding it's value was not nil. I was initially doing a bunch of if's. Something like:

query = if value do
  from(q in query, where: q.value == ^value)
else
  query
end

This was repeating many times. I then discovered the field/2 function. With it I was able to dynamically specify the field I wanted to compare against. So I built a function which let me reuse this logic:

defp put_filter(query, _key, nil) do
  query
end

defp put_filter(query, key, value) do
  from(q in query, where: field(q, ^key) == ^value)
end:

Usage looks like this:

query =
  base_query
  |> put_filter(:status, status)
  |> put_filter(:reason, reason)

Upvotes: 2

Related Questions