Phoenix Framework: limit Insert into database to one per day

Question

I wish to do a "daily votation lunch" app for the people here at work, using the Phoenix Framework. The model I thought of making was Votation, with each Votation containing many embedded Restaurants schemas (Read here for info on embedded schemas). The model is looking like this:

defmodule WhereToLunch.Votation do
  use WhereToLunch.Web, :model

  schema "votations" do
    embeds_many :restaurants, Restaurant
    timestamps()
  end

  @doc """
  Builds a changeset based on the `struct` and `params`.
  """
  def changeset(struct, params \\ %{}) do
    struct
    |> cast(params, [])
    |> validate_required([])
    |> #TODO: Is it !was_votation_inserted_today() ??
  end

  @doc """
  Returns `true` if a `Votation` object was already inserted in the database
  on the same day the function is called. Returns false otherwise.
  """
  def was_votation_inserted_today() do
    #TODO: How to check if a object was already inserted in the database
    #      on the same day the function is called?
  end
end

defmodule WhereToLunch.Restaurant do
  use Ecto.Model

  embedded_schema do
    field :name, :string
    field :votes, :integer, default: 0
  end
end

What I want to do is to not allow more than one Insert in the table where_to_launch.votations per day. What is the best approach for doing that?

Answer 1

I'd add a unique index on the expression date_part('day', inserted_at) and let the database handle uniqueness.

To create the unique index, add the following to a new migration:

def change do
  create index(:posts, ["date_part('day', inserted_at)"], name: "post_inserted_at_as_date", unique: true)
end

and then add a unique_constraint to your model's changeset/2:

def changeset(...) do
  ...
  |> unique_constraint(:inserted_at, name: "post_inserted_at_as_date")
end

The database will now disallow creating 2 posts with the same day in inserted_at:

iex(1)> Repo.insert Post.changeset(%Post{}, %{title: ".", content: "."})
[debug] QUERY OK db=0.3ms
begin []
[debug] QUERY OK db=3.4ms
INSERT INTO "posts" ("content","title","inserted_at","updated_at") VALUES ($1,$2,$3,$4) RETURNING "id" [".", ".", {{2017, 2, 6}, {16, 58, 0, 512553}}, {{2017, 2, 6}, {16, 58, 0, 517019}}]
[debug] QUERY OK db=0.9ms
commit []
{:ok,
 %MyApp.Post{__meta__: #Ecto.Schema.Metadata<:loaded, "posts">,
  comments: #Ecto.Association.NotLoaded<association :comments is not loaded>,
  content: ".", id: 1, inserted_at: ~N[2017-02-06 16:58:00.512553], title: ".",
  updated_at: ~N[2017-02-06 16:58:00.517019],
  user: #Ecto.Association.NotLoaded<association :user is not loaded>,
  user_id: nil}}
iex(2)> Repo.insert Post.changeset(%Post{}, %{title: ".", content: "."})
[debug] QUERY OK db=0.4ms
begin []
[debug] QUERY ERROR db=6.6ms
INSERT INTO "posts" ("content","title","inserted_at","updated_at") VALUES ($1,$2,$3,$4) RETURNING "id" [".", ".", {{2017, 2, 6}, {16, 58, 1, 695128}}, {{2017, 2, 6}, {16, 58, 1, 695138}}]
[debug] QUERY OK db=0.2ms
rollback []
{:error,
 #Ecto.Changeset<action: :insert, changes: %{content: ".", title: "."},
  errors: [inserted_at: {"has already been taken", []}], data: #MyApp.Post<>,
  valid?: false>}