Derive database type from the Ecto type in a predicate

[kipc...@gmail.com]

Proposal

Derive the database type and cast to it for a query parameter when the parameter is a custom type like a Ecto.ParameterizedType.

Background

When a query involves a predicate where the right hand side is a custom type, the query builder knows to call the `dump/3` function of the type (in this case a parameterised type). So it knows the database type involved, but it doesn't use the database type to cast the parameter.

Example:

Repo.all(
  from(o in Organization,
    where: o.value > ^Money.new(:USD, 100)
  )
)

% Fails because the parameter is not cast to it type:

SELECT o0."value" FROM "organizations" AS o0 WHERE (o0."value" > $1) [Money.new(:USD, "0")]. 

Where "value" is a `Money.Ecto.Composite.Type` type. The query builder correctly calls `dump/3` for the type. But it doesn't use the database type from `Money.Ecto.CompositeType.type/1` to cast the parameter. Therefore the error "cannot encode anonymous tuple {"USD", Decimal.new("0")}".  Note the underlying database type is a composite type, which in Postgrex is represented as a tuple.

This example:

Repo.all(
  from(o in Organization,
    where: o.value > type(^Money.zero(:USD), o.value)
  )
)

% Passes because the type is cast manually. Even though the query builder

% knows the type of the parameter.

SELECT o0."value" FROM "organizations" AS o0 WHERE (o0."value" > $1::money_with_currency) [Money.new(:USD, "0")]

works as expected, casting the parameter to the `money_with_currency` database type. But in this case, using the `type/2` macro seems unergonomic and unnecessary given that the query builder already knows the type of the parameter given that it called the `dump/3` function of the correct type.

[José Valim]

I believe to do this we would need to add a new callback to Ecto.Type that receives a field and return its type, if any (Ecto.Type because this can apply to any map). What do you think?

--
You received this message because you are subscribed to the Google Groups "elixir-ecto" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elixir-ecto...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/ab042265-6ade-404c-bb1d-af821b114e8an%40googlegroups.com.

[Kip Cole]

José, I think the case I’m describing is much more limited and (as best I can see) wouldn’t require any additional callback.

The case I’m describing is *only* when `EctoTypeModule.encode_params/3` is calling `CustomType.dump/3`. 

In this case, at least for comparison operators, Ecto is able to determine that a parameter on the right hand side should be the type of the left hand side.   Therefore it calls `dump/3` on the type module of the left hand side with the value for the right hand side.

If I’ve understand that correctly (and as you know I’ve never quite got the hang of Ecto internals), then Ecto already has all the information it needs to apply the typecast to the parameter (meaning “$1::type_name”).

I haven’t exhasstively checked all predicates, only comparisons. 

You received this message because you are subscribed to a topic in the Google Groups "elixir-ecto" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/elixir-ecto/oyXExxXaBYk/unsubscribe.
To unsubscribe from this group and all its topics, send an email to elixir-ecto...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/CAGnRm4%2BCqb1nMsOZBZGsQN-gg6MN31R66eTO5B7UuOM76%2Bd-wQ%40mail.gmail.com.

[José Valim]

Oh, I think I understand it now. Just to confirm: what is the type of "value" declared in your schema?

I am afraid this will be very tricky due to how Ecto/PG works. :(

To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/B65A5561-717D-4335-8FB5-3018B9FC0410%40gmail.com.

[Kip Cole]

In the following example:

    where: o.value > ^Money.new(:USD, 100)

o.value is defined by:

    field :value,           Money.Ecto.Composite.Type, default: Money.new(:USD, 0)

And the parameter value Money.new(:USD, 100) is a `Money.t/0` struct.

Since `Money.new(:USD, 100)` is a value (not a column), and since `Money.Ecto.Composite.Type.dump/3`(ie the Ecto type of `o.value)` is being called with parameter value `Money.new(:USD, 100)`, it would seem Ecto has the information it needs to derive the database type of the value.

> I am afraid this will be very tricky due to how Ecto/PG works. :(

I’m not surprised by that and I’ve tried to set the same expectation with `ex_money` users. But it seems soooooo close!

To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/CAGnRm4K3vDLe_ooHMNwv40nzXGkpjyXN%3DPB1a-h3RVzSHs2MKQ%40mail.gmail.com.

[José Valim]

Got it.

So the way this is supposed to work is that Postgres receives "o.value > $1", then it knows $1 must be of the same type as o.value. This information is sent to Postgrex which invokes the appropriate Postgrex type/extension. In other words, you can make this work by doing the proper casting on the Postgrex type/extension itself, instead of sending a separate value to the database and letting the database do the casting.

Ecto was not really designed to cast implicitly there, sorry. :(

To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/6D5385D2-71EE-4343-9C1E-0DBE3259C060%40gmail.com.

[Kip Cole]

Understood and not very surprised.

I have a working Postgrex extension. However that does not solve this issue since the underlying database type is a record (tuple, composite type) and the Elixir type is a Money.t.  Therefore when Postgrex is parsing a query, it expects the tuple form for a parameter unless the parameter is cast (in which case it uses the extension).  

Thats the reason I was looking to see if Ecto could auto-apply the cast. Because no matter what, a cast of the parameter is required when the db type is different to the Elixir type.

To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/CAGnRm4K6Ho%3DsrNpZqTn%2BE2%2Bm_0PJF2-v_OxLLLeOWwakecgCNw%40mail.gmail.com.