Some troubles with facet search

Question

I started develop facet search in my project. As example how it should work I took asos.com. As engine I use mysql for no fulltext search and sphinx for fulltext search. For faster search I denormalized data in db and created special table for this.

CREATE TABLE `item_search` (
  `id` int(10) unsigned NOT NULL DEFAULT '0',
  `item_id` int(10) unsigned NOT NULL DEFAULT '0',
  `tab` tinyint(3) unsigned NOT NULL,
  `designer_id` smallint(5) unsigned NOT NULL,
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `price` decimal(18,2) NOT NULL,
  `arm` tinyint(3) unsigned NOT NULL,
  `bridge` tinyint(3) unsigned NOT NULL,
  `lens_width` tinyint(3) unsigned NOT NULL,
  `rating` int(11) unsigned DEFAULT '0',
  `pageviews` int(11) unsigned DEFAULT '0',
  `category_id` int(4) unsigned NOT NULL,
  KEY `index2` (`item_id`,`tab`,`price`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY LIST (tab)
(PARTITION pg VALUES IN (1) ENGINE = InnoDB,
 PARTITION rx VALUES IN (2) ENGINE = InnoDB,
 PARTITION sun VALUES IN (3) ENGINE = InnoDB,
 PARTITION acc VALUES IN (4) ENGINE = InnoDB)

How to this looks in code.

if (Arr::get($args, 'query')){
  // Get item ids that was found by query
  $args['item_ids'] = Items::findByFullText($args);
}

...//Some code here

if ($item_ids = Arr::get($args, 'item_ids')){
  $where[] = "item_id IN (".implode(',', $item_ids).")";
}

...// Build and execute query and return item_ids

I can send multiple categories in multiple groups to search.

if ($categories = Arr::get($args, 'categories')){
  // group categories by categories group
  $categories = Category::groupCategories($categories);

  foreach ($categories as $group_id => $category_group){
   foreach ($category_group as $category_id){
    $item_ids[$group_id][] = Category::getItemIdsByCategory($category_id);
   }

   // Sum all item ids by this group
   if (sizeof($item_ids[$group_id]) == 1){
    $item_ids_by_group[$group_id] = $item_ids[$group_id][0];
   }else{
    $item_ids_by_group[$group_id] = call_user_func_array('array_merge', $item_ids[$group_id]);
   }
  }

  // Return found item ids by categories
   (sizeof($item_ids_by_group) == 1)
        ? $item_ids_by_group[$group_id]
        : call_user_func_array('array_intersect', $item_ids_by_group);
}

This is works pretty fast if enable cache for results, Only one problem that have here that no have possibility fetch categories like asos.do. What logic should be for returning categories ids for category groups? Many thanks for any response.

Some troubles with facet search

Answers (1)

Related Questions