Virtual Enum fields in Ecto

The Ecto.Enum type added in Ecto 3.5 is a very useful tool. Ecto.Enums can be persisted to the database as a string, integer, an ENUM type in supported databases, or a composite array.

# Example stored as strings
field :status, Ecto.Enum, values: [:active, :disabled, :parked, :deleted, :pending, :expired]

Ecto also provides the ability to use virtual fields. This can be useful to return computed values from database queries transparently alongside persisted data in your schemas.

For persisted Enum types, Ecto will handle the casting to and from atoms when interacting with a database. However, if your Enum field is a virtual field, the result will not be cast and your struct field will be the type that is returned from your query. This can be resolved by explicitly casting to a custom enum type.

Consider the following virtual field. We want to use dates and the states of other persisted Enum fields to create an overall status.

field :status, Ecto.Enum,
  virtual: true,
  values: [:active, :disabled, :parked, :deleted, :pending, :expired]

This can be done easily with an Ecto fragment that can be composed into your larger query:

defp set_status_virtual_field(query) do
  from link in query,
    select: %{
      link
      | status:
          fragment(
            """
              CASE
                WHEN ? IS NOT NULL THEN 'deleted'
                WHEN ? < NOW() THEN 'expired'
                WHEN ? > NOW() THEN 'pending'
                WHEN ? = 1 THEN 'disabled'
                WHEN ? = 2 THEN 'parked'
                WHEN ? = 0 THEN 'active'
              END
            """,
            link.deleted_at,
            link.valid_until,
            link.valid_from,
            link.state,
            link.state,
            link.state
          )
    }
end

# Example usage
def get_link!(id) do
  Link
  |> set_status_virtual_field()
  |> Repo.get!(id)
end

However, if you look at the value of the :status key in your struct, you will see that it is a string not an atom as you would expect from your field definition.

This can be resolved by casting with Ecto.Query.API.type/2 with a custom parameterized type. (Note the addition of the type/2 call after the fragment)

@status_enum Ecto.ParameterizedType.init(Ecto.Enum,
                 values: [:active, :disabled, :parked, :deleted, :pending, :expired]
               )

...
  
defp set_status_virtual_field(query) do
  from link in query,
    select: %{
      link
      | status:
          fragment(
            """
              CASE
                WHEN ? IS NOT NULL THEN 'deleted'
                WHEN ? < NOW() THEN 'expired'
                WHEN ? > NOW() THEN 'pending'
                WHEN ? = 1 THEN 'disabled'
                WHEN ? = 2 THEN 'parked'
                WHEN ? = 0 THEN 'active'
              END
            """,
            link.deleted_at,
            link.valid_until,
            link.valid_from,
            link.state,
            link.state,
            link.state
          )
          |> type(^@status_enum)
    }
end

This is not as seamless as one would hope, but it does correctly handle casting for computed virtual Enums.

One thing to consider is whether it makes sense to go this route or to use a helper function to compute an enum from a given record. For example, the following function accomplishes the same and does not require ensuring that the virtual field query fragment is run in all queries where the status will be needed.

def get_link_status(%Link{} = link) do
  current_time = DateTime.utc_now()

  cond do
    link.deleted_at -> :deleted
    DateTime.compare(current_time, link.valid_from) == :lt -> :pending
    DateTime.compare(current_time, link.valid_until) == :gt -> :expired
    true -> link.state
  end
end

As with all things there are tradeoffs, but if you need to correctly cast a virtual Enum, at least you now know how.