How to perform bulk associations with ecto?

Questions / Help
, ,
1

I’m working on a game app that has an answers table and a categories table. This relationship is many to many.

The admins for the app can add answers and categories individually using usual controller format. However, I want to create a way to bulk associate answers to categories. On a category page there is a textarea to receive a comma separated list of strings. Upon hitting submit, I want to achieve the following steps,

  • assess if any of the strings already exist as an answer
  • for any that do not exist, create an record in the answers table
  • for all the answers that were given, create the association in the join table

Here is my attempt.

  def put_answers_to_category(category, answers_string) do
    answers =
      answers_string
      |> parse_answers_string()
      |> find_existing_and_create_new()

    old_category =
      category
      |> Repo.preload(:answers)
      |> IO.inspect()

    category
    |> Ecto.Changeset.change()
    |> Ecto.Changeset.put_assoc(:answers, Enum.uniq(answers ++ old_category.answers))
    |> IO.inspect()
    |> Repo.update()
  end

  def parse_answers_string(answers_string) do
    answers_string
    |> String.split(",", trim: true)
    |> Enum.map(&String.trim/1)
    |> Enum.reject(&(&1 == ""))
  end

  def find_existing_and_create_new(answers_list) do
    existing_answers =
      Answer
      |> where([a], a.raw in ^answers_list)
      |> Repo.all()

    existing_answers_raw = Enum.map(existing_answers, & &1.raw)

    new_answers =
      answers_list
      |> Enum.filter(fn ans -> !Enum.member?(existing_answers_raw, ans) end)
      |> Enum.map(fn ans -> %{raw: ans} end)
      |> Enum.map(&create_answer/1)
      |> Enum.map(fn {:ok, answer} -> answer end)

    existing_answers ++ new_answers
  end

This seems to be working but I think it may be verbose/inefficient. I’ve read the docs around cast_assoc and put_assoc but I couldn’t quite find a better way. Is there a better/more efficient way to do this?

2

As far as Ecto goes, I think that your implementation is the proper way to approach updating a many_to_many. But that really just means using put_assoc with an array of the set of answer records you want the category changeset to have.

The rest of the complexity here comes from the bulk upsert on the answer text param, but changing that would require a different API design. For example, a more conventional many_to_many update would take the answer ids directly, requiring them to all already exist. For example, the FE could be upserting them individually as they are entered via another endpoint.

Don’t know enough about your requirements to comment on whether that would be better overall, but I would say that you lose a significant advantage of your current approach by creating the new answers in a separate DB transaction (AFAICT), which means if the category update never goes through for whatever reason (validation error) the new answers will still be around in your DB, which may be unexpected.

3

Thank you, this was helpful.