function getObjectInfo($sorting_fields, $inner_join = false, $relevance = false) { $searchSqlTranslator = new SJB_SearchSqlTranslator($this->table_prefix); $sql_string = $searchSqlTranslator->buildSqlQuery($this->criteria, $this->valid_criterion_number, array($this->sorting_field => $this->sorting_order), $this->inner_join); SJB_DB::query($sql_string); $this->affectedRows = SJB_DB::getAffectedRows(); if ($this->limit !== false) { if (isset($this->limit['limit'])) { $sql_string .= " limit " . $this->limit['limit'] . ", " . $this->limit['num_rows']; } else { $sql_string .= " limit " . $this->limit . ", 100"; } } return SJB_DB::query($sql_string); }
protected function _get_Captions_with_Counts_Grouped_by_Captions($request_data, array $listingSids = array()) { $items = array(); if (!empty($request_data[$this->field['field']]['tree'])) { $this->parent = $items = explode(',', $request_data[$this->field['field']]['tree']); $treeValues = $this->tree_values; $this->tree_values = array(); } if (SJB_Settings::getValue('enableBrowseByCounter')) { $request_data['access_type'] = array('accessible' => SJB_UserManager::getCurrentUserSID()); $criteria = SJB_SearchFormBuilder::extractCriteriaFromRequestData($request_data); $sqlTranslator = new SJB_SearchSqlTranslator('listings'); $whereStatement = $sqlTranslator->_getWhereStatement($criteria); $sql = "`{$this->field['field']}` != ''"; if (count($items) > 1) { $sql = " AND ("; foreach ($items as $key => $item) { if ($key == 0) { $sql .= " FIND_IN_SET('{$item}',`{$this->field['field']}`) "; } else { $sql .= " OR FIND_IN_SET('{$item}',`{$this->field['field']}`) "; } if (isset($treeValues[$item])) { $this->tree_values[$item] = $treeValues[$item]; } } $sql .= ")"; } if (!empty($listingSids)) { $sql .= ' AND `listings`.`sid` IN (' . implode(',', $listingSids) . ')'; } $sql = "SELECT `?w` AS caption, `sid` AS object_sid FROM `listings` {$whereStatement} AND {$sql}"; $result = SJB_DB::query($sql, $this->field['field']); $result = self::_getCountsByItems($result); } else { if (count($items) > 1) { foreach ($items as $item) { $this->tree_values[$item] = $treeValues[$item]; } } $result = self::getTreeItems($this->tree_values, SJB_ListingFieldTreeManager::getTreeDepthBySID($this->field['sid'])); } return $result; }
protected function _get_Captions_with_Counts_Grouped_by_Captions($request_data, array $listingSids = array()) { $columns = '?w as caption'; if (SJB_Settings::getValue('enableBrowseByCounter')) { $columns .= ', count(*) as count'; } $criteria = SJB_SearchFormBuilder::extractCriteriaFromRequestData($request_data); $sqlTranslator = new SJB_SearchSqlTranslator('listings'); $whereStatement = $sqlTranslator->_getWhereStatement($criteria); if (!empty($listingSids)) { $whereStatement .= 'AND `listings`.`sid` IN (' . implode(',', $listingSids) . ')'; } $res = SJB_DB::query("select {$columns} from listings {$whereStatement} and ?w != '' group by ?w", $this->field['field'], $this->field['field'], $this->field['field']); $result = array(); foreach ($res as $value) { if (SJB_Settings::getValue('enableBrowseByCounter')) { $result[$value['caption']] = $value['count']; } else { $result[$value['caption']] = ''; } } return $result; }
/** * @param $fieldName * @param $fieldID * @param SJB_SearchResultsTP $stp * @param $userField * @return mixed */ public static function countListingsByFieldName($fieldName, $fieldID, $stp, $userField) { $refineSearchLimit = SJB_Settings::getSettingByName('refine_search_items_limit'); $limit = $refineSearchLimit ? ' LIMIT 0, ' . $refineSearchLimit : ''; $listing = new SJB_Listing(array(), $stp->listing_type_sid); $id_alias_info = $listing->addIDProperty(); $listing->addActivationDateProperty(); $listing->addFeaturedProperty(); $username_alias_info = $listing->addUsernameProperty(); $listing_type_id_info = $listing->addListingTypeIDProperty(); $listing->addCompanyNameProperty(); $requestedCriteria = $stp->criteria_saver->getCriteria(); if (isset($requestedCriteria['PostedWithin']) && $requestedCriteria['PostedWithin']['multi_like'][0] != '') { $within_period = $requestedCriteria['PostedWithin']['multi_like'][0]; $i18n = SJB_I18N::getInstance(); $requestedCriteria['activation_date']['not_less'] = $i18n->getDate(date('Y-m-d', strtotime("- {$within_period} days"))); unset($requestedCriteria['PostedWithin']); } if (isset($requestedCriteria['CompanyName']['multi_like_and'][0])) { $userName = SJB_UserManager::getUserNameByCompanyName($requestedCriteria['CompanyName']['multi_like_and'][0]); unset($requestedCriteria['CompanyName']); if ($userName) { $requestedCriteria['username']['equal'] = $userName; } } $criteria = SJB_SearchFormBuilder::extractCriteriaFromRequestData($requestedCriteria, $listing); $aliases = new SJB_PropertyAliases(); $aliases->addAlias($id_alias_info); $aliases->addAlias($username_alias_info); $aliases->addAlias($listing_type_id_info); $aliases->changeAliasValuesInCriteria($criteria); $sqlTranslator = new SJB_SearchSqlTranslator('listings'); $whereStatement = $sqlTranslator->_getWhereStatement($criteria); $objectSids = implode(',', $stp->found_listings_sids); if ($userField == 1) { $field = SJB_UserProfileFieldManager::getFieldInfoBySID($fieldID); } else { $field = SJB_ListingFieldDBManager::getListingFieldInfoBySID($fieldID); } $result = array(); $cache = SJB_Cache::getInstance(); if (!empty($field['parent_sid'])) { $parentInfo = SJB_ListingFieldManager::getFieldInfoBySID($field['parent_sid']); $fieldName = $parentInfo['id'] . "_" . $fieldName; $field['id'] = $fieldName; $field['parentID'] = $parentInfo['id']; } switch ($field['type']) { case 'list': case 'multilist': if ($userField == 1) { $query = "SELECT up.`{$fieldName}` as caption, count(`listings`.`sid`) as count\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t FROM `listings`\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t INNER JOIN `users` `up` ON `listings`.`user_sid` = `up`.`sid`\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t {$whereStatement}\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t AND up.`{$fieldName}` != ''\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t GROUP BY `up`.`{$fieldName}` ORDER BY count DESC"; if (!($result = $cache->load(md5($query)))) { $result = SJB_DB::query($query); $cache->save($result, md5($query), array(SJB_Cache::TAG_LISTINGS, SJB_Cache::TAG_USERS)); } } else { $query = "SELECT `{$fieldName}` as caption, count(`{$fieldName}`) as count FROM `listings` {$whereStatement} AND `{$fieldName}` != '' GROUP BY `{$fieldName}` ORDER BY count DESC"; if (!($result = $cache->load(md5($query)))) { $result = SJB_DB::query($query); $cache->save($result, md5($query), array(SJB_Cache::TAG_LISTINGS)); } } self::breakMultiCategory($result); $newResult = array(); $listItem = new SJB_ListingFieldListItemManager(); foreach ($result as $key => $val) { if (!empty($field['parent_sid'])) { $caption = ''; if ($field['id'] == $field['parentID'] . '_State') { $listValues = SJB_StatesManager::getStatesNamesByCountry(false, true, $field['display_as']); } else { $listValues = $field['list_values']; } foreach ($listValues as $listValue) { if ($listValue['id'] == $val['caption']) { $caption = $listValue['caption']; break; } } } else { $itemInfo = $listItem->getListItemBySID($val['caption']); $caption = $itemInfo ? $itemInfo->getValue() : null; } if ($caption != null) { $newResult[$key]['count'] = $val['count']; $newResult[$key]['value'] = $caption; $newResult[$key]['sid'] = $val['caption']; } } arsort($newResult); $result = $newResult; if (count($result) > $refineSearchLimit) { $result = array_slice($result, 0, $refineSearchLimit); } break; case 'tree': $query = "SELECT `lt`.`sid` as `sid`, `lt`.`caption` as `value`, count(`listings`.`sid`) as `count`\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM `listings`\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `listing_field_tree` `lt` ON `lt`.`field_sid` = {$field['sid']} AND find_in_set(`lt`.`sid`, `listings`.`{$fieldName}`)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t{$whereStatement} GROUP BY `lt`.`sid` having `lt`.`sid` IS NOT NULL ORDER BY `count` DESC {$limit}"; if (!($propertyValue = $cache->load(md5($query)))) { $propertyValue = SJB_DB::query($query); $cache->save($propertyValue, md5($query), array(SJB_Cache::TAG_LISTINGS, SJB_Cache::TAG_FIELDS)); } foreach ($propertyValue as $value) { $result[$value['sid']] = $value; } break; default: if ($userField == 1) { $companyColumn = "up.`{$fieldName}`"; $query = "SELECT {$companyColumn} as `value`, count(listings.`sid`) as `count`\n\t\t\t\t\t\t\t\t\t\t\t FROM `listings`\n\t\t\t\t\t\t\t\t\t\t\t INNER JOIN `users` `up` ON `listings`.`user_sid` = `up`.`sid`\n\t\t\t\t\t\t\t\t\t\t\t {$whereStatement}\n\t\t\t\t\t\t\t\t\t\t\t GROUP BY {$companyColumn} ORDER BY `count` DESC {$limit}"; if (!($result = $cache->load(md5($query)))) { $result = SJB_DB::query($query); $cache->save($result, md5($query), array(SJB_Cache::TAG_LISTINGS, SJB_Cache::TAG_USERS)); } } else { if ($field['type'] == 'complex') { $query = "SELECT `value`, count(`value`) as count FROM `listings_properties` WHERE `id`='{$fieldName}' AND `value` != '' AND `object_sid` in ({$objectSids}) GROUP BY `value` ORDER BY count DESC {$limit}"; } else { $query = "SELECT `{$fieldName}` as value, count(`{$fieldName}`) as count FROM `listings` {$whereStatement} AND `{$fieldName}` != '' GROUP BY `{$fieldName}` ORDER BY count DESC {$limit}"; } if (!($result = $cache->load(md5($query)))) { $result = SJB_DB::query($query); $cache->save($result, md5($query), array(SJB_Cache::TAG_LISTINGS)); } } break; } $returnArr['caption'] = $field['caption']; $returnArr['values'] = $result; return $returnArr; }