zaster's avatar

Check pivot table for duplicate bill_ids

 $this->payBill->bills()->sync(collect($this->payBillBills)->filter()->keys());

Works well (But i need to have only unique bill_ids in the pivot table)

The pivot table looks like this

bill_paybill

id
bill_id
paybill_id
created_at
updated_at
deleted_at

Bill

    public function payBills()
    {
        return $this->belongsToMany('App\Models\PayBill', 'bill_paybill', 'bill_id', 'paybill_id');
    }

PayBill

   public function bills()
    {
        return $this->belongsToMany('App\Models\Bill', 'bill_paybill', 'paybill_id', 'bill_id');
    }

If it is necessary the livewire component looks like this

<?php

namespace App\Http\Livewire;

use Livewire\Component;
use Livewire\WithPagination;
use App\Models\Bill;

class PayBillAssignBillSection extends Component
{
    use WithPagination;

    public $sortBy = 'id';
    public $sortAsc = false;
    public $payBill;
    public $bills;
    public $search;
    public $perPage = 10;
    public $payBillBills = [];


    public function sortBy($field)
    {
        if($field == $this->sortBy){
            $this->sortAsc = !$this->sortAsc;
        }
         $this->sortBy = $field;
    }


    public function updatedPayBillBills()
    {
        if($this->payBill->status == "Pending")
        {
            //Check whether the bill is not assigned to another payBill
           //This doesn't check it
            $this->payBill->bills()->sync(collect($this->payBillBills)->filter()->keys());
        }

    }

    public function updatingSearch()
    {
        $this->resetPage();
    }

    public function mount()
    {
        $this->bills = Bill::all();

        $this->payBillBills = array_fill_keys($this->payBill->bills->pluck('id')->toArray(), 1);
        // dd($this->payBillBills);
    }

    public function render()
    {
        $billList = Bill::with('company')
         ->where('company_id', $this->payBill->company->id)
         ->where('comp_id', $this->payBill->comp->id)
          ->where('status', 2) //Verified
          ->Where('paid_by', null)
        //   ->whereDoesntHave('payBills') //Relationship Absense check
          ->when($this->search, function($query){
            return $query->where(function($query){
                $query->where('name', 'like', '%'. $this->search . '%');
            });
        })
        ->orderBy('id', 'ASC')
        ->paginate(10);

        $selectedBills = $this->payBill->bills()
        ->orderBy('id', 'ASC')->get();

        $assignedBills = $this->payBill->bills()
        ->whereDoesntHave('payBills')
        ->orderBy('id', 'ASC')->get();


        return view('livewire.pay-bill-assign-bill-section', [
            'billList' => $billList,
            'selectedBills' => $selectedBills,
            'assignedBills' => $assignedBills,
        ]);
    }
}

<div>
    <div class="grid grid-cols-1 text-left xl:grid-cols-6">
        <div class="col-span-3 p-4">
            <p class="text-2xl underline">Selected Bills</p> <br>
            @foreach ($selectedBills as $selectedBill)
               <p class="text-xl">
               <a href="{{ route('employees.pays.bills.edit', $selectedBill->id) }}" target="_blank">BID {{ $selectedBill->id }} :</a>
               Rs.  {{ number_format($selectedBill->getAmount(), 2) }}
               </p>
            @endforeach
            <p class="text-2xl underline">Assigned Bills</p> <br>
            @foreach ($assignedBills as $assignedBill)
               <p class="text-xl">
               <a href="{{ route('employees.pays.bills.edit', $assignedBill->id) }}" target="_blank">BID {{ $assignedBill->id }} :</a>
               Rs.  {{ number_format($assignedBill->getAmount(), 2) }}
               </p>
            @endforeach

        </div>

        @if($payBill->status == "Pending")
            <div class="col-span-3 p-4">

            <p class="text-2xl underline">Bill List</p> <br>

            <x-jet-input wire:model="search" id="search" type="search" class="block w-full" placeholder="Search"  autocomplete="off" />
                <br>

                @foreach($billList as $bill)
                    <p class="inline text-lg"><input type="checkbox" value="1" wire:model="payBillBills.{{ $bill->id }}">
                        <a href="{{ route('employees.pays.bills.edit', $bill->id) }}" target="_blank">BID {{ $bill->id }} :</a>
                        Rs. {{ number_format($bill->getAmount(), 2) }}
                    </p> <br>
                @endforeach
                {{-- <a href="{{ url()->previous() }}"><x-jet-button>Back</x-jet-button></a> --}}
                <br>
                {{-- Pagination --}}
                <div class="mx-4 mt-4">
                    {{ $billList->links() }}
                </div>
            </div>
        @endif
    </div>

</div>
0 likes
9 replies
zaster's avatar

@MohamedTammam

The table structure should be many to many for future purpose(It should be the standard) But currently i need to have unique bill_ids in the pivot table

I tried this from the database side

        Schema::create('bill_paybill', function (Blueprint $table) {
            $table->id();
            $table->integer('bill_id')->unique();
            $table->integer('paybill_id');
            $table->timestamps();
            $table->softDeletes();
        });

But then i am getting this error

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1' for key 'PRIMARY' (SQL: insert into `pay_bills` (`account_id`, `company_id`, `comp_id`, `date`, `reference`, `realise_date`, `realise_note`, `id`, `updated_at`, `created_at`) values (?, 7, 1, 2022-03-01, ?, ?, ?, 10, 2022-03-01 09:07:46, 2022-03-01 09:07:46))
MohamedTammam's avatar

@zaster If it isn't one-to-one or one-to-many, you need to check it yourself first.

If(DB::table('bill_paybill')->where('bill_id', $this->payBill->id)->count() == 0)
	// Insert your data
else
	// Do whatever you want

But generally I don't recommend doing this. Your database should reflect your business model, when you business changes then change your database schema too.

zaster's avatar

@MohamedTammam

Almost there Need to do something like this(Which doesn't work)

If(DB::table('bill_paybill')->where('bill_id', $this->payBill->pivot->bill_id)->count() == 0)
	// Insert your data
else
	// Do whatever you want

Please or to participate in this conversation.