Understanding many_to_many relations in Ecto

[krishna_vaguelyright]

I am trying to handle how Ecto handles many_to_many relations.

I am trying to build a many_to_many relation within the products table with self(products table)

The joint table looks like

    belongs_to :collection, KayaanPrints.Product, type: Ecto.ULID
    belongs_to :product, KayaanPrints.Product, type: Ecto.ULID

1. Should the joint table have its own primary_key/id or should it be a composite key built using both the above fields.
2. I came across using many_to_many for the same. I want 2 fields collections & products. I couldnt figure out how to do this even with join_keys: [product_id: :id, collection_id: :id]
3. I tried has_many, through: which is straight forward but only helps with reading.
4. For writing/updating I have to go through joint table but I am confused on how will new relations be updated/added/removed.

How much of this can be automated & what needs to handled manually?

[krishna_vaguelyright]

Found this thread that discusses this… will update as I implement this

New Ecto "How-To" Guide: Self-referencing many to many

On one of my early design choices for an earlier version of Metamorphic I used a separate relationship_type that was associated to a relationship through a has_many / belongs_to association. # relationship.ex ... belongs_to :relationship_type, RelationshipType, type: :binary_id ... # relationship_type.ex ... has_many :relationships, Relationship ... # person.ex ... many_to_many :relationships, Person, join_through: Relationship, join_keys: [person_id: :id, relation_id: :id], join_…

[krishna_vaguelyright]

I couldnt make the self-referencing relation work, so made a seperate collections table. Facing a tricky issue now.

When using many_to_many association between products & collections tables. When trying to cast_assoc during creating a new product & associating it with an existing collection, Ecto is trying to insert the collection(even when id is present) & hence failing. Below is what the changeset looks like.

#Ecto.Changeset<
  action: nil,
  changes: %{
    name: "Test Product",
    status: :active,
    type: :product,
    description: "Test Description",
    tags: ["test", "product"],
    minimum_order_quantity: Decimal.new("1"),
    price: Decimal.new("100.0"),
    uom: "pcs",
    available_quantity: Decimal.new("100"),
    collections: [
      #Ecto.Changeset<
        action: :insert,
        changes: %{
          id: "01JJVSBV2DS2V6EMSCTHPGDGX5",
          name: "Test Collection",
          status: :active,
          type: :collection,
          description: "Test Description"
        },
        errors: [],
        data: #KayaanPrints.Collection<>,
        valid?: true,
        ...
      >
    ]
  },
  errors: [],
  data: #KayaanPrints.Product<>,
  valid?: true,
  ...

Error:

Reason: %Ecto.ConstraintError{
  type: :unique,
  constraint: "collections_pkey",
  message: "constraint error when attempting to insert struct:\n\n    * \"collections_pkey\" (unique_constraint)