How to handle a nil parameter inside an Ecto database query

Questions / Help
, , ,
#1

I have created some basic query to load data from the database:

page =
Policy
|> join(:left, [p], c in Client, p.client_id == c.id)
|> where([p, c], c.user_id == ^conn.assigns.current_user.id)
|> where([p, c], p.client_id in ^params["policy"]["client_id"])
|> preload([p, c], [client: c])
|> Repo.paginate(params)

The nil problem arises when the GET parameter ["policy"]["client_id"] is missing (not set in the request). My question, how to handle that? How to prevent this error? Basically I want that particular where sentence to be ignored if ["policy"]["client_id"] is empty.

#2

I would encapsulate this query in a separate function and pattern match on the parameter

def foo(params["policy"]["client_id"] = client_id) when is_binary(client_id) do
  # query the db
end

def foo(_) do
  # return some thing useful
end
1 Like
#3

@schaary

Thank you. I understand that the use of multiple function definitions with various guards can cover all parameters’ possibilities. However, if I will be filtering by say 5 parameters then the list of possible combinations of those parameters will be big and that means I will have to write multiple function/guard definitions to catch all possibilities. I hope there is a simpler approach to run that Ecto query.

#4

You could also use query composition, pushing out the client_id filtering to a separate function… should look something like this:

def filter_policy_client_id(query, nil), do: query
def filter_policy_client_id(query, client_id) do
  query |> where([p], p.client_id in ^client_id)
end

# ...

page =
Policy
|> join(:left, [p], c in Client, p.client_id == c.id)
|> where([p, c], c.user_id == ^conn.assigns.current_user.id)
|> filter_policy_client_id(params["policy"]["client_id"])
|> preload([p, c], [client: c])
|> Repo.paginate(query, params)

Is client_id supposed to be a list? If it’s not, I suspect you should use equality comparison instead of in.

1 Like
#5

@schaary, @jwarlander
Thank you… I have learned new things in Elixir today inspecting the answers you have provided…