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
 end

schema "requisitions" do
    field :title, :string
    has_many :contract_type, RequisitionCategory, where: [context: "contract_type"]
end

schema "categories" do
    field :title, :string
    field :type, :integer
end

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?