many-to-many sync() method creates duplicates (is this really the right way to do it?)

@Snapey Thank you so much for your reply!

Indeed I might have incorrectly interpreted the right way to do it, as I was doing something like $model1->relationship()->sync($form-data-array), now this explains why by default the form returned an array of a single company_id and a nested array of prefecture_ids, guess I had to do something like

//model1->relationship()->sync($id)
$company->prefectures()->sync($data)

And in this case I don't need to mutate the data by doing

        foreach ($data['prefecture_id'] as $prefecture_id) {
            $mergedData[] = [
                'company_id' => $tenant->id,
                'prefecture_id' => $prefecture_id,
            ];
        }

So I'll comment it out as well.

I cannot try it out right now, will update this post with results first thing in the morning! 🙇‍♂️

sync() aside, the initial data creation is handled by insert() into new PivotModel, so the issue of no timestamps would still probably persist.

The corresponding M:M models look like this:

Company.php

    public function prefectures(): BelongsToMany {
        return $this->belongsToMany(Company::class, 'company_prefectures');
    }

Prefecture.php

    public function companies(): BelongsToMany
    {
        return $this->belongsToMany(Company::class, 'company_prefectures');
    }

@Pawooo

sync() aside, the initial data creation is handled by insert() into new PivotModel, so the issue of no timestamps would still probably persist.

using insert has two issues compared to sync. Using DB instead of eloquent will never add the timestamps unless you add them as additional keys in the data, and Insert has no check for duplicate records. Sync first checks if the relationship already exists so that duplicates are not created.

@Snapey Sorry to disturb you again. So I adjusted the creation logic to this pattern

//model1->relationship()->sync($model2PrimaryIDs)
$company->prefectures()->sync($data)

and removed data mutation (because data coming from the form is jus a bunch of $model2IDs)

        foreach ($data['prefecture_id'] as $prefecture_id) {
            $mergedData[] = [
                'company_id' => $tenant->id,
                'prefecture_id' => $prefecture_id,
            ];
        }
        $data = $mergedData;

Now my save() looks like this:

    public function save(): void {
	$tenant = Filament::getTenant();
	$data = $this->form->getState(); //dd($data) = array:1 [▼ // app/Filament/Pages/CompanyRegions.php:133  "prefecture_id" => array:3 [▼   0 => "2"    1 => "4"    2 => "6"  ]]
 	$companyID = $tenant->id;
    $company = auth()->user()->companies->find($companyID);  
       if ($company->prefectures->contains(1) === false) {
            $Entry = new ModelsCompanyPrefectures;
            $Entry->fill($data); // dd($Entry) = #attributes: array:1 [▼    "prefecture_id" => array:3 [▼      0 => "3"      1 => "5"      2 => "7"    ]]
            $Entry->save($data);
		    //$company->prefectures()->sync($data);

            Notification::make()
                ->success()
                ->title('Entry Created')
                ->send();
            } else {
                // $Entry->update($data);
                $company->prefectures()->detach();
                $company->prefectures()->sync($data);
                
                Notification::make()
                ->success()
                ->title('Entry Updated')
                ->send();
           }
        }

I'm supposed to be passing the array of $model2_ids (prefectures), which is what I think I'm doing, yet without data mutation it throws errors depending on what I do. Should I be doing data mutation regardless?

For instance, if I do $company->prefectures()->sync($data) or $Entry = new ModelsCompanyPrefectures; $Entry->fill($data); it removes timestamps created by Eloquent and associates array keys with table columns, which results in the following error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column '0' in 'field list'

insert into
  `company_prefectures` (`company_id`, `0`, `1`, `2`)
values
  (1, 2, 4, 6)

Doing $Entry->save($data) or $Entry->save(array_values($data)) will generate timestamps, but throw this error

ksort(): Argument #1 ($array) must be of type array, string given

What is it that I'm lacking to figure this out? Even if I hypothetically bring back the data mutation, timestamp issue would persist. Sorry if this is very basic, but at least I think I did my share of web search before posting my issue.

I honestly feel lost :( I just can't help but feel like all this process is probably already figured out for me and I just don't know the piece required to follow the path laid out for me by the Laravel/Filament devs.

UPD: People suggest using pivot model architecture as one possible solution (Github), maybe I should try it with my Custom Page

@Pawooo sorry, I dont understand what you are receiving into this function

@Snapey

I added dd($data) contents as a comment at every important step above, could you please specify if there's anything else you'd like to see?

After I played around with it it looks like this and it works, but I mutate the $data from my form:

        public function save(): void {

        $tenant = Filament::getTenant();
        $data = $this->form->getState();

        foreach ($data['prefecture_id'] as $prefecture_id) {
            $mergedData[] = [
                'prefecture_id' => $prefecture_id,
            ];
        }
        $data = $mergedData;

        $companyID = $tenant->id;

        $company = auth()->user()->companies->find($companyID);   

        if ($company->prefectures->contains($companyID) === false) {
            $company->prefectures()->sync($data);

            
            Notification::make()
                ->success()
                ->title('Entry Created')
                ->send();
            } else {
                $company->prefectures()->detach();
                $company->prefectures()->sync($data);
                
                Notification::make()
                ->success()
                ->title('Entry Updated')
                ->send();
           }
        }

I got timestamps saved by adding ->withTimestamps() to my CompanyModel.

Problems remaining:

1. I end up mutating the data as $mergedData (not sure if it's the correct way to do it, but it works)
2. If I update the values, I get duplicates, so I $company->prefectures()->detach(); everything before sync() if there's already an entry there
3. The code does not feel right overall (too many queries?)

I got it! Everything works, sync() as well. The reason why I got duplicates is because I specified belongsToMany(Company::class) instead of belongsToMany(Prefecture::class) and was returning an array with items duplicated based on the number of associated prefectures.

Company.php

    public function prefectures(): BelongsToMany {
        return $this->belongsToMany(Company::class, 'company_prefectures');
    }

Should be

Company.php

    public function prefectures(): BelongsToMany {
        return $this->belongsToMany(Prefecture::class, 'company_prefectures');
    }

@snapey Thanks again for trying to help me!