Build query where multiple fields can be nil

Questions / Help
1

Hi,

I’m facing some difficulties writing code that builds a query where multiple
fields (from a map) can be nil.

The code I came up with, so far, is:

deft get_account_by_id(account_id \\ "", opts = %{} \\ %{}) do nil_opts = opts |> Enum.filter(fn {k,v} -> is_nil(v) end) not_nil_opts = opts |> Enum.filter(fn {k,v} -> !is_nil(v) end) |> Map.new query = from Account, where: ^Map.to_list(Map.put(not_nil_opts, :id, account_id)) query = nil_opts |> Enum.reduce(query, fn ({k,_}, q) -> q |> where([a], fragment("? IS NULL", ^"#{k}")) end) ... end

Running that code like:

Core.Accounts.get_account_by_id(Ecto.UUID.generate(), %{organisation_id: nil})

Produces the following error in the console:

(Postgrex.Error) ERROR 42P18 (indeterminate_datatype): could not determine data type of parameter $2

Now I can understand why PG is complaining but, so far, I’ve not been able to come up
with a solution. I’ve tried macros too, but being an Elixir newbie all the attempts failed.

Thanks in advance for your help

2

That looks strange…

Maybe like this?

opts \\ %{}
3

Hi, my understanding is that the change you propose only deals with default values
while my code also impose that opts must be a map (sure, I could leverage guard clauses for that too).

Am I right?

1 Like
4

I made some progress that got rid of the “could not determine data type …” error.

First I enriched the nil_opts with:

nil_opts = opts |> Enum.filter_map(fn {k,v} -> is_nil(v) end, fn {k,v} -> {k, Account.__schema__(:types)[k]} end)

Then changed the Enum.reduce part to:

Enum.reduce(query, fn ({k,t}, q) -> q |> where([a], fragment("? IS NULL", type(^k, ^t))) end)

Now, the error I get in the console is:

(Ecto.Query.CastError) lib/core/accounts/accounts.ex:59: value `:organisation_id` in `where` cannot be cast to type :binary_id in query:

from a in Core.Accounts.Account,
where: a.id == ^"3d75d923-7c62-448c-8ad8-5f0bb7fbd44c",
where: fragment("? IS NULL", type(^:organisation_id, :binary_id)),
select: a
5

That does not do the same thing though. opts=%{} \\ %{} is a common pattern that I do as well, it means it defaults to a map via the \\ %{} and the =%{} means that if a user passes in something then it must be a map. By removing the =%{} you are letting the user pass in anything and potentially crash.

1 Like
6

Yes, You are right

Sorry for my post… It just looked strange to me :slight_smile:

7

… and the answer iiiisss … RTFM!

Ecto.Query.API has a function named “field” that allows to fetch fields dynamically.

Sorry for the background noise.

8

Here is how you can simplify the code above:

    Enum.reduce(opts, Account, fn
      {key, nil}, query ->
        where(query, [x], is_nil(field(x, ^key)))
      {key, value}, query ->
        where(query, [x], field(x, ^key) == ^value)
    end)

it assumes Ecto.Query has been imported.

2 Likes
9

Thanks for the advice @josevalim!