Ecto Fragments While Casting Types?

Questions / Help
1

I’m relying on the ip4r extension in Postgres to be able to query for IP addresses that match ranges stored in the table. Not to undersell the work that went into it but it’s essentially inet on steroids thanks to some index foo. An example of a query to find all rows that match a specific IP would be:

SELECT * FROM test WHERE subnet >>= '192.168.0.3';

Using the [EctoNetwork]https://github.com/adam12/ecto_network) package to help with casting to/from %Postgrex.INET{} I’m able to simple match queries and everything works as expected:

def query_addr(ip) do
  case EctoNetwork.INET.cast(ip) do
    {:ok, result} ->
      MyApp.Filter
      |> where(subnet: ^ip)
      |> Repo.all()
    {:ok, %Postgrex.INET{address: {:error, :einval}}} ->
      [{:error, "Error casting IP address to %Postgrex.INET{}: Invalid IP format"}]
    _ ->
      [{:error, "Unknown error validating IP address."}]
  end
end

iex(1)> MyApp.Filter.query_addr("192.168.0.0/24")

SELECT r0."id", r0."subnet", r0."inserted_at", r0."updated_at"
  FROM "public"."filters" AS r0
  WHERE (r0."subnet" = $1) [%Postgrex.INET{address: {192, 168, 0, 0}, netmask: 24}]

[
  %MyApp.Filter{
    __meta__: #Ecto.Schema.Metadata<:loaded, "public", "filters">,
    id: "5cc1c591d03a6765f150524b",
    subnet: %Postgrex.INET{address: {192, 168, 0, 0}, netmask: 24}
    inserted_at: ~N[2019-05-25 14:34:57],
    updated_at: ~N[2019-05-25 14:34:57],
  }
]

However when I attempt to use ip4r's search operators like in an Ecto fragment, casting on the value passed to %Postgrex.INET{} vanishes and it stays as a string. What is it I’m missing that’s preventing me from running this query?

def query_addr(ip) do
  case EctoNetwork.INET.cast(ip) do
    {:ok, result} ->
      MyApp.Filter
      |> where(fragment("subnet >>= ?", ^ip))
      |> Repo.all()
    {:ok, %Postgrex.INET{address: {:error, :einval}}} ->
      [{:error, "Error casting IP address to %Postgrex.INET{}: Invalid IP format"}]
    _ ->
      [{:error, "Unknown error validating IP address."}]
  end
end

iex(1)> MyApp.Filter.query_addr("192.168.0.3")

SELECT r0."id", r0."subnet", r0."inserted_at", r0."updated_at"
  FROM "public"."filters" AS r0
  WHERE (value >>= $1) ["192.168.0.0/24"]

** (DBConnection.EncodeError) Postgrex expected %Postgrex.INET{}, got "192.168.0.0/24". Please make sure the value you are passing matches the definition in your table or in your query or convert the value accordingly.
    (postgrex) lib/postgrex/type_module.ex:713: Postgrex.DefaultTypes."Elixir.Postgrex.Extensions.INET"/1
    (postgrex) lib/postgrex/type_module.ex:713: Postgrex.DefaultTypes.encode_params/3
    (postgrex) lib/postgrex/query.ex:62: DBConnection.Query.Postgrex.Query.encode/3
    (db_connection) lib/db_connection.ex:1148: DBConnection.encode/5
    (db_connection) lib/db_connection.ex:1246: DBConnection.run_prepare_execute/5
    (db_connection) lib/db_connection.ex:1342: DBConnection.run/6
    (db_connection) lib/db_connection.ex:540: DBConnection.parsed_prepare_execute/5
    (db_connection) lib/db_connection.ex:533: DBConnection.prepare_execute/4
    (ecto_sql) lib/ecto/adapters/sql.ex:546: Ecto.Adapters.SQL.execute!/4
    (ecto_sql) lib/ecto/adapters/sql.ex:538: Ecto.Adapters.SQL.execute/5
    (ecto) lib/ecto/repo/queryable.ex:147: Ecto.Repo.Queryable.execute/4
    (ecto) lib/ecto/repo/queryable.ex:18: Ecto.Repo.Queryable.all/3

Thanks for any help or guidance on this.

1 Like
2

You’re putting the string IP into the query, not the casted type.
where(fragment("subnet >>= ?", ^result))

2 Likes
3

That’s embarrassing :confounded:

Thanks Benjamin. I really appreciate your help.