How to apply table filters after joining with union?

public function customerAccountTotalBalances(): HasMany
    {
        $cashMovements = $this->financeCashMovements()
            ->select(...)
            ->orderBy('id', 'asc');

        $bankMovements = $this->financeBankMovements()
            ->select(...)
            ->orderBy('id', 'asc');

        $bondMovements = $this->financeBondMovements()
            ->select(...)
            ->orderBy('id', 'asc');

        $bondMovementOuts = $this->financeBondMovementOuts()
            ->select(...)
            ->orderBy('id', 'asc');

        $movements = $cashMovements->union($bankMovements)->union($bondMovements)->union($bondMovementOuts);
        return $movements->orderBy('created_at', 'asc');
    }

When I add filters in the table, the where queries do not affect the records because I joined with union. Is there any other method you can suggest to take the filters in the model and add them manually or to solve this problem?

Tray2

1 year ago

Best Answer

Level 73

The simplest would be to create a view in the database. That would also keep your code cleaner.

https://tray2.se/posts/use-a-view-instead-of-a-complex-eloquent-query-in-your-laravel-application

Or you can use the ->when method.

 return view('books.index')
            ->with([
                'books' => BookIndexView::query()
                    ->when($request['authors'], function ($query, $authors) {
                        $query->whereIn('author_id',
                            $this->numericStringToArray($authors));
                    })
                    ->when($request['published'], function ($query, $published) {
                        $query->where('published_year', $published);
                    })
                    ->when($request['genre'], function ($query, $genre) {
                        $query->where('genre', $genre);
                    })
                    ->when($request['format'], function ($query, $format) {
                        $query->where('format', $format);
                    })
                    ->when($request['search'], function ($query, $search) {
                        $query->where('title', 'LIKE',  "%$search%")
                        ->orWhere('author_name', 'LIKE', "%$search%")
                        ->orWhere('series', 'LIKE', "%$search%");
                    })
                    ->orderBy('author_name')
                    ->orderBy('series')
                    ->orderBy('part')
                    ->orderBy('published_year')
                    ->get(),
            ]);
    }

1 like

Please or to participate in this conversation.