How to increment or decrement a order column of pivot table

I am working on a purchase request. Purchase requests have types that require different approvals. There are only a maximum of like three approvals that may be need for any one type. The pivot table is where the order column resides. So the 3 tables involved are:

PR_TYPES

id
name

PR_TYPE_APPROVALS

id
Name

PR_TYPE_APPROVAL_PR_TYPE

purchase_request_type_id
pr_type_approval_id
order

This is a livewire component. I am using arrows on the UI to move the approval up or down.

Blade file:

@forelse($approvals as $approval)
                    <x-table.row
                            wire:loading.class.delay="opacity-50"
                            {{--                    onclick="window.location='{{ route('', $ticket->id) }}'" --}}
                            style="cursor: pointer;"
                    >
                        <x-table.cell>
                            {{$approval->pivot->id}}
                        </x-table.cell>
                        <x-table.cell>
                            {{$approval->name}}
                        </x-table.cell>
                        <x-table.cell>
                            {{$approval->pivot->order}}
                        </x-table.cell>
                        <x-table.cell>
                            <a
                                wire:click="moveApprovalUp({{$approval}})"
                                class="btn btn-primary">
                                <i class="fa fa-arrow-up"></i>
                            </a>
                            <a
                                wire:click="moveApprovalDown({{$approval}})"
                                class="btn btn-primary">
                                <i class="fa fa-arrow-down"></i>
                            </a>
                        </x-table.cell>
                    </x-table.row>
                @empty
                    <x-table.row wire:loading.class.delay="opacity-50">
                        <x-table.cell colspan="7">
                            <div class="text-center fs-2">
                                No results found...
                            </div>
                        </x-table.cell>
                    </x-table.row>
                @endforelse

Here is the component:

public $purchaseRequestType;

    public function moveApprovalUp($value)
    {
        $approval = $this->purchaseRequestType->typeApprovals()->where('pr_type_approval_id', $value['pivot']['pr_type_approval_id'])->first();
        //Check if there is more than 1 approval
        $numberOfApprovals = $this->purchaseRequestType->typeApprovals->count();

        if($numberOfApprovals <= 1) {
            $this->purchaseRequestType->typeApprovals()->updateExistingPivot($value['pivot']['pr_type_approval_id'], [
                'order' => 1,
            ]);
            return;
        }



        //duct one from current order
        $newOrderNumber = $value['pivot']['order'] - 1;
        //update the row
        $this->purchaseRequestType->typeApprovals()->updateExistingPivot($value['pivot']['pr_type_approval_id'], [
            'order' => $newOrderNumber,
        ]);


        foreach($this->purchaseRequestType->typeApprovals as $approvals) {

        }


    }

    public function moveApprovalDown($value)
    {
        $numberOfApprovals = $this->purchaseRequestType->typeApprovals->count();
        if($numberOfApprovals < 1) {
            return;
        }
    }
    public function render()
    {
        $approvals = $this->purchaseRequestType->typeApprovals;

        return view('livewire.purchasing.purchase-request.approval-management.edit-purchase-request-approval',
        compact(
            'approvals'
        )
        );

I am struggling with how to recalculate the order column once you move one up or down. I am not worried about how many queries because there are so few approvals need for each PR type. Any help would be appreciated.

Also, this is difficult to search because it gets confused with orderBy.

LaryAI

2 years ago

Level 58

To handle the reordering of the approvals in the pivot table, you need to adjust the order column for the affected rows. When moving an approval up or down, you should swap the order values of the two adjacent rows. Here's how you can implement the moveApprovalUp and moveApprovalDown methods:

public function moveApprovalUp($approval)
{
    $currentOrder = $approval['pivot']['order'];

    // Find the approval directly above
    $previousApproval = $this->purchaseRequestType->typeApprovals()
        ->wherePivot('order', '<', $currentOrder)
        ->orderByPivot('order', 'desc')
        ->first();

    if ($previousApproval) {
        // Swap the order values
        $this->purchaseRequestType->typeApprovals()->updateExistingPivot($approval['pivot']['pr_type_approval_id'], [
            'order' => $previousApproval->pivot->order,
        ]);

        $this->purchaseRequestType->typeApprovals()->updateExistingPivot($previousApproval->pivot->pr_type_approval_id, [
            'order' => $currentOrder,
        ]);
    }
}

public function moveApprovalDown($approval)
{
    $currentOrder = $approval['pivot']['order'];

    // Find the approval directly below
    $nextApproval = $this->purchaseRequestType->typeApprovals()
        ->wherePivot('order', '>', $currentOrder)
        ->orderByPivot('order', 'asc')
        ->first();

    if ($nextApproval) {
        // Swap the order values
        $this->purchaseRequestType->typeApprovals()->updateExistingPivot($approval['pivot']['pr_type_approval_id'], [
            'order' => $nextApproval->pivot->order,
        ]);

        $this->purchaseRequestType->typeApprovals()->updateExistingPivot($nextApproval->pivot->pr_type_approval_id, [
            'order' => $currentOrder,
        ]);
    }
}

In the moveApprovalUp method, you find the approval that is directly above the current one by looking for the nearest lower order value. If such an approval exists, you swap their order values.

Similarly, in the moveApprovalDown method, you find the approval that is directly below the current one by looking for the nearest higher order value and swap their order values if it exists.

Make sure to refresh the $approvals collection after performing these operations to reflect the new order in your Livewire component's view. You can do this by reassigning the $approvals property:

$this->approvals = $this->purchaseRequestType->typeApprovals()->orderBy('pivot_order')->get();

This will ensure that the UI is updated with the new order of approvals.

Please or to participate in this conversation.