Filter Function in Laravel

Question

Hello i build a filter system so i have a select box where i can select my options and then click a button and come to another page where i can see all my results.

here is a part of my controller

public function getValues(Request $request){
            $typ=$request->get('typ');
            $stellentyp=$request->get('stellentyp');
            $bereich=$request->get('bereich');
            $abschluss=$request->get('abschluss');
            $view = 'user.'.$stellentyp;
            $row = DB::table('angebots')
            ->where('stellentyp', $stellentyp)
            ->count();
        $fwds = DB::table('angebots')
            ->where('stellentyp', 'Freiwilligendienst')
            ->where('typ', $typ)
            ->where('bereich', $bereich)
            ->where('abschluss', $abschluss)
            ->orderBy('stellenname', 'asc')
            ->get();
         $jobs = DB::table('angebots')
            ->where('stellentyp', 'Job')
            ->where('typ', $typ)
            ->where('abschluss', $abschluss)
            ->where('bereich', $bereich)
            ->orderBy('stellenname', 'asc')
            ->get();

but i have a problem now i want that if my field is empty it should not filter

so like this

 if(empty($request->get('typ'))){}
 else{->where('typ', $typ)}

but it doesnt work i dont know how i can collect my database entries with a if/else? Does anyone know how it could work?

thank you!

i changed my function like this but i get a eerror

public function getValues(Request $request){
        $typ=$request->get('typ');
        $stellentyp=$request->get('stellentyp');
        $bereich=$request->get('bereich');
        $abschluss=$request->get('abschluss');
        $user = DB::table('users')->get();
        $angebots = DB::table('angebots') ->orderBy('stellenname', 'asc');
        if(!empty($request->get('stellentyp'))){
            $angebots->where('stellentyp', $stellentyp);
        }
        $angebots->get();
        $row = $angebots->count();
        return view('user/angebots', compact('typ', 'stellentyp', 'bereich', 'abschluss', 'row', 'angebots', 'user'));
    }

if i put the get after this so like that

$angebots = DB::table('angebots') ->orderBy('stellenname', 'asc')->get();

i have no error but if i put it after the if it shows me this error:

Undefined property: Illuminate\Database\MySqlConnection::$firma (View: C:\wamp\sites\j4ylara\resources\views\user\angebots.blade.php)

this is my view:

{{$row}} Angebote insgesamt
        <div class="row">
            @foreach ($angebots as $angebot)
                <div class="col-12 col-md-6 col-lg-4 pt-4">
                    <div class="card offer-card">
                        <div class="card-image">
                            <img class="img-fluid" src="{{ asset('uploads/avatars/' . $user[$angebot->firma -1]->avatar) }}">
                        </div>
                        <div class="card-body">
                            <h4 class="text-j4y-dark praktikumstitel">{{ $angebot->stellenname }}</h4>
                            <a href="{{ route('angebot.details',['id'=>$angebot->firma]) }}">Jetzt mehr anzeigen »</a>
                        </div>
                    </div>
                </div>
            @endforeach
        </div>

if i comment out the variable $angebot everything works

so i have the variable $row and it shows me how many results i get and if i filter it shows me the right number of results

but how can i display my results now if the foreach doesnt work???

Answer 1

You are close. You need to wait with the ->get() until after the ifs. This is so because once you execute the get() the query is executed and you cannot add anymore filters.

$fwds = DB::table('angebots')
->where('stellentyp', $stellentyp)
->orderBy('stellenname', 'asc'); //The get is not needed yet 

if ($this->typ) {
    $fwds->where('typ', 'like', "%{$this->typ}%");
}

if ($this->bereich) {
    $fwds->where('bereich', 'like', "%{$this->bereich}%");
}

if ($this->abschluss) {
    $fwds->where('abschluss', 'like', "%{$this->abschluss}%");
}

$fwds = $fwds->get(); //Place it after the ifs

Answer 2

I use Filter forms like this

class UserFilterForm extends UserRepository
{
    protected $fillable = [
        'id',
        'name',
        'email',
    ];

    public static function rules() :array
    {
        return [
            'id'        => 'nullable|integer|exists:users,id',
            'name'      => 'nullable|string',
            'email'     => 'nullable|email',
        ];
    }

    public function filter()
    {
        $qb = self::query()->with('roles');

        if ($this->id) {
            $qb->where('id', $this->id);
        }

        if ($this->name) {
            $qb->where('name', 'like', "%{$this->name}%");
        }

        if ($this->email) {
            $qb->where('email', 'like', "%{$this->email}%");
        }

        return $qb->paginate(10);
    }
}

And what i do in controller:

class UserController {
    public function index(Request $request)
    {
        $form = new UserFilterForm;

        $form->fill($request->all());

        $this->validate($request->all(), $form::rules());

        return view('users', compact($form->filter()));
    }
}