How to create parent record only if unique?

Questions / Help
1

I have a Post that belongs to a Venue. All Venues are unique, as determined by external_id. When I update a Post with a Venue, if the venue exists, point to it with the foreign key venue_id on Post. If the venue does not exist, create the Venue and associate it.

I’m able to create a unique index for Venue:

create index(:venues, [:external_id], unique: true)

And my schema and changeset for a Post update is:

schema "posts" do
  field :image, MyApp.ImageUploader.Type
  field :uuid, :string

  belongs_to :user, MyApp.User
  belongs_to :venue, MyApp.Venue

  timestamps()
end

  def changeset(struct, params \\ %{}) do
    struct
    |> cast(params, [:uuid, :venue_id])
    |> cast_attachments(params, [:image])
    |> cast_assoc(:venue)
    |> assoc_constraint(:user)
    |> validate_required([:image, :uuid])
  end

The schema and changeset for a venue is:

schema "venues" do
  field :name, :string
  field :external_id, :string
  field :phone, :string
  field :address, :string
  field :region, :string
  field :city, :string
  field :postal_code, :string
  field :country_code, :string
  field :lat, :decimal
  field :lng, :decimal

  has_many :captures, MyApp.Capture

  timestamps()
end

def changeset(struct, params \\ %{}) do
  struct
  |> cast(params, [:name, :external_id, :phone, :address, :city, :region, :postal_code, :country_code, :lat, :lng])
  |> validate_required([:name, :external_id, :region, :postal_code, :lat, :lng])
  |> unique_constraint(:external_id)
end

I’m able to create a venue when updating the post if the venue doesn’t already exist. But if the venue already exists, I get a uniqueness error when trying to update the post. What I want, is if the venue already exists, it’d associate with the existing venue record. How do I do that?

2

How would you do it in normal SQL is the way to ask that question. :slight_smile:

The two main styles:

  1. Grab the existing record or if it does not exist then make a new one, then insert the record pointing to that record.

  2. Create a complex insert instruction with embedded select’s and a UNION and a limit 1 on those to get the existing record or a default and insert multiple things at once, not all databases handle this well so style 1 is usually used.

Now, how would you do that in Ecto? :slight_smile:

3

I usually do #1. But since cast_assoc’s documentation (as shown below) seems to insert or associate depending on ID, I was wondering if there was a cleaner way to do things, since right now, I’m just doing it all in the controller and it looks a bit messy.

I would do that in the model as a stage in changeset, but model doesn’t allow use of Repo (though I know I can change it in web.ex), but I took it as a strong hint that I shouldn’t be querying the db in a model. Where would you put this code to do #1?

Once cast_assoc/3 is called, Ecto will compare those parameters with the addresses already associated with the user and act as follows:

  • If the parameter does not contain an ID, the parameter data will be passed to changeset/2 with a new struct and become an insert operation
  • If the parameter contains an ID and there is no associated child with such ID, the parameter data will be passed to changeset/2 with a new struct and become an insert operation
  • If the parameter contains an ID and there is an associated child with such ID, the parameter data will be passed to changeset/2 with the existing struct and become an update operation
  • If there is an associated child with an ID and its ID is not given as parameter, the :on_replace callback for that association will be invoked (see the “On replace” section on the module documentation)
4

I make modules that handle all access related to ‘some functionality’ (not for a schema, but for some functionality) and I put the code there.

5

Did you ever work out a viable solution to this? I have the exact same issue that I’ve been banging my head against for too long!

I’ve managed to get it to work using two separate functions acting as changesets in different scenarios.

The first - validate/2- uses cast_assoc to check for required fields of both the current schema, and nested schemas using a macro that I built; although this feels a bit hacky.

The second - changeset/2 uses put_assoc to retrieve or create the data. This is what actually does the work.

You can get by with just the second one using put_assoc but then you lose the error reporting bubbling back up to the forms from the changeset.

Like you, I’d love a solution that just uses cast_assoc (because that feels the most correct, as the data is external to the DB). Plus, it’s allowance for an ID seems to hint at a way of doing this without using put_assoc too.