Question regarding concatenating assignment operator in Eloquent Query builder

I'm working on SQL queries using Eloquent, and I'm having trouble concatenating my SQL strings in my query.

$search = DB::table('players')
      ->join('nfl_team', 'nfl_team.team_id', '=', 'players.team_id')
      ->join('colleges', 'colleges.college_id', '=', 'players.college_id')
      ->join('player_positions', 'nfl_team.team_id', '=', 'players.team_id')
      if (isset($first_name) && !empty($first_name))
      {
        ->where('players.FName', 'LIKE', '%'.$first_name.'%')
      }
      if (isset($last_name) && !empty($last_name))
      {
        ->where('players.LName', 'LIKE', '%'.$last_name.'%')
      }
      if (isset($team) && !empty($team))
      {
        ->where('players.team_id', '=', $team)
      }
      if (isset($college) && !empty($college))
      {
        ->where('players.college_id', '=', $college)
      }
      if (isset($position) && !empty($position))
      {
        ->where('players.position_id', '=', $position)
      }
      ->get();

The problem i'm having is with the code within the if statements.

In PHP, I would use something like this

if ( isset( $_GET['first_name'] ) && !empty( $_GET['first_name'] ) ) 
{
    $sql .= " AND players.FName LIKE '%" . $_GET['first_name'] . "%'";
}

But i'm not sure what the syntax is in Eloquent

athulpraj

8 years ago

Level 1

You can use Eloquent new Query

$players= $players->newQuery();
if ($request->has('first_name')) {
      $players->where(function ($query) use ($request)
      {
           $query->where('FName','LIKE','%'.$request->first_name.'%');
      });
}

ekhlas

8 years ago

Level 2

Try this

$search = DB::table('players')
      ->join('nfl_team', 'nfl_team.team_id', '=', 'players.team_id')
      ->join('colleges', 'colleges.college_id', '=', 'players.college_id')
      ->join('player_positions', 'nfl_team.team_id', '=', 'players.team_id');
      if (isset($first_name) && !empty($first_name))
      {
        $search = $search->where('players.FName', 'LIKE', '%'.$first_name.'%');
      }
      if (isset($last_name) && !empty($last_name))
      {
        $search = $search->where('players.LName', 'LIKE', '%'.$last_name.'%');
      }
      if (isset($team) && !empty($team))
      {
        $search = $search->where('players.team_id', '=', $team);
      }
      if (isset($college) && !empty($college))
      {
        $search = $search->where('players.college_id', '=', $college);
      }
      if (isset($position) && !empty($position))
      {
        $search = $search->where('players.position_id', '=', $position);
      }
      $search = $search->get();

oroalej

8 years ago

Level 3

I think you can use When to check for the request();

->join('player_positions', 'nfl_team.team_id', '=', 'players.team_id')
->when($request->filled('first_name'), function($query) use ($request){
   $query->where('players.FName', 'LIKE', '%'. $request->first_name.'%');
});

vinnliang

8 years ago

Level 1

Thanks guys! These all work great!

Please or to participate in this conversation.