I have a table for a collection of tasks. Only one of the fields :expiration_time and :expiration_all_day? may have a value (i.e. not be null/nil). Also, at least one should be provided.
Was making a start with implementing this, but can’t figure out how to use multiple fields in create constraint. I expected to be able to do this:
defmodule TodayTodo.Repo.Migrations.CreateTasks do
use Ecto.Migration
def change do
create table(:tasks, primary_key: false) do
add :id, :uuid, primary_key: true
add :description, :string
add :expiration_date, :date
add :expiration_time, :time
add :expiration_all_day?, :boolean # Prob throws
timestamps()
end
create(
constraint(
:tasks,
:validate_time_or_all_day,
check: "(expiration_time NOT NULL) OR (expiration_all_day? NOT NULL)"
)
)
end
end
defmodule TodayTodo.Tasks.Task do
use Ecto.Schema
import Ecto.Changeset
@primary_key {:id, :binary_id, autogenerate: true}
schema "tasks" do
field :description, :string
field :expiration_date, :date
field :expiration_time, :time
field :expiration_all_day?, :boolean
timestamps()
end
@doc false
def changeset(task, attrs) do
task
|> cast(attrs, [:id, :description, :expiration_date, :expiration_time, :expiration_all_day?])
|> validate_required([:description, :expiration_date])
|> check_constraint(:expiration_time, name: :validate_time_or_all_day)
end
end
But I get:
20:19:10.714 [info] create table tasks
20:19:10.718 [info] create check constraint validate_time_or_all_day on table tasks
** (Postgrex.Error) ERROR 42601 (syntax_error) syntax error at or near "NOT"
query: ALTER TABLE "tasks" ADD CONSTRAINT "validate_time_or_all_day" CHECK ((expiration_time IS NOT NULL) OR (expiration_all_day? IS NOT NULL))
(ecto_sql 3.8.3) lib/ecto/adapters/sql.ex:932: Ecto.Adapters.SQL.raise_sql_call_error/1
(elixir 1.13.3) lib/enum.ex:1593: Enum."-map/2-lists^map/1-0-"/2
(ecto_sql 3.8.3) lib/ecto/adapters/sql.ex:1024: Ecto.Adapters.SQL.execute_ddl/4
(ecto_sql 3.8.3) lib/ecto/migration/runner.ex:352: Ecto.Migration.Runner.log_and_execute_ddl/3
(ecto_sql 3.8.3) lib/ecto/migration/runner.ex:117: anonymous fn/6 in Ecto.Migration.Runner.flush/0
(elixir 1.13.3) lib/enum.ex:2396: Enum."-reduce/3-lists^foldl/2-0-"/3
(ecto_sql 3.8.3) lib/ecto/migration/runner.ex:116: Ecto.Migration.Runner.flush/0
(ecto_sql 3.8.3) lib/ecto/migration/runner.ex:289: Ecto.Migration.Runner.perform_operation/3
What am I missing?
EDIT: I changed the code blocks above, because I copied an old version of my code initially.
My bad, I copied an old version of my code into this post. I have fixed that now.
So it’s not about the name argument needing to be a string or atom, but rather: I can’t quite figure out why I get the error ** (Postgrex.Error) ERROR 42601 (syntax_error) syntax error at or near "NOT" and what field I am supposed to provide in |> check_constraint(:expiration_time, name: :validate_time_or_all_day). Can check_constraint only take one field?
You need to escape field names that have special characters like ? - Ecto normally takes care of this, but the SQL given to check is passed through verbatim.
To handle the error I got when migrating, I tried to escape the ? from the column expiration_all_day?. I tried so by doing check: "num_nonnulls(expiration_time, expiration_all_day\?) = 1". That didn’t work. Then I tried using single quotes and that did the trick.
def changeset(task, attrs) do
task
|> cast(attrs, [:id, :description, :expiration_date, :expiration_time, :expiration_all_day?])
|> validate_required([:description, :expiration_date])
|> check_constraint(:expiration_time, name: :validate_time_or_all_day, message: "select a time or 'all day', but not both")
end
So I only mention one variable in check_constraint/3, not both. It works but also seems a bit odd.
But most importantly, what I thought would work for the exclusive or does not work: check: "num_nonnulls('col1', col2') = 1". But that probably has to do with that one of the columns is boolean and by default false. So never equal to null.
As you can see I got rid of the question mark in my column name all together. Both cases didn’t work: NULLIF(all_day\?, false)) = 1 or NULLIF('all_day?', false)) = 1.
or taking it a step further… have expiration_datetime, and if it’s all day, set the time part to 23:59:59 or similar… Then there would be only one place to look to see whether the task has expired.
Depending on your time grain (in this case 1s), it would be the last moment of the day, so is a valid solution provided it isn’t important whether you knew a time was selected or not.
