Persist many to many association with additional columns

I’ve chosen Elixir/Phoenix to quickly prototype an idea. This is my first project with Phoenix and I’m struggling a little with things that should be obvious.
I haven’t accomplished anything yet but I’ve now consumed twice the time it would have taken me to complete the entire project in another language. Phoenix itself is pretty straightforward, but Ecto keeps standing on my way. I need some help before I give up and start over the project in another technology…

What I’m trying to achieve is pretty simple. I just want to attach categories to entities.
So for instance in a Requisition form there are several multi-select fields with different categories. There is one unique categories table with a type field.

The requisitions_category join table have an additional “context” column. Because the same category can be linked to the same req in different contexts (in that case that would be 2 different form fields) The forms are customisable in this app, that’s why I need something generic.

Because of this extra “context” field my understanding is that I can’t declare a many_to_many assoc.

Here are the (simplified) corresponding entities:

schema "requisitions_categories" do
    field :context, :string
    belongs_to :requisition, Requisition
    belongs_to :category, Category
schema "requisitions" do
    field :title, :string
    has_many :contract_type, RequisitionCategory, where: [context: "contract_type"]
schema "categories" do
    field :title, :string
    field :type, :integer

Now I’m trying to persist a new requisition. So basically these steps:

  • Insert the requisition
  • Get the inserted ID
  • create the corresponding requisitions_categories from the inserted requisition ID, the hardcoded (for now) context and the category ID.

The problem is that whatever I try, either the categories are ignored, or I get cryptic error messages.

First thing I don’t manage to understand is if Ecto is supposed to handle this case of if I have to do everything manually. I assume it does (otherwise that’s the point or using an ORM?) and my understanding is that put_assoc should handle it, but it doesn’t work.

Or am I supposed to use Ecto.Multi and proceed step by step?

I haven’t found this case in any documentation, and googling it did not help. So any good article / doc would help.

Also, I’m generally speaking not a big fan of ORMs (feel free to ignore the “O”) so my last option would be to skip Ecto entirely and write good old SQL queries and keep full control other how I access my data. What library should I use for that? Is it even an option with Phoenix?

Can you show some code and/or the errors you get. Even with your quite elaborate description I’m not really sure I understand the problem enough to be able to suggest something.

You’ll get more useful replies here if you share those messages.

FWIW, what you’ve described sounds like it should work fine with cast_assoc:

attrs = %{
  "title" => "Some Requisition"
  "requisitions_categories => [
    %{"context" => "Mumble", "category_id" => 1234}

|> cast(attrs, [:title])
|> cast_assoc(:requisitions_categories)

Thanks for your answers!

I finally managed to make it work using your suggestion @al2o3cr

Here’s my create_requisition function:

def create_requisition(attrs \\ %{}) do

    attrs = Map.put(attrs, "contract_type",["contract_type_id"], fn id ->
      %{"context" => "contract_type", "category_id" => id}

      |> Requisition.changeset(attrs)
      |> Ecto.Changeset.cast_assoc(:contract_type)
      |> Repo.insert()

It took me some time to accept that I could pass a requisitions_categories without a requisition_id (which violates an invariant and would be impossible on any typed language) and that Ecto will set it for me. For this reason the RequisitionCategory.changeset function was requiring requisition_id, and this is why it did not work. I updated RequisitionCategory.changeset to accept empty requisition_id and now it works.

I’m starting to wrap my head around it.

Now I have another question related to preloading. On the show page I want to display the contract_type title, not just its id. So I need to not only preload the requisitions_categories but also traverse the association to preload the categories to get their label.

In my template I want:

    <%= for contract_type <- @requisition.contract_type do %>
        <li><%= contract_type.category.title %></li>
    <% end %>

So I’ve created a get_requisition_with_details function that I call from the controller, but I don’t manage to preload categories.

def get_requisition_with_details!(id) do
    |> Repo.preload(:contract_type) # <-- How do I preload the category here?

I’ve seen somewhere that this syntax should work but it doesn’t:

Repo.preload(contract_type: :category)

Any suggestion?

You need to add the association to the schema, the preload will use the association name. The where option doesn’t work on through associations, so here I’d use has_one instead, and add the associations to a join schema too.

Something like this:

defmodule Requisition do
  schema "requisitions" do
    has_one :contract_type, RequisitionCategory,
      where: [context: "contract_type"]

defmodule RequisitionCategory do
  schema "requisitions_categories" do
    belongs_to :requisition, Requisition
    belongs_to :category, Category

Then you can you can write:

|> get_requisition!()
|> Repo.preload([contract_type: [:category])

Style note: I don’t believe it’s directly relevant (Ecto doesn’t do as much plural-juggling as, say, ActiveRecord) but has_many are usually declared with a plural phrase (:contact_types).

That looks like it should work - what error message do you get?

@delameko I have to handle multiple values, so I don’t think has_one is an option here, or it would be very counter intuitive.

@al2o3cr Yeah, I know I have to pluralise. Actually the same field might be multiple or single value depending on configuration, and single value is much more common, so I started to use singular but I realise that it’s important to pluralise to reflect the data model.

Here’s the error I get:

protocol Ecto.Queryable not implemented for Category of type Atom, the given module does not exist. This protocol is implemented for the following type(s): BitString, Tuple, Ecto.Query, Atom, Ecto.SubQuery

It happening on this line:

|> Repo.preload(contract_type: :category)

I also tried this

|> Repo.preload([contract_type: [:category]])

Or any combination but still get the same error.

The error suggest that Category in belongs_to :category, Category doesn’t refer to a module Ecto knows.

OMG, it totally makes sense, I forgot to alias Category in the RequisitionCategory module!

Thanks a lot, it works like a charm now!

I’m surprised that RequisitionCategory compiled despite this mistake. I would have expected the compiler to yell at me with a more obvious message. Something like “undeclared type ‘Category’” instead of “protocol Ecto.Queryable not implemented for Category of type Atom”! Anyway, I have to accept that it’s a loosely typed language, so that I can’t expect too much from the compiler.

It’s seen that there are several static analysis tools for Elixir. I assume they would have caught that for me. Is there one you can recommend me?