How to compose query dynamically when param is nil?

i’m trying to build query by following this guide

below is my sample code:

defmodule MyModule do
    defp base_query do
        from i in Item
    end

    defp build_query(query, criteria) do
        Enum.reduce(criteria, query, &compose_query/2)
    end

    defp compose_query({"category_id", category_id}, query) do
        if is_nil(category_id) do
            # TODO: needs to fix
            where(query, [i], is_nil(^category_id))
        else
            where(query, [i], category_id: ^category_id)
        end
    end

    defp compose_query(_unsupported_param, query) do
        query
    end

    def all(criteria) do
        base_query()
        |> build_query(criteria)
        |> Repo.all()
    end
end


MyModule.all(%{"category_id" => 1})
MyModule.all(%{"category_id" => nil})

however when category_id is nil,

iex(89)> MyModule.all(%{"category_id" => nil})
[debug] QUERY ERROR source="items" db=0.0ms queue=2.5ms idle=1786.2ms
SELECT i0."id", i0."cover", i0."detail", i0."downloads", i0."draft", i0."featured", i0."likes", i0."title", i0."views", i0."zip", i0."category_id", i0."industry_id", i0."filetype", i0."inserted_at", i0."updated_at" FROM "items" AS i0 WHERE ($1 IS NULL) [nil]
** (Postgrex.Error) ERROR 42P18 (indeterminate_datatype) could not determine data type of parameter $1

    query: SELECT i0."id", i0."cover", i0."detail", i0."downloads", i0."draft", i0."featured", i0."likes", i0."title", i0."views", i0."zip", i0."category_id", i0."industry_id", i0."filetype", i0."inserted_at", i0."updated_at" FROM "items" AS i0 WHERE ($1 IS NULL)
    (ecto_sql 3.5.4) lib/ecto/adapters/sql.ex:751: Ecto.Adapters.SQL.raise_sql_call_error/1
    (ecto_sql 3.5.4) lib/ecto/adapters/sql.ex:684: Ecto.Adapters.SQL.execute/5
    (ecto 3.5.8) lib/ecto/repo/queryable.ex:229: Ecto.Repo.Queryable.execute/4
    (ecto 3.5.8) lib/ecto/repo/queryable.ex:17: Ecto.Repo.Queryable.all/3

if i change where(query, [i], is_nil(^category_id)) to where(query, [i], category_id: is_nil(^category_id)) , i got this error :

iex(93)> MyModule.all(%{"category_id" => nil})
** (Postgrex.Error) ERROR 42883 (undefined_function) operator does not exist: integer = boolean

    query: SELECT i0."id", i0."cover", i0."detail", i0."downloads", i0."draft", i0."featured", i0."likes", i0."title", i0."views", i0."zip", i0."category_id", i0."industry_id", i0."filetype", i0."inserted_at", i0."updated_at" FROM "items" AS i0 WHERE (i0."category_id" = ($1 IS NULL))

    hint: No operator matches the given name and argument types. You might need to add explicit type casts.
    (ecto_sql 3.5.4) lib/ecto/adapters/sql.ex:751: Ecto.Adapters.SQL.raise_sql_call_error/1
    (ecto_sql 3.5.4) lib/ecto/adapters/sql.ex:684: Ecto.Adapters.SQL.execute/5
    (ecto 3.5.8) lib/ecto/repo/queryable.ex:229: Ecto.Repo.Queryable.execute/4
    (ecto 3.5.8) lib/ecto/repo/queryable.ex:17: Ecto.Repo.Queryable.all/3

so what is the correct syntax for compose where criteria when category_id is nil ?

thanks !

have you tried where(query, [i], is_nil(i.category_id))

hi, @Ljzn, this works

import Ecto.{Query,Changeset}
alias Pragmatic.Items.Item
alias Pragmatic.Repo

defmodule MyModule do
    defp base_query do
        from i in Item
    end

    defp build_query(query, criteria) do
        Enum.reduce(criteria, query, &compose_query/2)
    end

    defp compose_query({"category_id", category_id}, query) do
        if is_nil(category_id) do
            where(query, [i], category_id: is_nil(i.category_id))
        else
            where(query, [i], category_id: ^category_id)
        end
    end

    defp compose_query(_unsupported_param, query) do
        query
    end

    def all(criteria) do
        base_query()
        |> build_query(criteria)
        |> Repo.all()
    end
end


MyModule.all(%{"category_id" => 1})
MyModule.all(%{"category_id": nil})

query like MyModule.all(%{"category_id": nil}) not MyModule.all(%{"category_id" => nil})

iex(10)> MyModule.all(%{"category_id" => nil})
[debug] QUERY ERROR source="items" db=0.0ms queue=2.2ms idle=1688.2ms
SELECT i0."id", i0."cover", i0."detail", i0."downloads", i0."draft", i0."featured", i0."likes", i0."title", i0."views", i0."zip", i0."category_id", i0."industry_id", i0."filetype", i0."inserted_at", i0."updated_at" FROM "items" AS i0 WHERE (i0."category_id" = (i0."category_id" IS NULL)) []
** (Postgrex.Error) ERROR 42883 (undefined_function) operator does not exist: integer = boolean

    query: SELECT i0."id", i0."cover", i0."detail", i0."downloads", i0."draft", i0."featured", i0."likes", i0."title", i0."views", i0."zip", i0."category_id", i0."industry_id", i0."filetype", i0."inserted_at", i0."updated_at" FROM "items" AS i0 WHERE (i0."category_id" = (i0."category_id" IS NULL))

    hint: No operator matches the given name and argument types. You might need to add explicit type casts.
    (ecto_sql 3.5.4) lib/ecto/adapters/sql.ex:751: Ecto.Adapters.SQL.raise_sql_call_error/1
    (ecto_sql 3.5.4) lib/ecto/adapters/sql.ex:684: Ecto.Adapters.SQL.execute/5
    (ecto 3.5.8) lib/ecto/repo/queryable.ex:229: Ecto.Repo.Queryable.execute/4
    (ecto 3.5.8) lib/ecto/repo/queryable.ex:17: Ecto.Repo.Queryable.all/3
iex(10)> MyModule.all(%{"category_id": nil})  
warning: found quoted keyword "category_id" but the quotes are not required. Note that keywords are always atoms, even when quoted. Similar to atoms, keywords made exclusively of ASCII letters, numbers, and underscore do not require quotes
  iex:10

[debug] QUERY OK source="items" db=2.1ms idle=1869.9ms
SELECT i0."id", i0."cover", i0."detail", i0."downloads", i0."draft", i0."featured", i0."likes", i0."title", i0."views", i0."zip", i0."category_id", i0."industry_id", i0."filetype", i0."inserted_at", i0."updated_at" FROM "items" AS i0 []
1 Like