X-Git-Url: http://p2p-next.cs.pub.ro/gitweb/?a=blobdiff_plain;f=application%2Fmodels%2Fvideos_model.php;h=fccb7a043e686324ee2f065eb83e14d22a0c4e3c;hb=69c30ca56fdc6a4eff236902040cc04ec6eaccf2;hp=0d9fe7bf8585a4cd926819f633b41037c0700ff5;hpb=e788f88e8475e9c87eef355c71abed3c329c4c0b;p=living-lab-site.git diff --git a/application/models/videos_model.php b/application/models/videos_model.php index 0d9fe7b..fccb7a0 100644 --- a/application/models/videos_model.php +++ b/application/models/videos_model.php @@ -22,10 +22,18 @@ class Videos_model extends CI_Model { * Retrieves information about a set of videos which are going to be * displayed in the catalog. * - * TODO: filter, limit, ordering parameters * @param int $category_id DB category ID * @param int $offset * @param int $count + * @param string $ordering control videos ording by these + * possibilities: + * * @return array a list of videos, each one being an assoc array with: * */ - public function get_videos_summary($category_id, $offset, $count) + public function get_videos_summary($category_id, $offset, $count, $ordering = 'hottest') { $this->load->helper('text'); + // Ordering + switch ($ordering) + { + case 'hottest': + $order_statement = "ORDER BY date DESC, score DESC, RAND()"; + break; + case 'newest': + $order_statement = "ORDER BY date DESC"; + break; + case 'alphabetically': + $order_statement = "ORDER BY title"; + break; + + default: + $order_statement = ""; + } + $query = $this->db->query( - 'SELECT id, name, title, duration, user_id, views, thumbs_count, - default_thumb + "SELECT id, name, title, duration, user_id, views, thumbs_count, + default_thumb, (views + likes - dislikes) AS score FROM `videos` WHERE category_id = ? - ORDER BY name - LIMIT ?, ?', // TODO summary order + $order_statement + LIMIT ?, ?", array(intval($category_id), $offset, $count)); if ($query->num_rows() > 0) @@ -83,7 +108,7 @@ class Videos_model extends CI_Model { $category_id); if ($query->num_rows() > 0) - return $row = $query->row()->count; + return $query->row()->count; // Error return NULL; @@ -199,25 +224,87 @@ class Videos_model extends CI_Model { return $thumbs; } - public function search_videos($str_search) + /** + * Searches videos in DB based on a search query string and returns an + * associative array of results. + * If count is zero the function only return the number of results. + * @param string $search_query + * @param int $offset + * @param int $count + * @param int $category_id if NULL, all categories are searched + * @return array an associative array with the same keys as that from + * get_videos_summary's result, but with two additional keys: + * description and date. + */ + public function search_videos($search_query, $offset = 0, $count = 0, + $category_id = NULL) { - // TODO offset, count for search - $offset = 0; - $count = 100; + // very short queries + if (strlen($search_query) < 4) + { + $search_cond = "(title LIKE '%$search_query%' + OR description LIKE '%$search_query%' + OR tags LIKE '%$search_query%')"; + $relevance = "( 0.5 * (title LIKE '%git%') + + 0.2 * (description LIKE '%git%') + + 0.3 * (tags LIKE '%git%') ) AS relevance"; + } + // natural language mode + else if (! $this->is_advanced_search_query($search_query)) + { + $search_cond = "MATCH (title, description, tags) + AGAINST ('$search_query')"; + $relevance = "$search_cond AS relevance"; + } + // boolean mode + else + { + $against = "AGAINST ('$search_query' IN BOOLEAN MODE)"; + $search_cond = "MATCH (title, description, tags) + $against"; + $relevance = "( (0.5 * (MATCH(title) $against)) + + (0.3 * (MATCH(tags) $against)) + + (0.2 * (MATCH(description) $against)) ) AS relevance"; + } + + if ($count === 0) + { + $selected_columns = "COUNT(*) count"; + $order = ""; + $limit = ""; + } + else + { + // TODO select data, description if details are needed + $selected_columns = "id, name, title, duration, user_id, views, + thumbs_count, default_thumb, + (views + likes - dislikes) AS score, + $relevance"; + $order = "ORDER BY relevance DESC, score DESC"; + $limit = "LIMIT $offset, $count"; + } + + if ($category_id !== NULL) + $category_cond = "category_id = '$category_id' AND "; + else + $category_cond = ""; - $str_search = trim($str_search); + $search_query = trim($search_query); - $query = $this->db->query( - "SELECT id, name, title, duration, user_id, views, thumbs_count, - default_thumb + $str_query = "SELECT $selected_columns FROM `videos` - WHERE MATCH (title, description, tags) AGAINST (?) - ORDER BY name - LIMIT ?, ?", - array($str_search, $offset, $count)); + WHERE $category_cond $search_cond + $order + $limit"; + $query = $this->db->query($str_query); if ($query->num_rows() > 0) - $videos = $query->result_array(); + { + if ($count === 0) + return $query->row()->count; + else + $videos = $query->result_array(); + } else return NULL; @@ -243,6 +330,34 @@ class Videos_model extends CI_Model { return $videos; } + + public function decode_search_query($search_query) + { + $search_query = urldecode($search_query); + + $search_query = str_replace('_AST_', '*', $search_query); + $search_query = str_replace('_AND_', '+', $search_query); + $search_query = str_replace('_GT_', '>', $search_query); + $search_query = str_replace('_LT_', '<', $search_query); + $search_query = str_replace('_PO_', '(', $search_query); + $search_query = str_replace('_PC_', ')', $search_query); + $search_query = str_replace('_LOW_', '~', $search_query); + $search_query = str_replace('_QUO_', '"', $search_query); + + return $search_query; + } + + /** + * Return TRUE if it contains any special caracter from an advanced search + * query. + * @param string $search_query + * @return boolean + */ + public function is_advanced_search_query($search_query) + { + return (preg_match('/\*|\+|\-|>|\<|\(|\)|~|"/', $search_query) == 0 + ? FALSE : TRUE); + } } /* End of file videos_model.php */