gistfile1.php

$schools = DB::table('schools')
            ->select(DB::raw('
                    schools.name,
                    i.id as institute_school_id,
                    i.id as institute_id,
                    st.name as school_type,
                    l1.name as location_1_name,
                    l2.name as location_2_name
                  '))
            ->join('institutes as i', 'i.id', '=', 'schools.institute_id')
            ->join('schools_types as st', 'st.id', '=', 'schools.type_id')
            ->leftJoin('institutes_locations', function($join) {
                $join->on('schools.id', '=', 'institutes_locations.related_row_id');
                $join->on('institutes_locations.related_table', '=', DB::raw("'schools'"));
            })
            ->leftJoin('locations as l1', 'l1.id', '=', 'institutes_locations.location_level_1')
            ->leftJoin('locations as l2', 'l2.id', '=', 'institutes_locations.location_level_2')
            ->whereNull('schools.deleted_at')
            ->where(function($query) use ($institute_sector_id) {
                if($institute_sector_id && $institute_sector_id != -1) {
                    $query->where('i.sector_id', $institute_sector_id);
                }
            })
            ->where(function($query) use ($school_type_id) {
                if($school_type_id && $school_type_id != -1) {
                    $query->where('schools.type_id', $school_type_id);
                }
            })
            ->where(function($query) use ($school_franchise_id) {
                if($school_franchise_id && $school_franchise_id != -1) {
                    $query->where('schools.franchise_id', $school_franchise_id);
                }
            })
            ->where(function($query) use ($school_loc_level_1_id) {
                if($school_loc_level_1_id && $school_loc_level_1_id != -1) {
                    $query->where('institutes_locations.location_level_1', $school_loc_level_1_id);
                }
            })
            ->where(function($query) use ($school_loc_level_2_id) {
                if($school_loc_level_2_id && $school_loc_level_2_id != -1) {
                    $query->where('institutes_locations.location_level_2', $school_loc_level_2_id);
                }
            })
            ->where(function($query) use ($value)
            {
                if(Input::get('tab') === 'name') {
                    $query->where('schools.id' , 'like' , '%'.$value.'%')
                        ->orwhere('schools.name' , 'like' , '%'.$value.'%');
                }else {
                    $query->where('institutes_locations.address' , 'like' , '%'.$value.'%');
                    $query->orWhere('l1.name' , 'like' , '%'.$value.'%');
                    $query->orwhere('l2.name' , 'like' , '%'.$value.'%');
                }


            })
            ->orderBy($order_by, $order_type)
            ->get();