search.php

<?php
class Search_model extends CI_Model {

 function search($query,$lang,$query_type,$offset=0,$limit=10){
   $terms=$this->get_search_terms($query);
   $terms_count=count($terms);
   $select_rank="";
   $select=$this->get_select($query_type);
   $where="";
   $join=$this->get_join();
   $sort=$this->get_sort($query_type,'rank');
   $limit=$this->get_limit($query_type,$offset,$limit);
   if($terms_count>1){
     $select_rank=$this->rank_term($query).' + '.$this->get_select_rank($terms);
     $where=$this->where_term($query).' OR '.$this->get_where_rank($terms);
   }else{
     $select_rank=$this->rank_term($query);
     $where=$this->where_term($query);
   }
   $Search_query="SELECT $select , ( $select_rank )  AS rank FROM `projects` $join
   WHERE `projects`.`lang` = '$lang' AND `first_image` = 1 AND ( $where ) $sort $limit ";
   $projects=$this->db->query($Search_query);
   return ($query_type=='count')?$projects->result()[0]->num_rows:$projects->result();
 }

 function search_by_project_details($city_id,$area_id,$payment_id,
                                    $project_type_id,$room,$salon,
                                    $min_price,$max_price,$min_area,
                                    $max_area,$lang,$query_type,$offset=0,$limit=10){


    $select=$this->get_select($query_type);
    $where=$this->get_city_query($city_id).
    $this->get_payment_query($payment_id).
    $this->get_area_query($area_id).
    $this->get_project_type_query($project_type_id).
    $this->get_rooms_query($room).
    $this->get_salon_query($salon).
    $this->get_price_query($min_price,$max_price).
    $this->get_project_area_query($min_area,$max_area);
    $join=$this->get_join();
    $sort=$this->get_sort($query_type,' `projects`.`projectId` ');
    $limit=$this->get_limit($query_type,$offset,$limit);
    $query="SELECT $select  FROM `projects` $join WHERE `projects`.`lang` = '$lang' AND `first_image` = 1  $where $sort $limit ";
    $projects=$this->db->query($query);
    return ($query_type=='count')?$projects->result()[0]->num_rows:$projects->result();

 }
 private function get_select($query_type){
   if($query_type=='count'){
       return " COUNT(projects.projectId) AS num_rows ";
   }else if($query_type=='data'){
     return " `project_types`.`en_type`, `projects`.`projectId`,`price`,
               `project_types`.`ar_type`,`projects`.`bathroom_count`,
                `projects`.`room_count`,`projects`.`area`, `projects`.`name`,
                `projects`.`sammery`, `images`.`fileName` ";
    }
 }
 private function get_limit($query_type,$offset,$limit){
   if($query_type=='data')  return " LIMIT $offset,$limit ";
   return "";
 }
 private function get_sort($query_type,$sort_by)
 {
   if($query_type=='data')return " ORDER BY $sort_by DESC ";
   return "";
 }
 private function get_join(){
   return  " INNER JOIN `projects_images` ON `projects_images`.`projectId` = `projects`.`projectId`
     INNER JOIN `images` ON `images`.`imageId` = `projects_images`.`imageId`
     INNER JOIN `areas` ON `areas`.`area_id` = `projects`.`area_id`
     INNER JOIN `project_types` ON `projects`.`project_type_id` = `project_types`.`project_type_id` ";
 }
 private function get_rooms_query($room){
     $where="";
     if ($room!=0) {
         $where=" AND `projects`.`room_count` = $room ";
     }
    return $where;
  }
 private function get_city_query($city_id){
   $where="";
   if($city_id != 0){
      $where.=" AND `areas`.`city_id` = $city_id ";
   }
   return $where;
 }
 private function get_area_query($area_id){
   $where="";
   if($area_id != 0){
      $where.=" AND `projects`.`area_id` = $area_id ";
   }
   return $where;
 }
 private function get_payment_query($payment_id){
  $where="";
   if($payment_id != 0){
       $where.=" AND `projects`.`payment_option_id` = $payment_id ";
   }
   return $where;
 }
 private function get_project_type_query($project_type_id){
   $where="";
   if($project_type_id != 0){
     $where.=" AND `projects`.`project_type_id` = $project_type_id ";
   }
   return $where;
 }
 private function get_salon_query($salon){
   $where="";
   if($salon != 0){
     $where=" AND `projects`.`salon_count` = $salon ";
   }
   return $where;
 }
 private function get_price_query($min_price,$max_price){
   if((($min_price==0)&&($max_price==0))||($max_price<=$min_price)){
     return "";
   }else if (($min_price==0)&&($max_price>0)){
     return " AND `projects`.`price` <= $max_price ";
   }elseif (($min_price>0)&&($max_price==0)){
     return " AND `projects`.`price` >= $min_price ";
   }elseif ((($min_price>0)&&($max_price>0))&&($max_price>$min_price)) {
     return " AND `projects`.`price` >= $min_price AND `projects`.`price` <= $max_price ";
   }
 }
 private function get_project_area_query($min_area,$max_area){
   if((($min_area==0)&&($max_area==0))||($max_area<=$min_area)){
     return "";
   }else if (($min_area==0)&&($max_area>0)){
     return " AND `projects`.`area` <= $max_area ";
   }elseif (($min_area>0)&&($max_area==0)){
     return " AND `projects`.`area` >= $min_area ";
   }elseif ((($min_area>0)&&($max_area>0))&&($max_area>$min_area)) {
     return " AND `projects`.`area` >= $min_area AND `projects`.`area` <= $max_area ";
   }
 }

 private function get_select_rank($query_array){
   $select ="";
   $qry_arr_count=count($query_array)-1;
   foreach ($query_array as $index => $term) {
     if($index < $qry_arr_count){
       $select.=$this->rank_term($term).' + ';
     }else{
       $select.=$this->rank_term($term);
     }
   }
   return $select;
 }
 private function get_where_rank($query_array){
   $where ="";
   $qry_arr_count=count($query_array)-1;
   foreach ($query_array as $index => $term) {
      if($index < $qry_arr_count){
        $where .=$this->where_term($term).' OR ';
      }else {
          $where .=$this->where_term($term);
      }
   }
   return $where;
 }
 private function rank_term($term){
   return " ( CASE WHEN `projects`.`name` LIKE '%".$term."%' THEN 2 ELSE 0 END ) + ( CASE WHEN `projects`.`description` LIKE '%".$term."%' THEN 1 ELSE 0 END )";
 }
 private function where_term($term){
   return " `projects`.`name` LIKE '%".$term."%' OR `projects`.`description` LIKE '%".$term."%'";
 }
 private function get_search_terms($query){
  return  explode(" ",$query);
 }


}