Exclusive OR in Ecto: validating that only one of two fields is given

coen.bakker

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.

14 comments

/phoenix #ecto #validation

0 2731 14

2022-08-10 01:03:29 UTC

Showing Posts 1 to 10

Show Best Posts
Show All (oldest first)
Show All (newest first)

al2o3cr

The second argument to constraint should be a string or atom for the constraint name, but you’re passing a list of atoms.

Post #1

coen.bakker

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?

Post #2

coen.bakker

I tried other checks also but get similar errors. For example:

check: "num_nonnulls(expiration_time, expiration_all_day?) = 1"

Post #3

al2o3cr

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.

Post #4

dimitarvp

Did you fix this? There’s probably at least two ways of doing it IIRC.

Post #5

coen.bakker

Finally got time to look at it again.

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.

    create(
      constraint(
        :tasks,
        :validate_time_or_all_day,
        check: "num_nonnulls('expiration_time', 'expiration_all_day?') = 1"
      )
    )

In my changeset I have:

  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.

Post #6

coen.bakker

To follow up.

It indeed had to do with overlooking the default false of my boolean column. So it was an easy fix to make num_nonnulls/num_nulls work:

    create(
      constraint(
        :tasks,
        :validate_time_or_all_day,
        check: "num_nulls(time, NULLIF(all_day, false)) = 1"
      )
    )

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.

Post #7