UUID and problem with loading related models

Questions / Help
1

I am not able to fetch products for a loaded publisher.

migrations:

defmodule Catalog.Repo.Migrations.CreatePublisher do
  use Ecto.Migration

  def change do
    create table(:publishers, primary_key: false) do
      add :id, :uuid, primary_key: true, default: fragment("uuid_generate_v4()")
      add :name, :string, null: false
    end
  end
end

defmodule Catalog.Repo.Migrations.CreateVolume do
  use Ecto.Migration

  def change do
    create table(:products, primary_key: false) do
      add :id, :uuid, primary_key: true, default: fragment("uuid_generate_v4()")
      add :publisher_id, references(:publishers, type: :uuid)
      add :name, :string, null: false
    end
  end
end

models:

defmodule Catalog.Publisher do
  use Catalog.Web, :model

  @primary_key {:id, :binary_id, autogenerate: false}
  schema "publishers" do
    field :name, :string
    has_many :products, Catalog.Product
  end
end

defmodule Catalog.Product do
  use Catalog.Web, :model

  @primary_key {:id, :binary_id, autogenerate: false}
  schema "products" do
    field :name, :string
    belongs_to :publisher, Catalog.Publisher
  end
end

And loading publisher with products:

query = from _ in Catalog.Publisher, limit: 1
publisher = query |> Catalog.Repo.one |> Catalog.Repo.preload(:products)

Unfortunately there is something wrong because I am receiving error:

** (Ecto.Query.CastError) deps/ecto/lib/ecto/association.ex:399: value `"de397199-e3dc-4974-9924-4ce1fa93b1d6"` in `where` cannot be cast to type :id in query:

from v in Catalog.Product,
  where: v.publisher_id == ^"de397199-e3dc-4974-9924-4ce1fa93b1d6",
  order_by: [asc: v.publisher_id],
  select: {v.publisher_id, v}
1 Like
2

Have you tried it without the ^?

3

yes, without ^ I am receiving

 value `"de397199-e3dc-4974-9924-4ce1fa93b1d6"` cannot be dumped to type :id. Or the value is incompatible or it must be interpolated (using ^) so it may be cast accordingly in query
4

You need to use the raw binary form of the UUID and not ist textual representation as far as I know.

So it is something along <<0xde, 0x39, 0x71, 0x99, ...>>.

At least, thats the representation on my little toy project when I take a look at the logged queries.

5

I have removed lines @primary_key {:id, :binary_id, autogenerate: false} from models. But I am still receiving error:

** (Ecto.Query.CastError) deps/ecto/lib/ecto/association.ex:399: value `<<222, 57, 113, 153, 227, 220, 73, 116, 153, 36, 76, 225, 250, 147, 177, 214>>` in `where` cannot be cast to type :id in query:

from p in Catalog.Product,
  where: p.publisher_id == ^<<222, 57, 113, 153, 227, 220, 73, 116, 153, 36, 76, 225, 250, 147, 177, 214>>,
  order_by: [asc: p.publisher_id],
  select: {p.publisher_id, v}
6

If you removed that line from your schema, then it is going to expect integers. You have three options:

  1. Use :integer keys and then pass integer values
  2. Use :binary_id and pass binary values
  3. Use Ecto.UUID and pass actual UUIDs

It seems you want 3.

2 Likes
7

when I replaced

@primary_key {:id, :binary_id, autogenerate: false}

with

@primary_key {:id, Ecto.UUID, autogenerate: false}

Code:

query = from _ in Catalog.Publisher, limit: 1
query |> Catalog.Repo.one |> Catalog.Repo.preload(:products)

return

** (Ecto.Query.CastError) deps/ecto/lib/ecto/association.ex:399: value `"de397199-e3dc-4974-9924-4ce1fa93b1d6"` in `where` cannot be cast to type :id in query:

from p in Catalog.Product,
  where: p.publisher_id == ^"de397199-e3dc-4974-9924-4ce1fa93b1d6",
  order_by: [asc: p.publisher_id],
  select: {p.publisher_id, v}
8

You have to set the type of your foreign key in your scheme:

:type - Sets the type of automatically defined :foreign_key. Defaults to: :id and can be set per schema via @foreign_key_type

1 Like
9

I set

@foreign_key_type Ecto.UUID

in each model under @primary_key and it works.

Thanks @josevalim, @NobbZ

1 Like
10

I’m facing a weird issue with a get user end point that uses a uuid as an id. I’m using Ecto 2.2.8. My end point url looks like: http://localhost:4000/v1/accounts/da192848-0735-4382-9407-2bc32cb466cf

  1. For a correct UUID(E.g., UUID: da192848-0735-4382-9407-2bc32cb466ce), I get a proper account info as part of the json.

  2. For an incorrect UUID(let’s say for the same uuid length but some character changed E.g., UUID: da192848-0735-4382-9407-2bc32cb466cf), I get an incorrect json and like a below json response:


{

"status": "FAILURE",

"errors": [

{

"message": "Requested resource not found",

"code": "NOT_FOUND"

}

]

}
  1. For an incorrect UUID(let’s say with lesser characters), I get the below response:

Ecto.Query.CastError at GET /v1/accounts/da192

deps/ecto/lib/ecto/repo/queryable.ex:348: value `"da192"` in `where` cannot be cast to type Ecto.UUID in query:

from a in MyApp.Users.Account,

where: a.id == ^"da192",

select: a

I tried making the changes recommended above. How can I get the response as with case 2 above for a request url like: v1/accounts/da192 which uses a da192 as it’s UUID.

My migration has the column name as binary_id like : add :id, :binary_id, primary_key: true. Should I be using add :id, :uuid, primary_key: true ? Or is there something else that I need to do in order to fix this ?

11

As you haven’t shown much code, I can only guess, but it seems as if you are not checking the given UUID for validity. So when you have invalid UUIDs you can replace them with a valid but known to be unused one. It seems as if you were using UUID 4, so just replace invalid UUIDs with a fixed UUID 1 and the database will never find it.

Doing it this way will ensure that invalid UUIDs are always treatened as if they were inexistant. I’d prefer to return a proper error, telling the user that he sent you garbage.

Also I think it is strange to have UUIDs as part of the URL, they are hard to type for humans…

12

Hi @NobbZ, Thank you for your prompt response. I was able to validate this against a regex that checks for UUID v4 and I raised an appropriate error for invalid id’s.

13

I am a little confused with this binary_id type.

So what you are saying is that I need to do type validation before I can use it with Repo.get or any other action from Ecto or my application will blow up? Is that correct?

14

I like to use Ecto.UUID.cast(uuid) (rather than using regexes or whatever) to test if it’s a valid UUID before handing it to Repo.get or the like. If it’s not valid then I return an {:error, :invalid_uuid} tuple from the context function then decide how to surface it to the user (or Rollbar or whatever) in the caller. This way the app doesn’t “blow up” but you can gracefully handle it.

1 Like
15

Yes and no… The result of the query depends on how your database handles invalid input. But I think most databases error out on invalid input. Passing an ill-formed UUID is like passing a string when an integer is expected.

3 Likes