e94

Use of binary_id in Ecto.Query

Hi,

I’m new to Elixir. So sorry if I missed something.
I cannot make this query work:

alias Uniq.UUID

mandate_uuid = "6bf40047-117d-4056-b964-e54545f901ac"
mandate_uuid = UUID.string_to_binary!(mandate_uuid)

Repo.delete_all(
  from(bp in BalancePoint,
  where:
    bp.date >= ^start_time and bp.date <= ^end_time and
    bp.mandate_uuid == type(^mandate_uuid, :binary_id)
  )
)

I ve got the following error:

value `<<107, 244, 0, 71, 17, 125, 64, 86, 185, 100, 229, 69, 69, 249, 1, 172>>` 
cannot be dumped to type :binary_id in query

Here is my schema:

  @primary_key false
  @foreign_key_type :binary_id
  schema "balances_points" do
    field(:uuid, :binary_id, primary_key: true)
    field(:date, :utc_datetime)
    field(:asset, :string)
    field(:mandate_uuid, :binary_id)
    field(:total_quantity, :decimal)

    timestamps()
  end

The following code works with Postgrex :

alias Uniq.UUID

mandate_uuid = "6bf40047-117d-4056-b964-e54545f901ac"
mandate_uuid = UUID.string_to_binary!(mandate_uuid)

query = """
    DELETE FROM balances_points
      WHERE
        date >= $2::timestamp
        and date <= $3::timestamp
        and mandate_uuid = $1;
"""
Ecto.Adapters.SQL.query(Repo, query, [mandate_uuid, start_time, end_time])

I don’t understand what’s wrong with Repo.delete_all.
Thks

8 comments

/phoenix #ecto #ecto-query

0 4438 8

2023-02-22 23:28:21 UTC

Marked As Solved

benwilson512

Author of Craft GraphQL APIs in Elixir with Absinthe

Hi @e94 I don’t think you need to manually cast anything, you should be able to do:

mandate_uuid = "6bf40047-117d-4056-b964-e54545f901ac"

Repo.delete_all(
  from(bp in BalancePoint,
  where:
    bp.date >= ^start_time and bp.date <= ^end_time and
    bp.mandate_uuid == ^mandate_uuid
  )
)

Post #1

Also Liked

codeanpeace

I’d take a look at the Ecto.UUID module that comes with Ecto. In particular, it might be worth trying Ecto.UUID.cast!/1 and Ecto.UUID.dump!/1.

Off the top of my head, a possible reason the same approach is not working for the first query, but is fine for the second query is because the second query uses string interpolation.

Also, is there a particular reason why you’re using Uniq.UUID? I’d suggest seeing if Ecto.UUID can replace Uniq.UUID for your use case, especially since you’re already using Ecto to interface with Postgres. For reference, check out this article on https://pawelurbanek.com/elixir-phoenix-uuid.

Post #3

joey_the_snake

Would you be able to post a small reproducible repo? What Ben said should work.

Post #5

joey_the_snake

If you’d like some more info about the process Ecto uses:

Your value is “dumped” into the type needed by the database driver. For Postgrex those dumped formats are listed here: Postgrex — Postgrex v0.22.2. That’s why you had a problem when you tried to convert your string before inserting it into your query for Ecto but why it worked when you did it in Postgrex directly.
The query string along with the dumped values are passed to the database driver (Postgrex, for you) and the query is executed.
The values produced by Postgrex in the query result are in the “dumped” format.
Ecto will “load” the dumped into the schema format. Load is basically the opposite of dump.

Post #8