function _getListingSidCollectionFromRequest() { $listing = new SJB_Listing(array(), $this->listing_type_sid); $id_alias_info = $listing->addIDProperty(); $listing->addActivationDateProperty(); $listing->addFeaturedProperty(); $listing->addFeaturedLastShowedProperty(); $username_alias_info = $listing->addUsernameProperty(); $listing_type_id_info = $listing->addListingTypeIDProperty(); $listing->addCompanyNameProperty(); // select only accessible listings by user sid // see SearchCriterion.php, AccessibleCriterion class if ($this->listing_type_id == 'Resume') { $this->requested_data['access_type'] = array('accessible' => SJB_UserManager::getCurrentUserSID()); } $criteria = $this->criteria_saver->getCriteria(); if (isset($this->requested_data['PostedWithin']['multi_like'][0]) || isset($criteria['PostedWithin']['multi_like'][0])) { $within_period = ''; if (isset($this->requested_data['PostedWithin']['multi_like'][0])) { $within_period = $this->requested_data['PostedWithin']['multi_like'][0]; unset($this->requested_data['PostedWithin']['multi_like']); } if (isset($criteria['PostedWithin']['multi_like'][0])) { $within_period = $criteria['PostedWithin']['multi_like'][0]; unset($criteria['PostedWithin']); } $i18n = SJB_I18N::getInstance(); $this->requested_data['activation_date']['not_less'] = $i18n->getDate(date('Y-m-d', strtotime("- {$within_period} days"))); } if (isset($this->requested_data['CompanyName']['multi_like_and'][0]) || isset($criteria['CompanyName']['multi_like_and'][0])) { if (isset($this->requested_data['CompanyName']['multi_like_and'][0])) { $companyName = $this->requested_data['CompanyName']['multi_like_and'][0]; unset($this->requested_data['CompanyName']); } if (isset($criteria['CompanyName']['multi_like_and'][0])) { $companyName = $criteria['CompanyName']['multi_like_and'][0]; unset($criteria['CompanyName']); } $userName = SJB_UserManager::getUserNameByCompanyName($companyName); if ($userName) { $this->requested_data['username']['equal'] = $userName; } } $criteria = SJB_SearchFormBuilder::extractCriteriaFromRequestData(array_merge($criteria, $this->requested_data), $listing); $aliases = new SJB_PropertyAliases(); $aliases->addAlias($id_alias_info); $aliases->addAlias($username_alias_info); $aliases->addAlias($listing_type_id_info); $sortingFields = array(); if ($this->usePriority) { $sortingFields['priority'] = 'DESC'; } $innerJoin = array(); $orderInfo = $this->criteria_saver->getOrderInfo(); if (is_array($orderInfo['sorting_field'])) { $requestedSortingField = array(); foreach ($orderInfo['sorting_field'] as $orderInfoProperty) { $fieldName = strstr($orderInfoProperty, '_', true); $id = $this->getLocationProperty($fieldName, $listing, $orderInfoProperty); if (!empty($id)) { switch ($orderInfoProperty) { case 'Location_State': $innerJoin['states'] = array('stateName' => '`states`.`state_code`', 'join' => 'LEFT JOIN', 'join_field' => 'sid', 'join_field2' => $orderInfoProperty, 'main_table' => 'listings'); break; case 'Location_Country': $innerJoin['countries'] = array('countryName' => '`countries`.`country_name`', 'join' => 'LEFT JOIN', 'join_field' => 'sid', 'join_field2' => $orderInfoProperty, 'main_table' => 'listings'); break; default: break; } $sortingFields[$orderInfoProperty] = $orderInfo['sorting_order']; $requestedSortingField[] = $orderInfoProperty; } $this->requested_data['sorting_field'] = $requestedSortingField; $this->requested_data['sorting_order'] = $orderInfo['sorting_order']; } } else { $property = $listing->getProperty($orderInfo['sorting_field']); if (!empty($property) && $property->isSystem()) { $sortingFields[$orderInfo['sorting_field']] = $orderInfo['sorting_order']; if ($property->getID() == 'CompanyName') { $innerJoin['users'] = array('join' => 'INNER JOIN', 'join_field' => 'sid', 'join_field2' => 'user_sid', 'main_table' => 'listings'); } $this->requested_data['sorting_field'] = $orderInfo['sorting_field']; $this->requested_data['sorting_order'] = $orderInfo['sorting_order']; } else { $sortingFields['activation_date'] = 'DESC'; $this->requested_data['sorting_field'] = 'activation_date'; $this->requested_data['sorting_order'] = 'DESC'; } } $searcher = new SJB_ListingSearcher(); if ($this->listingsLimit) { $searcher->setLimit($this->listingsLimit); } $this->listing_search_structure['sorting_field'] = $this->requested_data['sorting_field']; $this->listing_search_structure['sorting_order'] = $this->requested_data['sorting_order']; return $searcher->getObjectsSIDsByCriteria($criteria, $aliases, $sortingFields, $innerJoin, $this->relevance); }
/** * @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; }