CODEWITHSUNDEEP
phplaravellaravel-5eloquent
Dreengreen
Dreengreen

Reputation: 347

Laravel Eloquent where and order conditions only if needed

Please look at following code. This code I have written with Codeigniter. What I need to highlight it where condition and order conditions works only if those post requests are set. Otherwise it i just select * from student.

Codeigniter Code

$this->db->select('*');
$this->db->from('student');
if($this->input->post('first_name') != NULL){
    $first_name = $this->input->post('first_name');
    $this->db->where('first_name', $first_name);
}

if($this->input->post('last_name') != NULL){
    $last_name= $this->input->post('last_name');
    $this->db->where('last_name', $last_name);
}

if($this->input->post('order_by') != NULL){
    $order_by= $this->input->post('order_by');
    $this->db->order_by($order_by);
}

$query = $this->db->get();

Laravel Code

I am going do the same thing with laravel.

$first_name = $request->input('first_name');
$last_name = $request->input('last_name');
$order_by = $request->input('$order_by');

$students = Student::orderBy($order_by)
      ->where('first_name',$first_name)
      ->where('last_name',$last_name);
      ->paginate(10);

I able to run above code. The code works when there all post requests.

But if there is no first_name post request I need to remove ->where('first_name',$first_name).

If there i no order_by post request, I need to remove orderBy($order_by).

How to do it with above Laravel code.

Upvotes: 1

Views: 1759

Answers (5)

Edwin
Edwin

Reputation: 1135

An approach would be to check if the name is set and then add it to the query builder like so:

$first_name = $request->input('first_name');
...

$students = Student::orderBy($order_by)
if ($first_name) {
    $students->where('first_name',$first_name)
}

$students->paginate(10);

However you can clean that up! Since Laravel 5.2.27 you can do as follows:

$students = Student::orderBy($order_by)
->when($request->input('first_name'), function($query) use ($request){
    return $query->where('first_name', $request->input('first_name'));
})
->paginate(10);

To make that even more readable you can use a custom macro for if-statements:

$students = Student::orderBy($order_by)
->if($request->input('first_name'), 'first_name', '=', $request->input('first_name'))
->paginate(10);

And the macro:

use Illuminate\Database\Query\Builder;

Builder::macro('if', function ($condition, $column, $operator, $value) {
    if ($condition) {
        return $this->where($column, $operator, $value);
    }

    return $this;
});

Source: https://themsaid.com/laravel-query-conditions-20160425 I hope this helps!

Upvotes: 1

Pavel
Pavel

Reputation: 936

$query = Student::where('last_name',$last_name);
if ($request->has('first_name')) {
    $query->where('first_name',$first_name);
}
if ($request->has('order_by')) {
    $query->orderBy($order_by);
}

$students = $query->paginate(10);

Upvotes: 0

u_mulder
u_mulder

Reputation: 54831

Same approach:

$first_name = $request->input('first_name');
$last_name = $request->input('last_name');
$order_by = $request->input('$order_by');

$query = Student::orderBy($order_by);
if ($first_name) {
    $query->where('first_name',$first_name);
}

if ($last_name) {
    $query->where('last_name',$last_name);
}

$students = $query->paginate(10);
return $students;

Upvotes: 0

Sagar Gautam
Sagar Gautam

Reputation: 9389

You can do it like,

$first_name = $request->input('first_name');
$last_name = $request->input('last_name');
$order_by = $request->input('order_by');

$query = DB::table('student');

if($request->first_name){
    $query->where('first_name',$first_name)
}

if($request->last_name){
    $query->where('last_name',$last_name);
}

if($request->order_by){
    $query->orderBy($order_by);
}

$students = $query->paginate(10);

Hope you understand.

Upvotes: 0

Naveen K
Naveen K

Reputation: 889

you can use like, 

$students = Student::latest();

if (isset($request->input('order_by'))) {
    $students->orderBy($order_by)
}
if (isset($request->input('first_name'))) {
          $students->where('first_name',$first_name);
}
if (isset($request->input('last_name'))) {
          $students->where('last_name',$last_name);
}
$students->paginate(10);

Upvotes: 6

Related Questions