Phoenix/Ecto - query for single record with a nil value

Question

In my Phoenix app, I'd like to run a get_by() Ecto query for a single record - however, one of the fields that I want to search on should evaluate to nil, but Phoenix/Ecto forbids using nil as a comparison operator.

Here's my ideal (but failing) query:

target_record = Repo.get_by(Records, %{user_id: user_id, deleted_at: nil})

I have been able to query for nil fields using is_nil(field_name) in a query for many records, such as:

target_records = from(r in Records,
                        where: r.user_id == ^user_id,
                        where: is_nil(r.deleted_at))
                        |> Repo.all()

but the I am reluctant to use that for my current task because that will return a list... my Records table can have many entries with the same user_id but only one of those entries will ever have deleted_at: nil, so there's no need for me to get back a list of one item and then convert it into a map...

I mean, I can do that, but it doesn't seem very clean.

How should a get_by query be arranged so that a nil value can be included?

Answer 1

The Repo.get and Repo.get_by are great for very simple queries. However, when you need to go beyond what they can do, you'll need to drop into the Ecto.Query API. Queries build with this API can be used with Repo.one, Repo.one!, and Repo.all. Note that Repo.one will raise if it gets more than 1 record.

So, this should work:

target_records = 
  from(r in Records, where: r.user_id == ^user_id and is_nil(r.deleted_at))
  |> Repo.one()

and can be written like:

target_records = 
  Repo.one from r in Records, where: r.user_id == ^user_id and is_nil(r.deleted_at)