/lib/services/Search/Services_Search_QueryParser.php
PHP | 986 lines | 505 code | 106 blank | 375 comment | 121 complexity | 965240588f0d0e8f3291645c23172ee6 MD5 | raw file
Possible License(s): BSD-3-Clause, GPL-2.0, MPL-2.0-no-copyleft-exception, 0BSD, Apache-2.0
- <?php
- class Services_Search_QueryParser {
- /*
- * We need a databse class to be able to properly escape
- * data in queries
- */
- private $_dbEng = null;
- /*
- * constructor
- */
- function __construct(dbeng_abs $db) {
- $this->_dbEng = $db;
- } # ctor
- /*
- * When passed an array with categories, this array is expanded \
- * to contain the fully qualified categories and subcategories.
- */
- public function prepareCategorySelection($dynaList) {
- SpotTiming::start(__FUNCTION__);
- $strongNotList = array();
- $categoryList = array();
-
- /*
- * The Dynatree jquery widget which we use, retrieves its data from ?page=catsjson,
- * for each node in the dynatree we provide a key. The tree could be seen as follows,
- * with the unique key within parenthesis.
- *
- * - Image (cat0)
- * +-- Film (cat0_z0)
- * +--- Format (cat0_z0_a)
- * +----- DivX (cat0_z0_a0)
- * +----- WMV (cat0_z0_a1)
- * +-- Series (cat0_z1)
- * +--- Format (cat0_z1_a)
- *+----- DivX (cat0_z1_a0)
- * +----- WMV (cat0_z1_a1)
- * +--- Source (cat0_z1_b)
- * - Applications (cat3)
- * +-- Format (cat1_zz_a / cat1_a)
- *
- * Basially, you have a headcategory number, then you have a categorytype, then a subcategorytype (a,b,c,d, ...)
- * then the subcategorynumber follows.
- *
- * When you want to select, in above example, a Film in DivX, the keyvalue is simply cat0_z0_a0.
- * However, when you want to select the whole of 'Image', keyvalue 'cat0' would suffice.
- *
- * If users would select categories manually (for example a manually constructed search), it would
- * be more convienent for them to be able to provide shorthands, allowing one to select common category
- * groups more easily. Spotweb wil expand those category selection items to contain the full selection.
- *
- * The following shorthands are allowed:
- *
- * cat0 - Will be expanded to all subcategoies of category 0
- * cat0_z0_a - Will be expanded to subcategory A of category 0, but the type must be z0
- * !cat0_z0_a1 - Will remove cat0_z0_a1 from the list (order in the list is important)
- * ~cat0_z0_a1 - 'Forbids' cat0_z0_a1 to be in the list (a NOT will be applied to it)
- * cat0_a - Select everything from subcategory A in category 0 (all z-types)
- *
- */
- $newTreeQuery = '';
-
- /*
- * Process each item in the list, and expand it where necessary
- */
- $dynaListCount = count($dynaList);
- for($i = 0; $i < $dynaListCount; $i++) {
- /*
- * The given category can be one of the following four types:
- * cat1_z0_a ==> Everything of cat1, type z0, and then everything of subcategory a
- * cat1_z0 ==> Everything of cat1, type z0
- * cat1_a ==> Everything of cat1 which is of 'subcategory a'
- * cat1 ==> Select the whole of cat1
- *
- */
- if ((strlen($dynaList[$i]) > 0) && ($dynaList[$i][0] == 'c')) {
- $hCat = (int) substr($dynaList[$i], 3, 1);
-
- # Was a type + global subcategory selected? (cat1_z0_a)
- if (strlen($dynaList[$i]) == 9) {
- $typeSelected = substr($dynaList[$i], 5, 2);
- $subCatSelected = substr($dynaList[$i], 8);
- # Was only as category selected (cat1)
- } elseif (strlen($dynaList[$i]) == 4) {
- $typeSelected = '*';
- $subCatSelected = '*';
- # Was a category and type selected (cat1_z0)
- } elseif ((strlen($dynaList[$i]) == 7) && ($dynaList[$i][5] === 'z')) {
- $typeSelected = substr($dynaList[$i], 5, 2);
- $subCatSelected = '*';
- # Was a category and subcateory specified, old stype? (cat1_a3)
- } elseif (((strlen($dynaList[$i]) == 7) || (strlen($dynaList[$i]) == 8)) && ($dynaList[$i][5] !== 'z')) {
- # Convert the old style to explicit categories (cat1_z0_a3, cat1_z1_a3, cat1_z2_a3, ... )
- foreach(SpotCategories::$_categories[$hCat]['z'] as $typeKey => $typeValue) {
- $newTreeQuery .= "," . substr($dynaList[$i], 0, 4) . '_z' . $typeKey . '_' . substr($dynaList[$i], 5);
- } # foreach
-
- $typeSelected = '';
- $subCatSelected = '';
- # was a subcategory specified? (cat1_a)
- } elseif (strlen($dynaList[$i]) == 6) {
- $typeSelected = '*';
- $subCatSelected = substr($dynaList[$i], 5, 1);
- } else {
- $newTreeQuery .= "," . $dynaList[$i];
-
- $typeSelected = '';
- $subCatSelected = '';
- } # else
- /*
- * Createa a string containing all subcategories.
- *
- * We always loop through all subcategories so we can reuse this bit of code
- * both for complete category selection as subcategory selection.
- */
- $tmpStr = '';
- foreach(SpotCategories::$_categories[$hCat] as $subCat => $subcatValues) {
- /*
- * There are four possible cases:
- *
- * $subcatSelected contains an empty string, it matches to nothing.
- * $subcatSelected contains an asterisk, it matches all subcategories.
- * $typeSelected contains an empty string, it matches nothing.
- * $typeSelected contains an asterisk, it matches all types.
- */
- if ($subCatSelected == '*') {
- foreach(SpotCategories::$_categories[$hCat]['z'] as $typeKey => $typeValue) {
- $typeKey = 'z' . $typeKey;
- if (($typeKey == $typeSelected) || ($typeSelected == '*')) {
- $tmpStr .= ',sub' . $hCat . '_' . $typeKey;
- } # if
- } # foreach
- } elseif (($subCat == $subCatSelected) && ($subCat !== 'z')) {
- foreach(SpotCategories::$_categories[$hCat]['z'] as $typeKey => $typeValue) {
- $typeKey = 'z' . $typeKey;
- if (($typeKey == $typeSelected) || ($typeSelected == '*')) {
-
- foreach(SpotCategories::$_categories[$hCat][$subCat] as $x => $y) {
- if (in_array($typeKey, $y[2])) {
- $tmpStr .= ",cat" . $hCat . "_" . $typeKey . '_' . $subCat . $x;
- } # if
- } # foreach
- } # if
- } # foreach
- } # if
- } # foreach
- $newTreeQuery .= $tmpStr;
- } elseif (substr($dynaList[$i], 0, 1) == '!') {
- # For a not, we just remove / exclude it from the list.
- $newTreeQuery = str_replace(',' . substr($dynaList[$i], 1), "", $newTreeQuery);
- } elseif (substr($dynaList[$i], 0, 1) == '~') {
- /*
- * For a STRONG NOT, we cannot remove it from the list because want to explicitly
- * remove those results from the query and we have to pass it in other URL's and the
- * likes
- */
- $newTreeQuery .= "," . $dynaList[$i];
-
- # and add it to the strongNotList array for usage later on
- $strongNotTmp = explode("_", $dynaList[$i], 2);
- /* To deny a whole category, we have to take an other shortcut */
- if (count($strongNotTmp) == 1) {
- $strongNotList[(int) substr($strongNotTmp[0], 4)][] = '';
- } else {
- $strongNotList[(int) substr($strongNotTmp[0], 4)][] = $strongNotTmp[1];
- } # else
- } else {
- $newTreeQuery .= "," . $dynaList[$i];
- } # else
- } # for
- if ((!empty($newTreeQuery)) && ($newTreeQuery[0] == ",")) {
- $newTreeQuery = substr($newTreeQuery, 1);
- } # if
- /*
- *
- * Starting from here, we have a prepared list - meaning, a list with all
- * categories fully expanded.
- *
- * We now translate this list to an nested list of elements which is easier
- * to convert to SQL. The format of the array is fairly typical:
- *
- * list['cat']
- * [cat] -> Head category, eg: 0 for Images
- * [type] -> Type, eg: 0 for z0
- * [subcattype] -> Subcategory type, eg: a
- * = value -> eg 1 for in total cat0_z0_a1
- */
- $dynaList = explode(',', $newTreeQuery);
- foreach($dynaList as $val) {
- if (substr($val, 0, 3) == 'cat') {
- # 0 element is headcategory
- # 1st element is type
- # 2ndelement is category
- $val = explode('_', (substr($val, 3) . '_'));
- $catVal = $val[0];
- $typeVal = $val[1];
- $subCatIdx = substr($val[2], 0, 1);
- $subCatVal = substr($val[2], 1);
- if (count($val) >= 4) {
- $categoryList['cat'][$catVal][$typeVal][$subCatIdx][] = $subCatVal;
- } # if
- } elseif (substr($val, 0, 3) == 'sub') {
- # 0 element is headcategory
- # 1st element is type
- $val = explode('_', (substr($val, 3) . '_'));
- $catVal = $val[0];
- $typeVal = $val[1];
- # Create the z-category in the categorylist
- if (count($val) == 3) {
- if (!isset($categoryList['cat'][$catVal][$typeVal])) {
- $categoryList['cat'][$catVal][$typeVal] = array();
- } # if
- } # if
- } # elseif
- } # foreach
- SpotTiming::stop(__FUNCTION__, array($categoryList, $strongNotList));
-
- return array($categoryList, $strongNotList);
- } # prepareCategorySelection
- /*
- * Converts a list of categories to an SQL filter
- */
- private function categoryListToSql($categoryList) {
- SpotTiming::start(__FUNCTION__);
- $categorySql = array();
- # Make sure we were passed a valid filter
- if ((!isset($categoryList['cat'])) || (!is_array($categoryList['cat']))) {
- return $categorySql;
- } # if
- /*
- * We have to translate the list of sub- and headcategories to an SQL WHERE statement in
- * multiple steps, where the 'category' is the basis for our filter.
- *
- * A testste for filters could be the following:
- * cat0_z0_a9,cat0_z1_a9,cat0_z3_a9, ==> HD beeld
- * cat0_z0_a9,cat0_z0_b3,cat0_z0_c1,cat0_z0_c2,cat0_z0_c6,cat0_z0_c11,~cat0_z1,~cat0_z2,~cat0_z3 ==> Nederlands ondertitelde films
- * cat0_a9 ==> Alles in x264HD
- * cat1_z0,cat1_z1,cat1_z2,cat1_z3 ==> Alle muziek, maar soms heeft muziek geen genre ingevuld!
- *
- * The category list structure is:
- *
- * array(1) {
- * ["cat"]=>
- * array(1) {
- * [1]=> <== Headcategory number (cat1)
- * array(4) {
- * ["z0"]=> <== Type (subcatz) number (cat1_z0)
- * array(4) {
- * ["a"]=> <== Subcategorylist (cat1_z0_a)
- * array(9) {
- * [0]=>
- * string(1) "0" <== Selected subcategory (so: cat1_z0_a0)
- * }
- * ["b"]=>
- * array(7) {
- * [0]=>
- * string(1) "0"
- *
- */
- foreach($categoryList['cat'] as $catid => $cat) {
- /*
- * Each category we have, we try to procss all subcategories
- * and convert it to a filter
- */
- if ((is_array($cat)) && (!empty($cat))) {
- foreach($cat as $type => $typeValues) {
- $catid = (int) $catid;
- $tmpStr = "((s.category = " . (int) $catid . ")";
-
- # dont filter the zz types (games/apps)
- if ($type[1] !== 'z') {
- $tmpStr .= " AND (s.subcatz = '" . $type . "|')";
- } # if
- $subcatItems = array();
- foreach($typeValues as $subcat => $subcatItem) {
- $subcatValues = array();
-
- foreach($subcatItem as $subcatValue) {
- /*
- * A spot can only contain one 'A' and 'Z' subcategory value, so we
- * can perform an equality filter instead of a LIKE
- */
- if ($subcat == 'a') {
- $subcatValues[] = "(s.subcata = '" . $subcat . $subcatValue . "|') ";
- } elseif (in_array($subcat, array('b', 'c', 'd'))) {
- $subcatValues[] = "(s.subcat" . $subcat . " LIKE '%" . $subcat . $subcatValue . "|%') ";
- } # if
- } # foreach
- /*
- *
- * We add all subactegories within the same subcategory together (for example all
- * formats of a movie) with an OR. This means you can pick between DivX and WMV as
- * a format
- *
- */
- if (count($subcatValues) > 0) {
- $subcatItems[] = " (" . join(" OR ", $subcatValues) . ") ";
- } # if
- } # foreach subcat
- /*
- * After this, same headcategory and type (Image + Movie, Sound) filters for
- * subcategories are merged together with an AND.
- *
- * This results in a filter like:
- *
- * (((category = 0) AND ( ((subcata = 'a0|') ) AND ((subcatd LIKE '%d0|%')
- *
- * This makes sure you are able to pick multiple genres within the same category/subcategory,
- * but you will not get unpredictable results by getting an 'Action' game for Linux when you
- * accidentally asked for either 'Action' or 'Romance'.
- */
- if (count($subcatItems) > 0) {
- $tmpStr .= " AND (" . join(" AND ", $subcatItems) . ") ";
- } # if
-
- # Finish of the query
- $tmpStr .= ")";
- $categorySql[] = $tmpStr;
- } # foreach type
- } # if
- } # foreach
- SpotTiming::stop(__FUNCTION__, array($categorySql));
- return $categorySql;
- } # categoryListToSql
-
- /*
- * Converts a list of "strong nots" to the corresponding
- * SQL statements
- */
- private function strongNotListToSql($strongNotList) {
- SpotTiming::start(__FUNCTION__);
- $strongNotSql = array();
-
- if (empty($strongNotList)) {
- return array();
- } # if
- /*
- * Each STRONG NOT is to be converted individually to a NOT
- * SQL WHERE filter
- */
- foreach(array_keys($strongNotList) as $strongNotCat) {
- foreach($strongNotList[$strongNotCat] as $strongNotSubcat) {
- /*
- * When the strongnot is for a whole category (eg: cat0), we can
- * make the NOT even simpler
- */
- if (empty($strongNotSubcat)) {
- $strongNotSql[] = "(NOT (s.Category = " . (int) $strongNotCat . "))";
- } else {
- $subcats = explode('_', $strongNotSubcat);
- /*
- * A spot can only contain one 'A' and 'Z' subcategory value, so we
- * can perform an equality filter instead of a LIKE
- */
- if (count($subcats) == 1) {
- if (in_array($subcats[0][0], array('a', 'z'))) {
- $strongNotSql[] = "(NOT ((s.Category = " . (int) $strongNotCat . ") AND (s.subcat" . $subcats[0][0] . " = '" . $this->_dbEng->safe($subcats[0]) . "|')))";
- } elseif (in_array($subcats[0][0], array('b', 'c', 'd'))) {
- $strongNotSql[] = "(NOT ((s.Category = " . (int) $strongNotCat . ") AND (s.subcat" . $subcats[0][0] . " LIKE '%" . $this->_dbEng->safe($subcats[0]) . "|%')))";
- } # if
- } elseif (count($subcats) == 2) {
- if (in_array($subcats[1][0], array('a', 'z'))) {
- $strongNotSql[] = "(NOT ((s.Category = " . (int) $strongNotCat . ") AND (s.subcatz = '" . $subcats[0] . "|') AND (subcat" . $subcats[1][0] . " = '" . $this->_dbEng->safe($subcats[1]) . "|')))";
- } elseif (in_array($subcats[1][0], array('b', 'c', 'd'))) {
- $strongNotSql[] = "(NOT ((s.Category = " . (int) $strongNotCat . ") AND (s.subcatz = '" . $subcats[0] . "|') AND (subcat" . $subcats[1][0] . " LIKE '%" . $this->_dbEng->safe($subcats[1]) . "|%')))";
- } # if
- } # else
- } # else not whole subcat
- } # foreach
- } # forEach
- SpotTiming::stop(__FUNCTION__, array($strongNotSql));
- return $strongNotSql;
- } # strongNotListToSql
- /*
- * Prepareert de filter values naar een altijd juist formaat
- */
- private function prepareFilterValues($search) {
- SpotTiming::start(__FUNCTION__);
- $filterValueList = array();
- /*
- * We have drie kinds of filters:
- * - Old type where you have a search[type] with the values stamp,title,tag and an search[text]
- * containing the value to search for. This limits you to a maximum of one filter which is not
- * sufficient.
- *
- * We automatically convert these kind of searches to the new type.
- *
- *
- * - New type where there is a search[value] array, which contain values in the following shape:
- * type:operator:value.
- * For example, tag:=:spotweb. A shorthand is also available when the operator is left out (eg: tag:spotweb),
- * we assume the EQ operator was intended.
- *
- * - Special kind of lists, there are a few values with a special meaning:
- * New:0 (new spots)
- * Downloaded:0 (spots which are downloaded by this account)
- * Watch:0 (spots on the watchlist of this account)
- * Seen:0 (spots which have already been opened by this account)
- * MyPostedSpots:0 (spots posted by this account)
- * WhitelistedSpotters:0 (spots posted by a whitelisted spotter)
- *
- */
- if (isset($search['type'])) {
- if (!isset($search['text'])) {
- $search['text'] = '';
- } # if
-
- /*
- * We can be provided a set of old and new filters, we don't want to
- * overwrite the regular filters, so we take care to append to them
- */
- if ((!isset($search['value'])) || (!is_array($search['value']))) {
- $search['value'] = array();
- } # if
- $search['value'][] = $search['type'] . ':=:' . $search['text'];
- unset($search['type']);
- } # if
- # Make sure that we always have something to iterate through
- if ((!isset($search['value'])) || (!is_array($search['value']))) {
- $search['value'] = array();
- } # if
- # Now we transform the new query (field:operator:value pair) to an exploded array for easier iteration
- foreach($search['value'] as $value) {
- if (!empty($value)) {
- $tmpFilter = explode(':', $value);
- # Default to an '=' operator when none is given
- if (count($tmpFilter) < 3) {
- $tmpFilter = array($tmpFilter[0],
- '=',
- $tmpFilter[1]);
- } # if
-
- /*
- * Create the actual filter, we add the array_slice part to
- * allow for an ':' in the actual search value.
- */
- $filterValueTemp = Array('fieldname' => $tmpFilter[0],
- 'operator' => $tmpFilter[1],
- 'value' => join(":", array_slice($tmpFilter, 2)));
-
- /*
- * and create the actual filter list. Before appending it,
- * we want to make sure no identical filter is already
- * in the list, because this might make MySQL very slow.
- */
- if (!in_array($filterValueTemp, $filterValueList)) {
- $filterValueList[] = $filterValueTemp;
- } # if
- } # if
- } # for
-
- SpotTiming::stop(__FUNCTION__, array($filterValueList));
- return $filterValueList;
- } # prepareFilterValues
- /*
- * Converts one or multiple userprovided txt filters to SQL statements
- */
- private function filterValuesToSql($filterValueList, $currentSession) {
- SpotTiming::start(__FUNCTION__);
- # Add a list of possible text searches
- $filterValueSql = array('OR' => array(), 'AND' => array());
- $additionalFields = array();
- $additionalTables = array();
- $additionalJoins = array();
-
- $sortFields = array();
- $textSearchFields = array();
-
- # Lookp table from 'friendly' name to fully qualified one
- $filterFieldMapping = array('filesize' => 's.filesize',
- 'date' => 's.stamp',
- 'stamp' => 's.stamp',
- 'userid' => 's.spotterid',
- 'spotterid' => 's.spotterid',
- 'moderated' => 's.moderated',
- 'poster' => 's.poster',
- 'titel' => 's.title',
- 'title' => 's.title',
- 'tag' => 's.tag',
- 'new' => 'new',
- 'reportcount' => 's.reportcount',
- 'commentcount' => 's.commentcount',
- 'downloaded' => 'downloaded',
- 'mypostedspots' => 'mypostedspots',
- 'whitelistedspotters' => 'whitelistedspotters',
- 'watch' => 'watch',
- 'seen' => 'seen');
- foreach($filterValueList as $filterRecord) {
- $tmpFilterFieldname = strtolower($filterRecord['fieldname']);
- $tmpFilterOperator = $filterRecord['operator'];
- $tmpFilterValue = $filterRecord['value'];
- # When no match for friendly name -> column name is found, ignore the search
- if (!isset($filterFieldMapping[$tmpFilterFieldname])) {
- break;
- } # if
- # make sure the operators are valid
- if (!in_array($tmpFilterOperator, array('>', '<', '>=', '<=', '=', '!='))) {
- break;
- } # if
- /*
- * Ignore empty searches. We cannot use the empty() operator,
- * because empty(0) evaluates to true but is an valid
- * value to search for
- */
- if (strlen($tmpFilterValue) == 0) {
- continue;
- } # if
- /*
- * When the search is pure textsearch, it might be able to be optimized
- * by utilizing the fulltext search (engine). If so, we take this path
- * to gain the most performance.
- */
- if (in_array($tmpFilterFieldname, array('tag', 'poster', 'titel'))) {
- /*
- * Some databases (sqlite for example), want to have all their fulltext
- * searches available in one SQL function call.
- *
- * To be able to do this, we append all fulltext searches for now, so we
- * can create the actual fulltext search later on.
- */
- if (!isset($textSearchFields[$filterFieldMapping[$tmpFilterFieldname]])) {
- $textSearchFields[$filterFieldMapping[$tmpFilterFieldname]] = array();
- } # if
- $textSearchFields[$filterFieldMapping[$tmpFilterFieldname]][] = array('fieldname' => $filterFieldMapping[$tmpFilterFieldname], 'value' => $tmpFilterValue);
- } elseif (in_array($tmpFilterFieldname, array('new', 'downloaded', 'watch', 'seen', 'mypostedspots', 'whitelistedspotters'))) {
- /*
- * Some fieldnames are mere dummy fields which map to actual
- * functionality. Those dummiefields are processed below
- */
- switch($tmpFilterFieldname) {
- case 'new' : {
- $tmpFilterValue = ' ((s.stamp > ' . (int) $this->_dbEng->safe($currentSession['user']['lastread']) . ')';
- $tmpFilterValue .= ' AND (l.seen IS NULL))';
-
- break;
- } # case 'new'
- case 'whitelistedspotters' : {
- $tmpFilterValue = ' (wl.spotterid IS NOT NULL)';
- break;
- } # case 'whitelistedspotters'
- case 'mypostedspots' : {
- $additionalFields[] = '1 AS mypostedspot';
- $additionalJoins[] = array('tablename' => 'spotsposted',
- 'tablealias' => 'spost',
- 'jointype' => 'LEFT',
- 'joincondition' => 'spost.messageid = s.messageid');
- $tmpFilterValue = ' (spost.ouruserid = ' . (int) $this->_dbEng->safe($currentSession['user']['userid']) . ') ';
- $sortFields[] = array('field' => 'spost.stamp',
- 'direction' => 'DESC',
- 'autoadded' => true,
- 'friendlyname' => null);
- break;
- } # case 'mypostedspots'
- case 'downloaded' : {
- $tmpFilterValue = ' (l.download IS NOT NULL)';
- $sortFields[] = array('field' => 'downloadstamp',
- 'direction' => 'DESC',
- 'autoadded' => true,
- 'friendlyname' => null);
- break;
- } # case 'downloaded'
- case 'watch' : {
- $additionalFields[] = '1 AS mywatchedspot';
- $tmpFilterValue = ' (l.watch IS NOT NULL)';
- $sortFields[] = array('field' => 'watchstamp',
- 'direction' => 'DESC',
- 'autoadded' => true,
- 'friendlyname' => null);
- break;
- } # case 'watch'
- case 'seen' : {
- $additionalFields[] = '1 AS myseenspot';
- $tmpFilterValue = ' (l.seen IS NOT NULL)';
- $sortFields[] = array('field' => 'seenstamp',
- 'direction' => 'DESC',
- 'autoadded' => true,
- 'friendlyname' => null);
- break;
- } # case 'seen'
- } # switch
-
- # append the created query string to be an AND filter
- $filterValueSql['AND'][] = $tmpFilterValue;
- } else {
- /*
- * No FTS, no dummyfield, it must be some sort of comparison then.
- *
- * First we want to extract the field we are filtering on.
- */
- if ($tmpFilterFieldname == 'date') {
- $tmpFilterValue = date("U", strtotime($tmpFilterValue));
- } elseif ($tmpFilterFieldname == 'stamp') {
- $tmpFilterValue = (int) $tmpFilterValue;
- } elseif (($tmpFilterFieldname == 'filesize') && (is_numeric($tmpFilterValue) === false)) {
- # Explicitly cast to float to workaroun a rounding bug in PHP on x86
- $val = (float) trim(substr($tmpFilterValue, 0, -1));
- $last = strtolower($tmpFilterValue[strlen($tmpFilterValue) - 1]);
- switch($last) {
- case 'g': $val *= (float) 1024;
- case 'm': $val *= (float) 1024;
- case 'k': $val *= (float) 1024;
- } # switch
- $tmpFilterValue = round($val, 0);
- } # if
-
- /*
- * add quotes around it when not numeric. We cannot blankly always add quotes
- * as postgresql doesn't like that of course
- */
- if (!is_numeric($tmpFilterValue)) {
- $tmpFilterValue = "'" . $this->_dbEng->safe($tmpFilterValue) . "'";
- } else {
- $tmpFilterValue = $this->_dbEng->safe($tmpFilterValue);
- } # if
- # depending on the type of search, we either add the filter as an AND or an OR
- if (in_array($tmpFilterFieldname, array('spotterid', 'userid'))) {
- $filterValueSql['OR'][] = ' (' . $filterFieldMapping[$tmpFilterFieldname] . ' ' . $tmpFilterOperator . ' ' . $tmpFilterValue . ') ';
- } else {
- $filterValueSql['AND'][] = ' (' . $filterFieldMapping[$tmpFilterFieldname] . ' ' . $tmpFilterOperator . ' ' . $tmpFilterValue . ') ';
- } # else
- } # if
- } # foreach
- /*
- * When all filters are processed, we want to check wether we actually
- * have to process any of the $textSearchFields for which we could run
- * the db specific FTS engine.
- *
- * If so, ask the FTS engin to process the query.
- */
- if (!empty($textSearchFields)) {
- /*
- * We group searches per search type, but this means
- * we need to pass several the total amount of added
- * fields to the FTS so it can create unique relevancy
- * field names.
- *
- * For example: search for poster AA and Title BB
- */
- foreach($textSearchFields as $searchField => $searches) {
- $ftsEng = dbfts_abs::Factory($this->_dbEng);
- $parsedTextQueryResult = $ftsEng->createTextQuery($searches, $additionalFields);
- if (in_array($searches[0]['fieldname'], array('s.poster', 's.tag', 's.title'))) {
- $filterValueSql['AND'][] = ' (' . implode(' OR ', $parsedTextQueryResult['filterValueSql']) . ') ';
- } else {
- $filterValueSql['AND'][] = ' (' . implode(' AND ', $parsedTextQueryResult['filterValueSql']) . ') ';
- } # if
- $additionalTables = array_merge($additionalTables, $parsedTextQueryResult['additionalTables']);
- $additionalFields = array_merge($additionalFields, $parsedTextQueryResult['additionalFields']);
- $sortFields = array_merge($sortFields, $parsedTextQueryResult['sortFields']);
- } # foreach
- } # if
-
- SpotTiming::stop(__FUNCTION__, array($filterValueSql, $additionalFields, $additionalTables, $additionalJoins, $sortFields));
- return array($filterValueSql, $additionalFields, $additionalTables, $additionalJoins, $sortFields);
- } # filterValuesToSql
- /*
- * Converts the sorting as asked to an intermediate format ready for processing
- */
- private function prepareSortFields($sort, $sortFields) {
- SpotTiming::start(__FUNCTION__);
- $VALID_SORT_FIELDS = array('category' => 1,
- 'poster' => 1,
- 'title' => 1,
- 'filesize' => 1,
- 'stamp' => 1,
- 'subcata' => 1,
- 'spotrating' => 1,
- 'commentcount' => 1);
- if ((!isset($sort['field'])) || (!isset($VALID_SORT_FIELDS[$sort['field']]))) {
- /*
- * Add an extra sort on stamp. It might be that a FTS engine or something else,
- * has added a requested sorting as well, so make sure we add it to the end of
- * sortfields.
- */
- $sortFields[] = array('field' => 's.stamp', 'direction' => 'DESC', 'autoadded' => true, 'friendlyname' => null);
- } else {
- if (strtoupper($sort['direction']) != 'ASC') {
- $sort['direction'] = 'DESC';
- } # if
- /*
- * Explicit requested sorts, are prepended to the beginning of the array, so
- * the user requested sorting always is preferred above any other sorting
- */
- array_unshift($sortFields, array('field' => 's.' . $sort['field'],
- 'direction' => $sort['direction'],
- 'autoadded' => false,
- 'friendlyname' => $sort['field']));
- } # else
-
- SpotTiming::stop(__FUNCTION__, array($sortFields));
- return $sortFields;
- } # prepareSortFields
-
-
- /*
- * "Compresses" an expanded category list. It tries to search for the smallest
- * (in string length) match which contains the same information.
- *
- * This function, for example, will translate cat0_z0_a1,cat0_z0_a2,... to a
- * simple cat0_z0_a string and other nifty tricks.
- *
- * This is wanted to get cleaner urls, to be more efficient when parsing and
- * to be able to lessen the change we will hit the GET HTTP url limit.
- *
- */
- public function compressCategorySelection($categoryList, $strongNotList) {
- SpotTiming::start(__FUNCTION__);
- $compressedList = '';
- /*
- * We process each category, and the matching subcategories, to make sure all
- * required elments are set. If so, we remove the individual elements and
- * add the shorthand for it.
- */
- foreach(SpotCategories::$_head_categories as $headCatNumber => $headCatValue) {
- $subcatsMissing = array();
- # match each subcategory
- if (isset($categoryList['cat'][$headCatNumber])) {
- $subcatsMissing[$headCatNumber] = array();
- foreach($categoryList['cat'][$headCatNumber] as $subCatType => $subCatValues) {
- $subcatsMissing[$headCatNumber][$subCatType] = array();
-
- foreach(SpotCategories::$_categories[$headCatNumber] as $subCat => $subcatValues) {
- if ($subCat !== 'z') {
- if (isset($categoryList['cat'][$headCatNumber][$subCatType][$subCat])) {
- # process all subcategory values to see if any are missing
- foreach(SpotCategories::$_categories[$headCatNumber][$subCat] as $subcatValue => $subcatDescription) {
- # Make sure the subcategory is actually avaialble for this type
- if (in_array($subCatType, $subcatDescription[2])) {
- # and if the subcat element is missing, add it to the missing list
- if (array_search($subcatValue, $categoryList['cat'][$headCatNumber][$subCatType][$subCat]) === false) {
- $subcatsMissing[$headCatNumber][$subCatType][$subCat][$subcatValue] = 1;
- } # if
- } # if
- } # foreach
- } else {
- // $subcatsMissing[$headCatNumber][$subCatType][$subCat] = array();
- } # if
- } # if
- } # foreach
-
- } # foreach
- //var_dump($categoryList);
- //var_dump(expression)($subcatsMissing);
- //die();
- /*
- * If not the complete headcategory has been selected, we have to
- * do a tiny bit more work to get the exact match
- */
- if (!empty($subcatsMissing[$headCatNumber])) {
- /*
- * There are three possible situations:
- *
- * - the subcategory does not exist at all, we select the complete subcategory
- * - the subcategory exists, but is empty. It means we do not want anything out of it
- * - the subcategory exists, and is not empty. The items in it, are the items we do not want
- */
- foreach($categoryList['cat'][$headCatNumber] as $subType => $subTypeValue) {
- /*
- * Check wether the complete headcat+subtype (cat0_z0, cat0_z1) is selected
- */
- if (!empty($subcatsMissing[$headCatNumber][$subType])) {
- foreach(SpotCategories::$_subcat_descriptions[$headCatNumber] as $subCatKey => $subCatValue) {
- if ($subCatKey !== 'z') {
- if (!isset($subcatsMissing[$headCatNumber][$subType][$subCatKey])) {
- // $compressedList .= 'cat' . $headCatNumber . '_' . $subType . '_' . $subCatKey . ',';
- } elseif (empty($subcatsMissing[$headCatNumber][$subType][$subCatKey])) {
- /*
- * If the subcategory is completely empty, the user doesn't
- * want anything from it
- */
- } else {
- /*
- * The subcategory does exist, but contains only items
- * the user doesn't want or need. We deselected them here.
- *
- * We can either add the whole category, and add a few
- * "NOT"'s (!cat0_z0_a1) or just selected the individual
- * items. We determine this whether the majority is
- * selected or excluded.
- */
- $moreFalseThanTrue = (count(@$subcatsMissing[$headCatNumber][$subType][$subCatKey]) > (count(@SpotCategories::$_categories[$headCatNumber][$subCatKey][$subCatValue]) / 2));
- foreach(SpotCategories::$_categories[$headCatNumber][$subCatKey] as $subCatValue => $subCatDesc) {
- if (in_array($subType, $subCatDesc[2])) {
- if ($moreFalseThanTrue) {
- if (!isset($subcatsMissing[$headCatNumber][$subType][$subCatKey][$subCatValue])) {
- $compressedList .= 'cat' . $headCatNumber . '_' . $subType . '_' . $subCatKey . $subCatValue . ',';
- } # if
- } else {
- if (isset($subcatsMissing[$headCatNumber][$subType][$subCatKey][$subCatValue])) {
- /*
- * We have to make sure the whole category is selected, so we perform an
- * extra check for it
- */
- if (strpos(',' . $compressedList . ',', ',cat' . $headCatNumber . '_' . $subType . '_' . $subCatKey . ',') === false) {
- $compressedList .= 'cat' . $headCatNumber . '_' . $subType . '_' . $subCatKey . ',';
- } # if
-
- # and start deselecting the subcategories
- $compressedList .= '!cat' . $headCatNumber . '_' . $subType . '_' . $subCatKey . $subCatValue . ',';
- } # if
- } # if
- } # if
- } # foreach
- } # else
- } # if
-
- } # foreach
- } else {
- $compressedList .= 'cat' . $headCatNumber . '_' . $subType . ',';
- } # if
- } # foreach
- } else {
- $compressedList .= 'cat' . $headCatNumber . ',';
- } # else
- } # if
- } # foreach
- # and of course, add the strong not list
- if (!empty($strongNotList)) {
- foreach($strongNotList as $headCat => $subcatList) {
- foreach($subcatList as $subcatValue) {
- $compressedList .= '~cat' . $headCat . '_' . $subcatValue . ',';
- } # foreach
- } # foreach
- } # if
- SpotTiming::stop(__FUNCTION__, array($compressedList));
- return $compressedList;
- } # compressCategorySelection
- /*
- * Converts an array with search terms (tree, type, valus) to an SQL statement
- * to be glued to an SQL WHERE query
- */
- public function filterToQuery($search, $sort, $currentSession, $indexFilter) {
- SpotTiming::start(__FUNCTION__);
-
- $isUnfiltered = false;
-
- $categoryList = array();
- $categorySql = array();
-
- $strongNotList = array();
- $strongNotSql = array();
-
- $filterValueList = array();
- $filterValueSql = array();
-
- $additionalFields = array();
- $additionalTables = array();
- $additionalJoins = array();
- $sortFields = array();
-
- # Take the easy way out of no filters have been given
- if (empty($search)) {
- return array('filter' => '',
- 'search' => array(),
- 'additionalFields' => array(),
- 'additionalTables' => array(),
- 'additionalJoins' => array(),
- 'categoryList' => array(),
- 'strongNotList' => array(),
- 'filterValueList' => array(),
- 'unfiltered' => false,
- 'sortFields' => array(array('field' => 'stamp', 'direction' => 'DESC', 'autoadded' => true, 'friendlyname' => null)));
- } # if
- /*
- * Process the parameters in $search, legacy parameters are converted
- * to a common format by prepareFilterValues, this list is then
- * converted to SQL
- */
- $filterValueList = $this->prepareFilterValues($search);
- list($filterValueSql, $additionalFields, $additionalTables, $additionalJoins, $sortFields) = $this->filterValuesToSql($filterValueList, $currentSession);
- /*
- * When asked to forget all category filters (and only search for a word/typefilter)
- * we simply reset the filter by overwriting $search with $indexfilter
- */
- if ((isset($search['unfiltered'])) && (($search['unfiltered'] === 'true'))) {
- $search = array_merge($search, $indexFilter);
- $isUnfiltered = true;
- } # if
- /*
- * If a tree was given, convert it to subcategories etc.
- * prepareCategorySelection() makes sure all categories eventually
- * are in a common format
- */
- if (!empty($search['tree'])) {
- # explode the dynaList
- $dynaList = explode(',', $search['tree']);
- list($categoryList, $strongNotList) = $this->prepareCategorySelection($dynaList);
- # and convert to SQL
- $categorySql = $this->categoryListToSql($categoryList);
- $strongNotSql = $this->strongNotListToSql($strongNotList);
- } # if
- # Check for an explicit sorting convention
- $sortFields = $this->prepareSortFields($sort, $sortFields);
- $endFilter = array();
- if (!empty($categorySql)) {
- $endFilter[] = '(' . join(' OR ', $categorySql) . ') ';
- } # if
- if (!empty($filterValueSql['AND'])) {
- $endFilter[] = '(' . join(' AND ', $filterValueSql['AND']) . ') ';
- } # if
- if (!empty($filterValueSql['OR'])) {
- $endFilter[] = '(' . join(' OR ', $filterValueSql['OR']) . ') ';
- } # if
- $endFilter[] = join(' AND ', $strongNotSql);
- $endFilter = array_filter($endFilter);
- SpotTiming::stop(__FUNCTION__, array(join(" AND ", $endFilter)));
- return array('filter' => join(" AND ", $endFilter),
- 'categoryList' => $categoryList,
- 'unfiltered' => $isUnfiltered,
- 'strongNotList' => $strongNotList,
- 'filterValueList' => $filterValueList,
- 'additionalFields' => $additionalFields,
- 'additionalTables' => $additionalTables,
- 'additionalJoins' => $additionalJoins,
- 'sortFields' => $sortFields);
- } # filterToQuery
- } # Services_Search_QueryParser