How can I group filtering on a certain column

I have a string column status in my table invoices that can have this values: public array $statuses = ['waiting', 'pending', 'attested', 'paid', 'deleted']; Then why I render the view I let the user filter on this status column so that invoices with the chosen status are displayed in the view. The render method:

public function render(): View
    {
        $invoices = Invoice::query()
            ->select([
                'invoices.*'
            ]);
        foreach ($this->searchColumns as $column => $value) {
            if (!empty($value)) {
                $invoices
                    ->when($column === 'ub', fn($invoices) => $invoices->whereRelation('user', 'users.id', $value))
                    ->when($column === 'status', fn($invoices) => $invoices->where('invoices.'.$column, 'LIKE', '%'.$value.'%'))
                ;
            }
        }
        $invoices->orderBy($this->sortColumn, $this->sortDirection);
        return view('livewire.invoices.all', [
            'invoices' => $invoices->simplePaginate(10),
        ]);
    }

Everything works fine, but I have a need to let the user filter on a status that contains more than one of the statuses above. I like to called that status unpaid and I like it to display invoices that has any of the statuses waiting, pending and attested. How can I achieve that? I like to add unpaid to $statuses so the user can chose it in the view's filter that is a dropdown. And when unpaid is chosen I like to run a query Where status is IN ['waiting', 'pending', 'attested] instead of status = unpaid since unpaid doesn't exist a status in DB.

LaryAI

1 year ago

Level 58

To achieve the desired functionality, you can modify your filtering logic to handle the special case when the user selects the "unpaid" status. You can use the whereIn method to filter invoices with statuses of "waiting", "pending", or "attested". Here's how you can adjust your render method:

public function render(): View
{
    $invoices = Invoice::query()
        ->select(['invoices.*']);

    foreach ($this->searchColumns as $column => $value) {
        if (!empty($value)) {
            $invoices
                ->when($column === 'ub', fn($invoices) => $invoices->whereRelation('user', 'users.id', $value))
                ->when($column === 'status', function ($invoices) use ($value) {
                    if ($value === 'unpaid') {
                        // If the status is 'unpaid', filter for 'waiting', 'pending', or 'attested'
                        $invoices->whereIn('invoices.status', ['waiting', 'pending', 'attested']);
                    } else {
                        // Otherwise, perform a normal LIKE search
                        $invoices->where('invoices.status', 'LIKE', '%' . $value . '%');
                    }
                });
        }
    }

    $invoices->orderBy($this->sortColumn, $this->sortDirection);

    return view('livewire.invoices.all', [
        'invoices' => $invoices->simplePaginate(10),
    ]);
}

Explanation:

Check for 'unpaid' Status: In the when clause for the status column, check if the $value is 'unpaid'.
Use whereIn: If the status is 'unpaid', use the whereIn method to filter invoices with statuses 'waiting', 'pending', or 'attested'.
Default Behavior: If the status is not 'unpaid', continue with the existing LIKE search logic.

This approach allows you to add 'unpaid' to your $statuses array for the dropdown, and when selected, it will correctly filter the invoices based on the specified statuses.

Shivamyadav

1 year ago

Level 20

Hey man, I need your help could you please dm me on my email [email protected] Please dude.

Shivamyadav

1 year ago

Level 20

Try this

public function render(): View
{
    $invoices = Invoice::query()
        ->select(['invoices.*']);

    foreach ($this->searchColumns as $column => $value) {
        if (!empty($value)) {
            $invoices
                ->when($column === 'ub', fn($invoices) => $invoices->whereRelation('user', 'users.id', $value))
                ->when($column === 'status', function ($invoices) use ($value) {
                    if ($value === 'unpaid') {
                        // Custom filter for "unpaid" invoices
                        return $invoices->whereIn('invoices.status', ['waiting', 'pending', 'attested']);
                    }
                    // Default filter for single statuses
                    return $invoices->where('invoices.status', 'LIKE', '%' . $value . '%');
                });
        }
    }

    $invoices->orderBy($this->sortColumn, $this->sortDirection);

    return view('livewire.invoices.all', [
        'invoices' => $invoices->simplePaginate(10),
    ]);
}

Please or to participate in this conversation.