Use of binary_id in Ecto.Query

Phoenix Forum Questions / Help
, ,
1

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

2

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
  )
)
3 Likes
3

Hi @benwilson512

Thanks for your reply.
I’ve tried also.
It gives the following error:

Postgrex expected a binary of 16 bytes, got "6bf40047-117d-4056-b964-e54545f901ac". 
Please make sure the value you are passing matches the definition in your table
 or in your query or convert the value accordingly
4

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 UUID Primary Key in Elixir Phoenix with PostgreSQL and Ecto.

1 Like
5

Hi @codeanpeace

Thanks for your help.
I’ve tried also with Ecto cast and dump.
It leads to the same errors.

I’m using Uniq because it supports UUIDv6 and v7, which are more convenient than v4. I don’t think Ecto supports v6 and v7.

6

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

1 Like
7

Hi @joey_the_snake,

Thanks for the suggestion.
I’ve done it. And it works as mentionned by @benwilson512.
So I need to find what’s wrong in the other project.

8

My bad. The error was raised a few lines after the call to Repo.delete_all…
Thanks @benwilson512 @codeanpeace @joey_the_snake for your help.

9

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

  1. Your value is “dumped” into the type needed by the database driver. For Postgrex those dumped formats are listed here: Postgrex — Postgrex v0.16.5. 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.
  2. The query string along with the dumped values are passed to the database driver (Postgrex, for you) and the query is executed.
  3. The values produced by Postgrex in the query result are in the “dumped” format.
  4. Ecto will “load” the dumped into the schema format. Load is basically the opposite of dump.
1 Like