CODEWITHSUNDEEP
mysqldatabaselaravel
edward
edward

Reputation: 107

Laravel pivot table need to have duplicate entries

I am using Laravel to build a small customer list system.

I need to link COMPANY with CONTACT_PERSON. In a normal situation, I can use many to many pivot tables to link and update them.

However, I need to keep historical records. That means the same person can act as a contact in the period earlier, resigned, and reappointed later with another period.

I try to duplicate the pivot table entries with phpMyAdmin, makes the same COMPANY linked the same CONTACT_PERSON twice, and two entries were found when retrieving the COMPANY records.
However, how can use Laravel duplicate the entries? How to use Laravel to CURD those. Or I just do it in the wrong way?

Thank you very much!

EDIT : Example :
Company A employed Mr. A between 01-01-2001 till 02-02-2001, Compnay A employed Mr. A again on 03-03-2001 till 04-04-2001. There are company B,C,D ... in company table, There are Mr. B, C , D in the employee table too.

Use Laravel relationship pivot table to link company A to Mr. A will have two records in the above case (I need to have a historical record).

Is it the right way to handle such cases with Laravel many to many relationships? Or I should consider other ways ?

Upvotes: 1

Views: 656

Answers (1)

Eric Landheer
Eric Landheer

Reputation: 2243

A pivot table can contain additional columns. You could consider adding migrations that add a started_at and resigned_at datetime field to your pivot table.

Your data structure would look something like this:

Company

  • id
  • ...

Contact_person_company

  • contact_person_id
  • company_id
  • started_at
  • resigned_at

Contact person

  • id
  • ...

After that, you could create functions in your model to work with the pivot columns and check for their status. A null value for the resigned_at column indicates that the ContactPerson is still active.

<?php

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsToMany;

class Company extends Model
{
    // ... other functions

    public function contactPersons(): BelongsToMany
    {
        $this->belongsToMany(Company::class)
            ->withPivot('started_at', 'resigned_at')
            ->wherePivotNull('resigned_at');
    }

    public function resignedContactPersons()
    {
        $this->belongsToMany(Company::class)
            ->withPivot('started_at', 'resigned_at')
            ->wherePivotNotNull('resigned_at');
    }

}

You should define these functions as well on the ContactPerson model.

If the logic gets very complicated, consider creating a Pivot model (CompanyContactPerson).

Please note that the combination of contact_person_id, company_id started_at should be unique. Otherwise, considering adding a primary key like ID the company_contact_person table to identify these records.

Upvotes: 1

Related Questions