laravel advanced search

Question

I want to make an advanced search in which the user has optional parameters to search,am joining data from three tables as follows

$qry="SELECT rooms.*, salereservation.*, customers.*
                FROM rooms
                    JOIN salereservation
                        ON salereservation.room_id = rooms.room_id
                    JOIN customers
                        ON customers.id = salereservation.customer_id
                WHERE salereservation.sale_status=1 AND ";

i am appending to the query string the optional parameters as follows

if($fname!=''){
        $qry.="fname LIKE %fname% AND ";
    }
    if($lname!=''){
        $qry.="lname LIKE %:lname% AND ";
    }
    if($time_in!=''){
        $qry.="start_datetime LIKE %time_in% AND ";
    }
    if($time_out!=''){
        $qry.="end_datetime LIKE %time_out% AND ";
    }
    if($phone!=''){
        $qry.="phone LIKE %phone% AND ";
    }
    if($room_no!=''){
        $qry.="room_no LIKE %room_no%";
    }

my problem is how to turn the code into laravel query builder

Answer 1

I didn't try this code but its should work under laravel 4.2.

$query = DB::table('rooms')
        ->join("salereservation", "salereservation.room_id", "=", "rooms.room_id")
        ->join("customers", "customers.id", "=", "salereservation.customer_id")
        ->where("salereservation.sale_status",'=',1)
        ->select('rooms.*', 'salereservation.*', 'customers.*');

if($fname!=''){
    $query->where("fname",'like',"%$fname%");
}

if($lname!=''){
    $query->where("lname",'like',"%$lname%");
}
if($time_in!=''){
   $query->where("start_datetime",'like',"%$time_in%");
}
if($time_out!=''){
    $query->where("end_datetime",'like',"%$time_out%");
}
if($phone!=''){
    $query->where("phone",'like',"%$phone%");
}
if($room_no!=''){
  $query->where("room_no",'like',"%$room_no%");
}
$data = $query->get(); //finally get the result

Update:

For query verification you can print your query using:

 $queries = DB::getQueryLog();
 $last_query = end($queries);
 dd($last_query);

And verify if your query different from your desired query. If something went to different we can upgrade our query structure according to them.and also can you update with your latest query generated from laravel methods.

But if you still face some difficulties to understand my point of view. let me know.

Answer 2

You have to setup your DB like this:

rooms:
 - id
 - room_no

reservations:
 - customer_id
 - start_datetime
 - end_datatime
 - room_id

customers:
 - lname
 - fname
 - phone

Then setup the propers relationships in your models

//Reservation model
class Reservation extends \Eloquent {

    public function room()
    {
        return $this->belongsTo('Room', 'room_id');
    }

    public function customer()
    {
        return $this->belongsTo('Customer', 'customer_id');
    }
}

Create a query scope like the following :

// Reservation model
// $terms is an array which pairs column_name to the querying value
public function scopeSearch($query, $terms)
{
    if ( ! empty($terms['room_no']))
    {
        $query->has('rooms.room_no', $terms['room_no']);
    }

    $customer_cols = ['lname', 'fname', 'phone'];
    $reservations_cols = ['start_datetime', 'end_datetime'];
    foreach ($terms as $key => $value)
    {
        if (in_array($key, $customer_cols))
        {
            $query->whereHas('customer', function($subquery) use ($key, $value)
            {
                $subquery->where($key, 'like', '%'.$value.'%');
            });
        }
        if (in_array($key, $reservation_cols))
        {
            $query->where($key, $value);
        }
    }

}

Now you can invoke your query scope by doing the following:

$reservations = Reservation:search(array('fname' => 'value', 'lname' => 'value2', ...));

And accessing results by:

foreach ($reservations as $res)
{
    $res->room->number;
    $res->customer->name;
}