Many to many relationship

Questions / Help
,
1

Context:

A customer_lead can be associated to many service_category. A service_category can be associated to many customer_leads.

customerleads_service migration

 create table(:customerlead_service) do
      add :service_category_id, references("service_categories")
      add :customer_lead_id, references("customer_leads")

      timestamps()
    end

customer_service schema

schema "customerlead_service" do
    belongs_to :customer_leads, CustomerLead
    belongs_to :service_categories, ServiceCategory

    timestamps()
  end

customer migration

  create table(:customer_leads) do
      add :first_name,      :string
      add :last_name,       :string
      add :email,           :string
      add :address,         :string
      add :phone_number,    :string
      add :company_name,    :string
      add :message,         :string
      add :status,          :boolean, null: false, default: false

      timestamps()
    end

customer schema

  schema "customer_leads" do
    field :first_name,      :string
    field :last_name,       :string
    field :email,           :string
    field :address,         :string
    field :phone_number,    :string
    field :company_name,    :string
    field :message,         :string
    field :status,          :boolean

    many_to_many :service_categories, ServiceCategory,
      join_through: CustomerleadService

    timestamps()
  end
   def changeset(customer, attrs \\ %{}) do
    customer
    |> cast(attrs, @required_fields ++ @optional_fields)
    |> validate_required(@required_fields)
  end

  def add_service_to_customer_lead(changeset, attrs) do
    service = Ramlaw.Services.get_service_category_by([slug: attrs["services"]])
              |> Repo.preload(:customer_leads)

    changeset
    |> Repo.preload(:service_categories)
    |> Ecto.Changeset.change()
    |> Ecto.Changeset.put_assoc(:service_categories, [service])
    |> Repo.update!()
  end

service migration

create table(:service_categories) do
      add :title,     :string
      add :slug,      :string
      add :status,    :boolean, null: false, default: false

      timestamps()
    end

service schema

schema "service_categories" do
    field :title,   :string
    field :slug,    :string
    field :status,  :boolean

    has_many :services, Service,
      on_delete:    :delete_all,
      foreign_key:  :service_categories_id,
      on_replace:   :nilify

    many_to_many :customer_leads, CustomerLead,
      join_through: CustomerleadService

    timestamps()
  end

The problem

In my test I noticed when I first create a customer_lead, the service_category get’s associated properly

AFTER INSERT: %Ramlaw.Schema.CustomerLead{
  __meta__: #Ecto.Schema.Metadata<:loaded, "customer_leads">,
  address: nil,
  company_name: "profilo",
  email: "venomnert1994@hotmail.com",
  first_name: "Nert",
  id: 4,
  inserted_at: ~U[2019-11-10 13:23:06Z],
  last_name: "test",
  message: "fdafsa",
  phone_number: "6473355012",
  service_categories: [
    %Ramlaw.Schema.ServiceCategory{
      __meta__: #Ecto.Schema.Metadata<:loaded, "service_categories">,
      customer_leads: [],
      id: 10,
      inserted_at: ~U[2019-11-10 13:23:06Z],
      services: #Ecto.Association.NotLoaded<association :services is not loaded>,
      slug: "family-law",
      status: false,
      title: "Family Law",
      updated_at: ~U[2019-11-10 13:23:06Z]
    }
  ],
  status: nil,
  updated_at: ~U[2019-11-10 13:23:06Z]
}

However, when I query the same customer_lead that was added, its service_category is empty. Also this applies to the associated service_category as well.

Code snippet

service_cat
    |> Repo.preload(:customer_leads)
    |> IO.inspect(label: "RESULTS")

 ClientLeads.get_client_lead(added_client.id)
    |> IO.inspect(label: "TEST")

result

RESULTS: %Ramlaw.Schema.ServiceCategory{
  __meta__: #Ecto.Schema.Metadata<:loaded, "service_categories">,
  customer_leads: [],
  id: 10,
  inserted_at: ~U[2019-11-10 13:23:06Z],
  services: #Ecto.Association.NotLoaded<association :services is not loaded>,
  slug: "family-law",
  status: nil,
  title: "Family Law",
  updated_at: ~U[2019-11-10 13:23:06Z]
}
TEST: %Ramlaw.Schema.CustomerLead{
  __meta__: #Ecto.Schema.Metadata<:loaded, "customer_leads">,
  address: nil,
  company_name: "profilo",
  email: "venomnert1994@hotmail.com",
  first_name: "Nert",
  id: 4,
  inserted_at: ~U[2019-11-10 13:23:06Z],
  last_name: "test",
  message: "fdafsa",
  phone_number: "6473355012",
  service_categories: [],
  status: false,
  updated_at: ~U[2019-11-10 13:23:06Z]
}

I feel like I have set up everything correctly regarding the many-to-many association; however, i’m not able to retrieve the associations.

1 Like
2

If anyone else runs into this issue, the solution is to remove schema for the joining table customer_service schema.

And update your schema to the following, which is to remove the timestamp:

customerleads_service migration

 create table(:customerlead_service) do
      add :service_category_id, references("service_categories")
      add :customer_lead_id, references("customer_leads")
    end

However, I’m not sure as to why the schema was causing this issue though.

Also this post at ElixirSchool definitely helped:

1 Like
3

timestamps() don’t work when you create an intermediary record like that. You have to create a new record independently by passing the parents tables IDs as attributes. The problem I’ve had with the update syntax as shown in the elixir school article is that you’re replacing all intermediary records customer_lead_services with those specific customer_lead_id and service_categories_id, so it’s not the best option to use the syntax if you want to continue to add intermediary records afterward.

3 Likes
4

Hey @csisnett sorry for the late response. But can you elaborate more on this by providing some pseudo code. I’m not able to follow what you are saying, i’m still new to ecto :confused:

5

Sure no problem @venomnert,

you would need a changeset function for the customerlead_service schema as well.

def changeset(customerlead_service, attrs) do
  customerlead_service
  |> cast(attrs, [:customer_leads_id, :service_categories_id])
  |> validate_required([:customer_leads_id, :service_categories_id])
end

To create the record you would need the parent tables to be created already


customer_lead = %CustomerLead{...}
service_category = %ServiceCategory{...}
attrs = %{"customer_leads_id" => customerlead.id, "service_categories_id" => service_category.id}

and call this function (which should be in your Context) passing attrs

def create_customerlead_service(attrs = %{}) do
  %CustomerLeadService{}
  |> CustomerLeadService.changeset(attrs)
  |> Repo.insert()
end

Not relevant to the question but I would change field names in the customerlead_service schema from :customer_leads and :service_categories to :customer_lead and service_category as you’re only referring to one and not several in the intermediary table.

1 Like
6

@csisnett with this current approach as you mentioned initially “You have to create a new record independently by passing the parents tables IDs as attributes”, I would manually have to create the record, so that timestamp works.

Out of curiosity, is there a better alternative to creating many-to-many relationship?