Jump to content

Yarhajile

Members
  • Content count

    43
  • Joined

  • Last visited

  • Days Won

    1

Yarhajile last won the day on April 30 2010

Yarhajile had the most liked content!

Profile Information

  • Real Name
    Elijah
  • Gender
    Male
  • Location
    Spokane, WA
  1. Yarhajile

    [Development] Products Specifications

    I've looked through the queries being performed and everything seems to look OK as far as indexing goes. The specification_description table's indexes don't get used in the query plans since there are so few rows in the table on my end. The indexes are there though on the specifications_id, so I wouldn't expect any problems. If you're going to be creating the package using the counting code, you'll notice the deficient area of what I've been working on is on the SPECIFICATION_FILTER_NO_RESULT conditions. I simply disable the filters if there aren't any results, but it should be easy enough to implement the "greyed out" feature here to pass on to the tep_get_filter_string() function. I'd have done it myself if I felt a need to use it. :) Just wanted to point that out since it's the only incomplete item that I could think of off the top of my head.
  2. Yarhajile

    [Development] Products Specifications

    I think I'm done for now (the weekend at least!) making any further changes. Our current setup is in an eye-brow raising satisfactory condition, so as long as the security checks out ok, we'll be moving forward in getting this to a heavy testing stage this next week. Thanks for providing all of the work you've done on this!
  3. Yarhajile

    [Development] Products Specifications

    I've moved the counting function into an object so that a some of the more repetitive queries can be stored in the object rather than in $GLOBALS. No doubt this can be built out even further to incorporate some of the other features this mod provides, but at a more modular level. The functionality is the same, just a little cleaner delivery. New file: catalog/includes/classes/Specifications.php <?php class Specifications { private $specs = array(); private $applied_filters = array(); private $current_category_id; private $languages_id; public function __construct() { global $current_category_id, $languages_id; $this->current_category_id = $current_category_id; $this->languages_id = $languages_id; $this->setAppliedFilters(); } private function setAppliedFilters() { $category_sql = $this->current_category_id != 0 ? "and s2c.categories_id = '" . $this->current_category_id . "'" : ''; // Check for filters on each applicable Specification $specs_query_raw = "SELECT s.specifications_id, s.filter_class, s.products_column_name, sd.specification_name FROM " . TABLE_SPECIFICATION . " AS s INNER JOIN " . TABLE_SPECIFICATION_GROUPS . " AS sg ON s.specification_group_id = sg.specification_group_id INNER JOIN " . TABLE_SPECIFICATIONS_TO_CATEGORIES . " AS s2c ON sg.specification_group_id = s2c.specification_group_id INNER JOIN " . TABLE_SPECIFICATION_DESCRIPTION . " sd ON sd.specifications_id = s.specifications_id WHERE s.show_filter = 'True' AND sg.show_filter = 'True' " . $category_sql; $specs_query = tep_db_query ($specs_query_raw); while ($specs_array = tep_db_fetch_array ($specs_query) ) { // Retrieve the GET vars used as filters // Variable names are the letter "f" followed by the specifications_id for that spec. $var = $specs_array['specifications_id']; $$var = '0'; if (isset ($_GET['f' . $var]) && $_GET['f' . $var] != '') { // Sanitize variables to prevent hacking $$var = tep_clean_get__recursive ($_GET['f' . $var]); // Set the cporrect variable type (All _GET variables are strings by default) $$var = tep_set_type ($$var); $this->applied_filters[$var] = $$var; } // if (isset ($_GET[$var] } // while ($specs_array } private function getAppliedFilters() { return $this->applied_filters; } public function getFilterCount($specification, $specifications_id, $filter_class, $products_column_name) { $raw_query_start = "select count(p.products_id) as count "; $raw_query_from = " from (products p) join (products_to_categories p2c) on (p.products_id = p2c.products_id) "; $raw_query_where = " where p.products_status = '1' and p2c.categories_id = '" . $this->current_category_id . "' "; $raw_query_addon_array = tep_get_filter_sql($filter_class, $specifications_id, $specification, $products_column_name, '1'); $raw_query_from .= $raw_query_addon_array['from']; $raw_query_where .= $raw_query_addon_array['where']; $applied_filters = $this->getAppliedFilters(); foreach($applied_filters as $k => $v) { if ($v == $specification) { continue; } $specs_array = $this->getSpecification($k); $raw_query_addon_array = tep_get_filter_sql($specs_array['filter_class'], $specs_array['specifications_id'], $v, $specs_array['products_column_name'], $this->languages_id); $raw_query_from .= $raw_query_addon_array['from']; $raw_query_where .= $raw_query_addon_array['where']; } // foreach($_GET $raw_query = $raw_query_start . $raw_query_from . $raw_query_where; $filter_count_query = tep_db_query($raw_query); $filter_count_results = tep_db_fetch_array($filter_count_query); $count = (string)$filter_count_results['count']; return $count; } private function getSpecification($id) { if (!isset($this->specs[$id])) { $specs_query_raw = "SELECT s.specifications_id, s.products_column_name, s.filter_class, s.filter_show_all, s.filter_display, sd.specification_name, sd.specification_prefix, sd.specification_suffix FROM " . TABLE_SPECIFICATION . " s JOIN " . TABLE_SPECIFICATION_DESCRIPTION . " sd ON s.specifications_id = sd.specifications_id JOIN " . TABLE_SPECIFICATION_GROUPS . " sg ON s.specification_group_id = sg.specification_group_id JOIN " . TABLE_SPECIFICATIONS_TO_CATEGORIES . " s2c ON sg.specification_group_id = s2c.specification_group_id WHERE s.specifications_id = '" . $id . "' and s.show_filter = 'True' and sg.show_filter = 'True' and sd.language_id = '" . $this->languages_id . "' and s2c.categories_id = '" . $this->current_category_id . "' "; $specs_query = tep_db_query($specs_query_raw); $this->specs[$id] = tep_db_fetch_array($specs_query); } return $this->specs[$id]; } } In catalog/includes/boxes/products_filter.php around line 47 before the 'while' loop for the $specs_array, add this. ~SNIP~ $count = 1; if ( !isset($_GET['f' . $specs_array['specifications_id']])) { $count = $spec_object->getFilterCount($filter_id, $specs_array['specifications_id'], $specs_array['filter_class'], $specs_array['products_column_name']); } if (SPECIFICATION_FILTER_NO_RESULT != 'normal') { if ($count == 0) { continue; } } $filters_select_array[$filter_index] = array ('id' => $filter_id, 'text' => $filter_text, 'count' => $count ); $filter_index++; } // while ($filters_array if ($specs_array['filter_class'] == 'range') { if ($specs_array['products_column_name'] == 'products_price') { $previous_filter = $currencies->format ($previous_filter); } $count = $spec_object->getFilterCount($previous_filter_id, $specs_array['specifications_id'], $specs_array['filter_class'], $specs_array['products_column_name']); if (SPECIFICATION_FILTER_NO_RESULT != 'normal') { if ($count > 0) { $filters_select_array[$filter_index] = array ('id' => $previous_filter_id, 'text' => $previous_filter . '+', 'count' => $count ); } } } ~SNIP~ I'll be working on implementing some of the other features of the mod into this object to consolidate the code for our store even more, but will post any of the updates I make here so that whomever chooses to use them can do so. Elijah
  4. Yarhajile

    [Development] Products Specifications

    You're welcome!
  5. Yarhajile

    [Development] Products Specifications

    It also looks like the change in step 3 //breadcrumbs : add to breadcumb array current array & sanitized $_GET value $specs_array_breadcrumb[] = array_merge($specs_array, array("value" => $_GET[$var])); should be //breadcrumbs : add to breadcumb array current array & sanitized $_GET value $specs_array_breadcrumb[] = array_merge($specs_array, array("value" => $$var)); in order to use the sanitized $$var Elijah
  6. Yarhajile

    [Development] Products Specifications

    Sanam, Your breadcrumb code worked straight out of the box on my setup over here. Thanks for the work on that! I did modify the crumb generation loop just a bit to shrink it down. This code // Start of Filter Breadcrumbs $total_crumbs = count($specs_array_breadcrumb); for($master=0;$master<$total_crumbs;$master++){ // start master: go through array and build the links $filter_link = ""; $master_array = $specs_array_breadcrumb[$master]; for($link=0;$link<$total_crumbs;$link++){ // start link: ignore the current array value from the master and add rest if($link != $master){ $filter_link .= '&f'.$specs_array_breadcrumb[$link]['specifications_id'].'='.$specs_array_breadcrumb[$link]['value']; } } // if end link $breadcrumb->add('[<sup>X</sup> '.$master_array['specification_name'].' : '.$master_array['value'].']', tep_href_link(FILENAME_PRODUCTS_FILTERS, 'cPath=' . $cPath . $filter_link)); }// if end master // End of Filter Breadcrumbs can be accomplished with this instead for a smaller footprint. The tep_get_all_get_params() function is the biggest player here as it does the work your loop does.. // Start of Filter Breadcrumbs foreach($specs_array_breadcrumb as $crumb){ $breadcrumb->add('<sup>X</sup>' . $crumb['specification_name'] . ' : ' . $crumb['value'], tep_href_link(FILENAME_PRODUCTS_FILTERS, tep_get_all_get_params(array('f' . $crumb['specifications_id'])))); } // End of Filter Breadcrumbs Elijah
  7. Yarhajile

    [Development] Products Specifications

    You've started something here with this project that will fix a lot of the navigation struggles sites have when they have lots of categories & products and I'm glad to help! Fixed a couple of bugs in the tep_get_filter_sql2() function. This one should be used instead. The 'exact' and probably the 'multiple' filter classes still need work so they skip specs that already have filters applied instead of the hack job I did for the sql table aliases. You may have a better idea for that! ///// // Generate the SQL to return the filtered values function tep_get_filter_sql2 ($filter_class, $specifications_id, $filter_array = array(), $products_column_name, $languages_id) { $sql_array = array('from' => '', 'where' => ''); $filter_array = (is_array ($filter_array) ) ? $filter_array : array ($filter_array); // If the Show All option is set, return a blank string if (isset ($filter_array[0]) && ($filter_array[0] == '0' || $filter_array[0] == '') ) { return $sql_array; } else { // Scrub the filter array so apostrophes in filters don't error out. foreach($filter_array as $filterKey => $filterValue) { $filter_array[$filterKey] = tep_db_input($filterValue); } // The Manufacturer's column contains an ID and not the name, so we have to change it if ($products_column_name == 'manufacturers_id') { $filter_array = tep_get_manufacturer_id ($filter_array, $products_column_name); $products_column_name = 'p.' . $products_column_name; } switch ($filter_class) { case 'exact': foreach ($filter_array as $filter) { // This might not be necessary if we can skip filters for already applied specs, // but until then this will keep the query aliases from overlapping $key = $specification_id . '_' . crc32($filter); if (isset ($filter) && $filter != '0' && $filter != '') { if (strlen ($products_column_name) > 1) { // Use an existing column $sql_array['where'] .= " AND " . $products_column_name . " = '" . $filter . "' "; } else { $sql_array['from'] .= " INNER JOIN " . TABLE_PRODUCTS_SPECIFICATIONS . " ps" . $key . " ON p.products_id = ps" . $key . ".products_id "; $sql_array['where'] .= " AND ps" . $key . ".specification = '" . $filter ."' AND ps" . $key . ".specifications_id = '" . $specifications_id . "' AND ps" . $key . ".language_id = '" . (int) $languages_id . "' "; } // if (strlen ($products_column_name ... else ... } // if (isset ($filter } // foreach ($filter_array break; case 'multiple': if (strlen ($products_column_name) > 1) { $sql_array['where'] .= " and " . $products_column_name . " in ("; $first = true; foreach ($filter_array as $filter) { if ($first == true) { $first = false; $sql_array['where'] .= " '" . $filter . "' "; } else { $sql_array['where'] .= ", '" . $filter . "' "; } } $sql_array['where'] .= ") "; } else { $sql_array['from'] .= " INNER JOIN " . TABLE_PRODUCTS_SPECIFICATIONS . " ps" . $specifications_id . " ON p.products_id = ps" . $specifications_id . ".products_id "; $first = true; foreach ($filter_array as $filter) { if ($filter != '0') { if ($first == true) { $first = false; $sql_array['where'] .= " AND ps" . $specifications_id . ".specification in ('" . $filter . "' "; } else { $sql_array['where'] .= ", '" . $filter . "' "; } } } $sql_array['where'] .= ") AND ps" . $specifications_id . ".specifications_id = '" . $specifications_id . "' AND ps" . $specifications_id . ".language_id = '" . (int) $languages_id . "' "; foreach ($filter_array as $filter) { if ($filter == '0') { $sql_array = array('from' => '', 'where' => ''); } } } break; case 'range': $filters_range = explode ('-', $filter_array[0]); if (strlen ($products_column_name) > 1) { if (count ($filters_range) < 2) { $sql_array['where'] .= " and " . $products_column_name . " > '" . $filters_range[0] . "'"; } else { $sql_array['where'] .= " and (" . $products_column_name . " between '" . $filters_range[0] . "' and '" . $filters_range[1] . "')"; } } else { if (count ($filters_range) < 2) { // There is only one parameter, so it is a minimum $sql_array['from'] .= " INNER JOIN " . TABLE_PRODUCTS_SPECIFICATIONS . " ps" . $specifications_id . " ON p.products_id = ps" . $specifications_id . ".products_id "; $sql_array['where'] .= " AND ps" . $specifications_id . ".specification > '" . $filters_range[0] ."' AND ps" . $specifications_id . ".specifications_id = '" . $specifications_id . "' AND ps" . $specifications_id . ".language_id = '" . (int) $languages_id . "' "; } else { // There are two parameters, so treat them as minimum and maximum $sql_array['from'] .= " INNER JOIN " . TABLE_PRODUCTS_SPECIFICATIONS . " ps" . $specifications_id . " ON p.products_id = ps" . $specifications_id . ".products_id "; $sql_array['where'] .= " AND (ps" . $specifications_id . ".specification between '" . $filters_range[0] ."' and '" . $filters_range[1] . "') AND ps" . $specifications_id . ".specifications_id = '" . $specifications_id . "' AND ps" . $specifications_id . ".language_id = '" . (int) $languages_id . "' "; } } break; case 'reverse': // No existing columns are set up as a reverse range, so this filter class has no provision for existing columns $sql_array['from'] .= " INNER JOIN " . TABLE_PRODUCTS_SPECIFICATIONS . " ps" . $specifications_id . " ON p.products_id = ps" . $specifications_id . ".products_id "; $sql_array['where'] .= " AND '" . $filter_array[0] . "' BETWEEN SUBSTRING_INDEX(ps.specification,'-',1) AND SUBSTRING_INDEX(ps.specification,'-',-1) AND ps" . $specifications_id . ".specifications_id = '" . $specifications_id . "' AND ps" . $specifications_id . ".language_id = '" . (int) $languages_id . "' "; break; case 'start': if (strlen ($products_column_name) > 1) { $sql_array['where'] .= " and " . $products_column_name . " like '" . $filter_array[0] . "%' "; } else { $sql_array['from'] .= " INNER JOIN " . TABLE_PRODUCTS_SPECIFICATIONS . " ps" . $specifications_id . " ON p.products_id = ps" . $specifications_id . ".products_id "; $sql_array['where'] .= " AND ps" . $specifications_id . ".specification LIKE '" . $filters_array[0] ."%' AND ps" . $specifications_id . ".specifications_id = '" . $specifications_id . "' AND ps" . $specifications_id . ".language_id = '" . (int) $languages_id . "' "; } break; case 'partial': if (strlen ($products_column_name) > 1) { $sql_array['where'] .= " and " . $products_column_name . " like '%" . $filter_array[0] . "%' "; } else { $sql_array['from'] .= " INNER JOIN " . TABLE_PRODUCTS_SPECIFICATIONS . " ps" . $specifications_id . " ON p.products_id = ps" . $specifications_id . ".products_id "; $sql_array['where'] .= " AND ps" . $specifications_id . ".specification like '%" . $filters_array[0] ."%' AND ps" . $specifications_id . ".specifications_id = '" . $specifications_id . "' AND ps" . $specifications_id . ".language_id = '" . (int) $languages_id . "' "; } break; case 'like': // Function currently uses 'sounds like' to do a soundex match if (strlen ($products_column_name) > 1) { $sql_array['where'] .= " and " . $products_column_name . " sounds like '%" . $filter_array[0] . "%' "; } else { $sql_array['from'] .= " INNER JOIN " . TABLE_PRODUCTS_SPECIFICATIONS . " ps" . $specifications_id . " ON p.products_id = ps" . $specifications_id . ".products_id "; $sql_array['where'] .= " AND ps" . $specifications_id . ".specification sounds like '" . $filters_array[0] ."' AND ps" . $specifications_id . ".specifications_id = '" . $specifications_id . "' AND ps" . $specifications_id . ".language_id = '" . (int) $languages_id . "' "; } break; case 'none': case '': default: break; } // switch ($filter_class } // if (count ($filter_array) ... else ... return $sql_array; } Here's how I currently have the tep_show_filter_count() function setup, it still needs to be cleaned up & re-factored, but it gets the job done. function tep_show_filter_count($current_category_id, $specification, $specification_id, $filter_class, $products_column_name, $languages_id = '1') { $raw_query_start = "select count(p.products_id) as count "; $raw_query_from = " from (products p) join (products_to_categories p2c) on (p.products_id = p2c.products_id) "; $raw_query_where = " where p.products_status = '1' and p2c.categories_id = '" . $current_category_id . "' "; $raw_query_addon = tep_get_filter_sql2($filter_class, $specification_id, $specification, $products_column_name, '1'); $raw_query_from .= $raw_query_addon['from']; $raw_query_where .= $raw_query_addon['where']; // Loop through the $_GET vars looking for any set filters. foreach($_GET as $k => $v) { if (preg_match('/^f[0-9]+$/', $k)) { // We found a var that smells like a filter; append the filtered sql so we can count it. $k = str_replace('f', '', $k); if ($v == $specification) { continue; } // Ugly hack job to store the specs array since we don't need to query it over and over again for each filter. // Eventually move this whole function into an object to store states like this. if (!isset($GLOBALS['specs'][$k])) { $specs_query_raw = "select s.specifications_id, s.products_column_name, s.filter_class, s.filter_show_all, s.filter_display, sd.specification_name, sd.specification_prefix, sd.specification_suffix from " . TABLE_SPECIFICATION . " s, " . TABLE_SPECIFICATION_DESCRIPTION . " sd, " . TABLE_SPECIFICATION_GROUPS . " sg, " . TABLE_SPECIFICATIONS_TO_CATEGORIES . " s2c where s.specification_group_id = sg.specification_group_id and sg.specification_group_id = s2c.specification_group_id and sd.specifications_id = s.specifications_id and s2c.categories_id = '" . $current_category_id . "' and s.show_filter = 'True' and sg.show_filter = 'True' and sd.language_id = '" . $languages_id . "' and s.specifications_id = '" . $k . "' "; $specs_query = tep_db_query ($specs_query_raw); $specs_array = tep_db_fetch_array($specs_query); $GLOBALS['specs'][$k] = $specs_array; } else { $specs_array = $GLOBALS['specs'][$k]; } $raw_query_addon = tep_get_filter_sql2($specs_array['filter_class'], $specs_array['specifications_id'], $v, $specs_array['products_column_name'], $languages_id); $raw_query_from .= $raw_query_addon['from']; $raw_query_where .= $raw_query_addon['where']; } // if (preg_match } // foreach($_GET $raw_query = $raw_query_start . $raw_query_from . $raw_query_where; $filter_count_query = tep_db_query($raw_query); $filter_count_results = tep_db_fetch_array($filter_count_query); $count = (string)$filter_count_results['count']; return $count; }
  8. Yarhajile

    [Development] Products Specifications

    Rewrote the tep_get_filter_sql() function to use inner joins instead of sub-selects and I see a HUGE performance gain, such that caching the counts wont be necessary. I haven't tested all of the filter classes, but they should be functional. The "multiple" class would be the one that has any problems since it's more complicated. I currently have it named tep_get_filter_sql2() since everything that makes a call to that function will need to be re-worked to use the different return value (array instead of string). I had to do this since the current implementation simply appends the generated $sql_string to the end of the query in its "where" clause and in order to use INNER JOIN's, we need to also append the join to the "from" clause. This function spits out an array containing the 'from' and 'where' strings so the code requesting the functions output can put those two clauses where they need to be in the query. ///// // Generate the SQL to return the filtered values function tep_get_filter_sql2 ($filter_class, $specifications_id, $filter_array = array(), $products_column_name, $languages_id) { $sql_array = array('from' => '', 'where' => ''); if (isset($_GET['f' . $specifications_id])){ return $sql_array; } $filter_array = (is_array ($filter_array) ) ? $filter_array : array ($filter_array); // If the Show All option is set, return a blank string if (isset ($filter_array[0]) && ($filter_array[0] == '0' || $filter_array[0] == '') ) { return $sql_array; } else { // Scrub the filter array so apostrophes in filters don't error out. foreach($filter_array as $filterKey => $filterValue) { $filter_array[$filterKey] = tep_db_input($filterValue); } // The Manufacturer's column contains an ID and not the name, so we have to change it if ($products_column_name == 'manufacturers_id') { $filter_array = tep_get_manufacturer_id ($filter_array, $products_column_name); $products_column_name = 'p.' . $products_column_name; } switch ($filter_class) { case 'exact': foreach ($filter_array as $filter) { if (isset ($filter) && $filter != '0' && $filter != '') { if (strlen ($products_column_name) > 1) { // Use an existing column $sql_array['where'] .= " AND " . $products_column_name . " = '" . $filter . "' "; } else { $sql_array['from'] .= " INNER JOIN " . TABLE_PRODUCTS_SPECIFICATIONS . " ps" . $specifications_id . " ON p.products_id = ps" . $specifications_id . ".products_id "; $sql_array['where'] .= " AND ps" . $specifications_id . ".specification = '" . $filter ."' AND ps" . $specifications_id . ".specifications_id = '" . $specifications_id . "' AND ps" . $specifications_id . ".language_id = '" . (int) $languages_id . "' "; } // if (strlen ($products_column_name ... else ... } // if (isset ($filter } // foreach ($filter_array break; case 'multiple': if (strlen ($products_column_name) > 1) { $sql_array['where'] .= " and " . $products_column_name . " in ("; $first = true; foreach ($filter_array as $filter) { if ($first == true) { $first = false; $sql_array['where'] .= " '" . $filter . "' "; } else { $sql_array['where'] .= ", '" . $filter . "' "; } } $sql_array['where'] .= ") "; } else { $sql_array['from'] .= " INNER JOIN " . TABLE_PRODUCTS_SPECIFICATIONS . " ps" . $specifications_id . " ON p.products_id = ps" . $specifications_id . ".products_id "; $first = true; foreach ($filter_array as $filter) { if ($filter != '0') { if ($first == true) { $first = false; $sql_array['where'] .= " AND ps" . $specifications_id . ".specification in ('" . $filter . "' "; } else { $sql_array['where'] .= ", '" . $filter . "' "; } } } $sql_array['where'] .= ") AND ps" . $specifications_id . ".specifications_id = '" . $specifications_id . "' AND ps" . $specifications_id . ".language_id = '" . (int) $languages_id . "' "; foreach ($filter_array as $filter) { if ($filter == '0') { $sql_array = array('from' => '', 'where' => ''); } } } break; case 'range': $filters_range = explode ('-', $filter_array[0]); if (strlen ($products_column_name) > 1) { if (count ($filters_range) < 2) { $sql_array['where'] .= " and " . $products_column_name . " > '" . $filters_range[0] . "'"; } else { $sql_array['where'] .= " and (" . $products_column_name . " between '" . $filters_range[0] . "' and '" . $filters_range[1] . "')"; } } else { if (count ($filters_range) < 2) { // There is only one parameter, so it is a minimum $sql_array['from'] .= " INNER JOIN " . TABLE_PRODUCTS_SPECIFICATIONS . " ps" . $specifications_id . " ON p.products_id = ps" . $specifications_id . ".products_id "; $sql_array['where'] .= " AND ps" . $specifications_id . ".specification > '" . $filters_range[0] ."' AND ps" . $specifications_id . ".specifications_id = '" . $specifications_id . "' AND ps" . $specifications_id . ".language_id = '" . (int) $languages_id . "' "; } else { // There are two parameters, so treat them as minimum and maximum $sql_array['from'] .= " INNER JOIN " . TABLE_PRODUCTS_SPECIFICATIONS . " ps" . $specifications_id . " ON p.products_id = ps" . $specifications_id . ".products_id "; $sql_array['where'] .= " AND (ps" . $specifications_id . ".specification between '" . $filters_range[0] ."' and '" . $filters_range[1] . "') AND ps" . $specifications_id . ".specifications_id = '" . $specifications_id . "' AND ps" . $specifications_id . ".language_id = '" . (int) $languages_id . "' "; } } break; case 'reverse': // No existing columns are set up as a reverse range, so this filter class has no provision for existing columns $sql_array['from'] .= " INNER JOIN " . TABLE_PRODUCTS_SPECIFICATIONS . " ps" . $specifications_id . " ON p.products_id = ps" . $specifications_id . ".products_id "; $sql_array['where'] .= " AND '" . $filter_array[0] . "' BETWEEN SUBSTRING_INDEX(ps.specification,'-',1) AND SUBSTRING_INDEX(ps.specification,'-',-1) AND ps" . $specifications_id . ".specifications_id = '" . $specifications_id . "' AND ps" . $specifications_id . ".language_id = '" . (int) $languages_id . "' "; break; case 'start': if (strlen ($products_column_name) > 1) { $sql_array['where'] .= " and " . $products_column_name . " like '" . $filter_array[0] . "%' "; } else { $sql_array['from'] .= " INNER JOIN " . TABLE_PRODUCTS_SPECIFICATIONS . " ps" . $specifications_id . " ON p.products_id = ps" . $specifications_id . ".products_id "; $sql_array['where'] .= " AND ps" . $specifications_id . ".specification LIKE '" . $filters_array[0] ."%' AND ps" . $specifications_id . ".specifications_id = '" . $specifications_id . "' AND ps" . $specifications_id . ".language_id = '" . (int) $languages_id . "' "; } break; case 'partial': if (strlen ($products_column_name) > 1) { $sql_array['where'] .= " and " . $products_column_name . " like '%" . $filter_array[0] . "%' "; } else { $sql_array['from'] .= " INNER JOIN " . TABLE_PRODUCTS_SPECIFICATIONS . " ps" . $specifications_id . " ON p.products_id = ps" . $specifications_id . ".products_id "; $sql_array['where'] .= " AND ps" . $specifications_id . ".specification like '%" . $filters_array[0] ."%' AND ps" . $specifications_id . ".specifications_id = '" . $specifications_id . "' AND ps" . $specifications_id . ".language_id = '" . (int) $languages_id . "' "; } break; case 'like': // Function currently uses 'sounds like' to do a soundex match if (strlen ($products_column_name) > 1) { $sql_array['where'] .= " and " . $products_column_name . " sounds like '%" . $filter_array[0] . "%' "; } else { $sql_array['from'] .= " INNER JOIN " . TABLE_PRODUCTS_SPECIFICATIONS . " ps" . $specifications_id . " ON p.products_id = ps" . $specifications_id . ".products_id "; $sql_array['where'] .= " AND ps" . $specifications_id . ".specification sounds like '" . $filters_array[0] ."' AND ps" . $specifications_id . ".specifications_id = '" . $specifications_id . "' AND ps" . $specifications_id . ".language_id = '" . (int) $languages_id . "' "; } break; case 'none': case '': default: break; } // switch ($filter_class } // if (count ($filter_array) ... else ... return $sql_array; } Elijah
  9. Yarhajile

    [Development] Products Specifications

    I'll see what I can do to get that hammered out tomorrow. I'm hoping that reworking how the queries are performed will reduce the slower page load times associated with the filter counting when caching isn't involved. I still don't see a GOOD way to cache every possible filter combination for a given category under the test (and likely live) scenario I'm throwing at it. I have a simple recursive function that generates a list of every possible combination for a group of 8 spec groups with 4 having 5 filters and 4 having between 20 and 100 filters. Somewhere around 52 million possible combination's that the filters could be arranged in (and that's with no duplicates like a+b & b+a as they produce the same results). Something doesn't feel right about performing 52M db selects & then another 52M inserts into whatever caching engine is used. Plus the fact that the index would need to be refreshed each time a product or filter was added or removed. I wonder how the sites like newegg.com and bestbuy.com do it? Elijah
  10. Yarhajile

    [Development] Products Specifications

    Ran into something this evening that I thought would be worth asking about. I noticed that the tep_get_filter_sql() function generates sql strings using sql sub-selects. I was curious to whether consideration had been made to using INNER JOIN's instead of the notoriously ill-performing sub-selects? I see a pretty substantial difference in the query plans between the two methods... Method 1 (sub-selects) explain select count(p.products_id) as count from (products p) join (products_to_categories p2c ) on (p.products_id = p2c.products_id) where p.products_status = '1' and p2c.categories_id = '1668' and p.products_id in (select products_id from products_specifications where specification = 'Boys' and specifications_id = '29' and language_id = '1' ) and p.products_id in (select products_id from products_specifications where specification = 'Preschool' and specifications_id = '31' and language_id = '1' ); output copied from phpMyAdmin, rows returned are bolded: 1 PRIMARY p2c ref PRIMARY,products_id,categories_id categories_id 3 const 3594 1 PRIMARY p eq_ref PRIMARY,products_status PRIMARY 5 partyworks_ms2max.p2c.products_id,const 1 Using where; Using index 3 DEPENDENT SUBQUERY products_specifications ref products_id,specification specification 258 const 4 Using where 2 DEPENDENT SUBQUERY products_specifications ref products_id,specification specification 258 const 2 Using where Method 2 (Inner join) explain select count(p.products_id) as count from (products p) join (products_to_categories p2c ) on (p.products_id = p2c.products_id) INNER JOIN products_specifications ps1 ON p.products_id = ps1.products_id INNER JOIN products_specifications ps2 ON p.products_id = ps2.products_id where p.products_status = '1' and p2c.categories_id = '1668' and ps1.specification = 'Boys' and ps1.specifications_id = '29' and ps1.language_id = '1' and ps2.specification = 'Preschool' and ps2.specifications_id = '31' and ps2.language_id = '1' output copied from phpMyAdmin, rows returned are bolded: 1 SIMPLE ps1 ref products_id,specification specification 258 const 2 Using where 1 SIMPLE p eq_ref PRIMARY,products_status PRIMARY 5 partyworks_ms2max.ps1.products_id,const 1 Using index 1 SIMPLE p2c ref PRIMARY,products_id,categories_id products_id 3 partyworks_ms2max.p.products_id 4 Using where 1 SIMPLE ps2 ref products_id,specification specification 258 const 4 Using where Stacking filters on top of each-other like that would need a bit more work in that function to keep the inner join aliases unique (ps1, ps2, etc), but I think the streamlined queries would be worth it. Just my $.02 Elijah
  11. Yarhajile

    [Development] Products Specifications

    I'll keep going in the direction I'm in then since having the counts / disabled empty filters is something of a necessity for our shop. Getting this project off the ground and implemented into our live setup has been turned into a priority, so I have nothing but time and will to continue on! ^_^ I'm thinking that the counting mechanism might be best served up as an object. Keeping track of certain states outside the scope of the simple function would reduce the query load down even further without the need for global vars or returning back extra data from the counting function & re-digesting it on the next filter count ( i.e. re-fetching the specification data for each filter set in $_GET so it can be applied to tep_get_filter_sql() ). Would keep things nice and neat.
  12. Yarhajile

    [Development] Products Specifications

    Looking at what other e-commerce setups do for their specifications implementation, it would appear that caching the combination's of filters to get the counts may be the best way to do it, or at least the popular way. So from a cron job or manual option, refreshing the indices could be done whenever needed. I'm open to ideas for caching the filter counts since I'm sure a pre-built solution would be better than the quick hack I did. I use an md5 hash of a serialized array containing the spec filters that would normally be sent to the tep_get_filter_sql() function and compare that to a database table containing that key and the count associated with it. If the key isn't found, then it performs the queries generated by tep_get_filter_sql() and inserts the found count into the database table so the next time that exact filter combination is chosen, the cached count is returned. It works pretty well, would just need to write something up that generates all possible filter combination's to include in the index. Elijah
  13. Yarhajile

    [Development] Products Specifications

    Wondering if you have given any more thought or have come up with a better solution for the filter counts. I'm running into something of a problem with the code I've been using in that the sql queries performed grows substantially depending on the amount of filters you have. For instance, I'm testing a category that contains 3000 products. This category has two spec groups associated with it. One with a spec that has 100 filters (pulldown)and a spec that has 50 filters (pulldown). The other spec group has 3 specs that have 5 filters (Link Lists) each. For each filter, an sql query needs to be ran to count how many products exist. Those queries change each time a filter is added so that you only see the products that would be visible if you were to select that filter, so you have an enormous amount of possible query configurations for each of the 165 (in our case) filters. I can cache the results for a particular combination which yields me a full 3 second increase in page load time, bringing it to almost instantaneous since I'm not needing to perform the subqueries to get accurate filter counts. But keeping the cache fresh means another set of issues to work with that don't need to be discussed here right yet. So in a round-about sort of way of asking, did you have something in mind for performing those counts that wouldn't involve copious amounts of caching or an enormous amount of timely sql queries? I'm considering going back to the drawing board on this otherwise. :P Thanks! Elijah
  14. Yarhajile

    [Development] Products Specifications

    Made a few corrections, was just meant to share the rough draft to see if it was going in the right direction. Removed, they just weren't cleaned up before I posted it here. The function as I posted it used a mix of both the global $current_category_id and the $categoryId that was being passed, which were both the same thing. *fixed I didn't even look in the admin functions to see if it wasn't already used. It's been renamed to tep_show_filter_count() What would you suggest then for pulling the variables out of the $_GET array? Performing the query in lines 39-71 and comparing it to the $_GET vars? Sounds like a better plan to me. This one is tough. I'll do what I can though! Here's the modified version that should mostly comply, other than #3 & #4. function tep_show_filter_count($current_category_id, $specification, $specification_id, $filter_class, $products_column_name, $languages_id) { $raw_query = "select count(p.products_id) as count from (products p) join (products_to_categories p2c) on (p.products_id = p2c.products_id) where p.products_status = '1' and p2c.categories_id = '" . $current_category_id . "' "; $raw_query .= tep_get_filter_sql($filter_class, $specification_id, $specification, $products_column_name, '1'); // Loop through the $_GET vars looking for any set filters. foreach($_GET as $k => $v) { if (preg_match('/^f[0-9]+$/', $k)) { // We found a var that smells like a filter; append the filtered sql so we can count it. $k = str_replace('f', '', $k); $specs_query_raw = "select s.specifications_id, s.products_column_name, s.filter_class, s.filter_show_all, s.filter_display, sd.specification_name, sd.specification_prefix, sd.specification_suffix from " . TABLE_SPECIFICATION . " s, " . TABLE_SPECIFICATION_DESCRIPTION . " sd, " . TABLE_SPECIFICATION_GROUPS . " sg, " . TABLE_SPECIFICATIONS_TO_CATEGORIES . " s2c where s.specification_group_id = sg.specification_group_id and sg.specification_group_id = s2c.specification_group_id and sd.specifications_id = s.specifications_id and s2c.categories_id = '" . $current_category_id . "' and s.show_filter = 'True' and sg.show_filter = 'True' and sd.language_id = '" . $languages_id . "' and s.specifications_id = '" . $k . "' "; $specs_query = tep_db_query ($specs_query_raw); $specs_array = tep_db_fetch_array($specs_query); $raw_query .= tep_get_filter_sql($specs_array['filter_class'], $specs_array['specifications_id'], $v, $specs_array['products_column_name'], $languages_id); } // if (preg_match } // foreach($_GET $filter_count_query = tep_db_query($raw_query); $filter_count_results = tep_db_fetch_array($filter_count_query); $count = (int)$filter_count_results['count']; return $count; } Elijah
×