Querying a table with UUID using Ecto.Query gives Ecto.Query.CastError

Question

So I've got the following migration:

create table(:things, primary_key: false) do
  add :id, :uuid, primary_key: true
  add :state, :string

  timestamps()
end

Which has the following schema:

@primary_key {:id, Ecto.UUID, autogenerate: true}
@derive {Phoenix.Param, key: :id}
schema "things" do
  field :state, :string

  timestamps()
end

And upon trying the following query in the REPL, I get an Ecto.Query.CastError:

iex(8)> q = from s in Thing, where: s.id == "ba34d9a0-889f-4999-ac23-f04c7183f2ba", select: s
#Ecto.Query<from o in App.Thing,
 where: o.id == "ba34d9a0-889f-4999-ac23-f04c7183f2ba", select: o>
iex(9)> Repo.get!(Thing, q)
** (Ecto.Query.CastError) /project/deps/ecto/lib/ecto/repo/queryable.ex:341: value `#Ecto.Query<from o in App.Thing, where: o.id == "ba34d9a0-889f-4999-ac23-f04c7183f2ba", select: o>` in `where` cannot be cast to type Ecto.UUID in query:

from o in App.Thing,
  where: o.id == ^#Ecto.Query<from o in App.Thing, where: o.id == "ba34d9a0-889f-4999-ac23-f04c7183f2ba", select: o>,
  select: o

    (elixir) lib/enum.ex:1811: Enum."-reduce/3-lists^foldl/2-0-"/3
    (elixir) lib/enum.ex:1357: Enum."-map_reduce/3-lists^mapfoldl/2-0-"/3
    (elixir) lib/enum.ex:1811: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto) lib/ecto/repo/queryable.ex:124: Ecto.Repo.Queryable.execute/5
    (ecto) lib/ecto/repo/queryable.ex:37: Ecto.Repo.Queryable.all/4
    (ecto) lib/ecto/repo/queryable.ex:78: Ecto.Repo.Queryable.one!/4
    (stdlib) erl_eval.erl:670: :erl_eval.do_apply/6
    (iex) lib/iex/evaluator.ex:219: IEx.Evaluator.handle_eval/5
    (iex) lib/iex/evaluator.ex:200: IEx.Evaluator.do_eval/3
    (iex) lib/iex/evaluator.ex:178: IEx.Evaluator.eval/3

I'm not sure this is the proper way of using UUID's with Ecto, I've looked around but there are several people doing it differently. I'm using Ecto 2.1 and Postgres 10.0 with postgrex.

Any tips on how to get Ecto querying correctly and casting the UUID?

EDIT: The ID I'm giving in the query is an actual existing ID I copied from the database.

Paweł Dawczak Paweł Dawczak · Accepted Answer · 2017-10-17T21:08:17

I find the problem you're experiencing is a bit confusing at first, but I think it works quite correctly - the problem is in how you're querying the database.

If you have your query ready, you need to use Repo.all(query), so your code should be:

q = from s in Thing, where: s.id == "ba34d9a0-889f-4999-ac23-f04c7183f2ba", select: s
Repo.all(q)

You can query the database to look for specific record by primary key, and this is when you can use Repo.get!/3.

Try this:

Repo.get!(Thing, "ba34d9a0-889f-4999-ac23-f04c7183f2ba")

When you pass your q as a second argument to get!, it will try to cast it to UUID, but in fact, query is not cast-able, which is explained in the exception:

** (Ecto.Query.CastError) /project/deps/ecto/lib/ecto/repo/queryable.ex:341: value `#Ecto.Query<from o in App.Thing, where: o.id == "ba34d9a0-889f-4999-ac23-f04c7183f2ba", select: o>` in `where` cannot be cast to type Ecto.UUID in query:

Can you see the whole #Ecto.Query<> is enclosed in ``?

You can find more in the documentation:

Hope that helps!

Querying a table with UUID using Ecto.Query gives Ecto.Query.CastError

1 Answers