思考Sphinx没有将相关数据索引到搜索到的模型(Thinking Sphinx not indexing associated data to model searched by)
在我的Product.rb中
define_index do indexes :name, :sortable => true indexes brand has :id, kosher_id, gluten_free_id, lactose_free_id has stores(:id), :as => 'store_ids' has locations(:id), :as => 'location_ids' has categories(:id), :as => 'category_ids' has kosher(:passover), :as => 'kosher_passover' end
在我的搜索课中
def self.for_locations_by_query(query, options = {}) query, categories = @@coder.decode(query).gsub(/(\b\w+\b)/, "+\\0"), @@coder.decode(options.delete(:categories)) origin, page = @@coder.decode(options.delete(:origin)), @@coder.decode(options.delete(:page)) per_page, sort_order = @@coder.decode(options.delete(:per_page)), @@coder.decode(options.delete(:sort_order)) distance, classifications = @@coder.decode(options.delete(:distance)), @@coder.decode(options.delete(:classifications)) with = {} with[:store_ids] = Location.all(:origin => origin, :within => distance).collect(&:store_id).uniq unless origin.blank? or distance.blank? with[:category_ids] = categories.split(',') unless categories.blank? without = {} without[:kosher_id] = 0 if classifications.split(',').include? 'kosher' without[:gluten_free_id] = 0 if classifications.split(',').include? 'gluten_free' without[:lactose_free_id] = 0 if classifications.split(',').include? 'lactose_free' without[:kosher_passover] = 0 if classifications.split(',').include? 'kosher_passover' products = Product.search(query, :with => with, :without => without, :per_page => 20, :page => 1) stores = products.collect { |p| p.stores }.flatten.uniq locations = stores.collect { |s| s.locations.find(:all, :origin => origin, :within => distance) }.flatten.uniq locations.sort_by_distance_from(origin) locations.each { |l| l.dist = l.distance } locations = locations.paginate(:page => page, :per_page => per_page) return { :total_entries => locations.total_entries, :size => locations.size, :locations => locations } end
* .sphinx.conf文件 - 产品型号
source product_core_0 { type = mysql sql_host = [FILTERED] sql_user = [FILTERED] sql_pass = [FILTERED] sql_db = [FILTERED] sql_sock = /var/run/mysqld/mysqld.sock sql_query_pre = SET NAMES utf8 sql_query_pre = SET TIME_ZONE = '+0:00' sql_query = SELECT SQL_NO_CACHE `products`.`id` * 3 + 1 AS `id` , `products`.`name` AS `name`, `products`.`brand` AS `brand`, `products`.`id` AS `sphinx_internal_id`, 485965105 AS `class_crc`, 0 AS `sphinx_deleted`, IFNULL(`products`.`name`, '') AS `name_sort`, `products`.`id` AS `id`, `products`.`kosher_id` AS `kosher_id`, `products`.`gluten_free_id` AS `gluten_free_id`, `products`.`lactose_free_id` AS `lactose_free_id`, GROUP_CONCAT(DISTINCT IFNULL(`stores`.`id`, '0') SEPARATOR ',') AS `store_ids`, GROUP_CONCAT(DISTINCT IFNULL(`locations`.`id`, '0') SEPARATOR ',') AS `location_ids`, GROUP_CONCAT(DISTINCT IFNULL(`categories`.`id`, '0') SEPARATOR ',') AS `category_ids`, `koshers`.`passover` AS `kosher_passover` FROM `products` LEFT OUTER JOIN `product_stores` ON (`products`.`id` = `product_stores`.`product_id`) LEFT OUTER JOIN `stores` ON (`stores`.`id` = `product_stores`.`store_id`) LEFT OUTER JOIN `product_locations` ON (`products`.`id` = `product_locations`.`product_id`) LEFT OUTER JOIN `locations` ON (`locations`.`id` = `product_locations`.`location_id`) LEFT OUTER JOIN `category_products` ON (`products`.`id` = `category_products`.`product_id`) LEFT OUTER JOIN `categories` ON (`categories`.`id` = `category_products`.`category_id`) LEFT OUTER JOIN `koshers` ON `koshers`.id = `products`.kosher_id WHERE `products`.`id` >= $start AND `products`.`id` <= $end GROUP BY `products`.`id` ORDER BY NULL sql_query_range = SELECT IFNULL(MIN(`id`), 1), IFNULL(MAX(`id`), 1) FROM `products` sql_attr_uint = sphinx_internal_id sql_attr_uint = class_crc sql_attr_uint = sphinx_deleted sql_attr_uint = id sql_attr_uint = kosher_id sql_attr_uint = gluten_free_id sql_attr_uint = lactose_free_id sql_attr_bool = kosher_passover sql_attr_str2ordinal = name_sort sql_attr_multi = uint store_ids from field sql_attr_multi = uint location_ids from field sql_attr_multi = uint category_ids from field sql_query_info = SELECT * FROM `products` WHERE `id` = (($id - 1) / 3) } index product_core { source = product_core_0 path = [FILTERED]/releases/20101008215652/db/sphinx/staging/product_core charset_type = utf-8 } index product { type = distributed local = product_core }
有问题的行是
with[:store_ids] = ...
产品可以与多个商店ID相关联,但是如果编号最小的ID
在with[:store_ids]
的数组中,它似乎只能起作用。 例如,与产品1000
相关联的ID为[12,15,41]
,如果with[:store_ids] = [15,41]
则返回0结果,但是如果12
在数组中则可以工作。我已停止,启动,重新启动,重新编制索引,重建,为sphinx生成一个新的conf文件,并以各种顺序重复,我没有运气。
In my Product.rb
define_index do indexes :name, :sortable => true indexes brand has :id, kosher_id, gluten_free_id, lactose_free_id has stores(:id), :as => 'store_ids' has locations(:id), :as => 'location_ids' has categories(:id), :as => 'category_ids' has kosher(:passover), :as => 'kosher_passover' end
In my Searching Class
def self.for_locations_by_query(query, options = {}) query, categories = @@coder.decode(query).gsub(/(\b\w+\b)/, "+\\0"), @@coder.decode(options.delete(:categories)) origin, page = @@coder.decode(options.delete(:origin)), @@coder.decode(options.delete(:page)) per_page, sort_order = @@coder.decode(options.delete(:per_page)), @@coder.decode(options.delete(:sort_order)) distance, classifications = @@coder.decode(options.delete(:distance)), @@coder.decode(options.delete(:classifications)) with = {} with[:store_ids] = Location.all(:origin => origin, :within => distance).collect(&:store_id).uniq unless origin.blank? or distance.blank? with[:category_ids] = categories.split(',') unless categories.blank? without = {} without[:kosher_id] = 0 if classifications.split(',').include? 'kosher' without[:gluten_free_id] = 0 if classifications.split(',').include? 'gluten_free' without[:lactose_free_id] = 0 if classifications.split(',').include? 'lactose_free' without[:kosher_passover] = 0 if classifications.split(',').include? 'kosher_passover' products = Product.search(query, :with => with, :without => without, :per_page => 20, :page => 1) stores = products.collect { |p| p.stores }.flatten.uniq locations = stores.collect { |s| s.locations.find(:all, :origin => origin, :within => distance) }.flatten.uniq locations.sort_by_distance_from(origin) locations.each { |l| l.dist = l.distance } locations = locations.paginate(:page => page, :per_page => per_page) return { :total_entries => locations.total_entries, :size => locations.size, :locations => locations } end
*.sphinx.conf file - product model
source product_core_0 { type = mysql sql_host = [FILTERED] sql_user = [FILTERED] sql_pass = [FILTERED] sql_db = [FILTERED] sql_sock = /var/run/mysqld/mysqld.sock sql_query_pre = SET NAMES utf8 sql_query_pre = SET TIME_ZONE = '+0:00' sql_query = SELECT SQL_NO_CACHE `products`.`id` * 3 + 1 AS `id` , `products`.`name` AS `name`, `products`.`brand` AS `brand`, `products`.`id` AS `sphinx_internal_id`, 485965105 AS `class_crc`, 0 AS `sphinx_deleted`, IFNULL(`products`.`name`, '') AS `name_sort`, `products`.`id` AS `id`, `products`.`kosher_id` AS `kosher_id`, `products`.`gluten_free_id` AS `gluten_free_id`, `products`.`lactose_free_id` AS `lactose_free_id`, GROUP_CONCAT(DISTINCT IFNULL(`stores`.`id`, '0') SEPARATOR ',') AS `store_ids`, GROUP_CONCAT(DISTINCT IFNULL(`locations`.`id`, '0') SEPARATOR ',') AS `location_ids`, GROUP_CONCAT(DISTINCT IFNULL(`categories`.`id`, '0') SEPARATOR ',') AS `category_ids`, `koshers`.`passover` AS `kosher_passover` FROM `products` LEFT OUTER JOIN `product_stores` ON (`products`.`id` = `product_stores`.`product_id`) LEFT OUTER JOIN `stores` ON (`stores`.`id` = `product_stores`.`store_id`) LEFT OUTER JOIN `product_locations` ON (`products`.`id` = `product_locations`.`product_id`) LEFT OUTER JOIN `locations` ON (`locations`.`id` = `product_locations`.`location_id`) LEFT OUTER JOIN `category_products` ON (`products`.`id` = `category_products`.`product_id`) LEFT OUTER JOIN `categories` ON (`categories`.`id` = `category_products`.`category_id`) LEFT OUTER JOIN `koshers` ON `koshers`.id = `products`.kosher_id WHERE `products`.`id` >= $start AND `products`.`id` <= $end GROUP BY `products`.`id` ORDER BY NULL sql_query_range = SELECT IFNULL(MIN(`id`), 1), IFNULL(MAX(`id`), 1) FROM `products` sql_attr_uint = sphinx_internal_id sql_attr_uint = class_crc sql_attr_uint = sphinx_deleted sql_attr_uint = id sql_attr_uint = kosher_id sql_attr_uint = gluten_free_id sql_attr_uint = lactose_free_id sql_attr_bool = kosher_passover sql_attr_str2ordinal = name_sort sql_attr_multi = uint store_ids from field sql_attr_multi = uint location_ids from field sql_attr_multi = uint category_ids from field sql_query_info = SELECT * FROM `products` WHERE `id` = (($id - 1) / 3) } index product_core { source = product_core_0 path = [FILTERED]/releases/20101008215652/db/sphinx/staging/product_core charset_type = utf-8 } index product { type = distributed local = product_core }
The problematic line is
with[:store_ids] = ...
The product can be associated to multiple store ids, however it only seems to work if the lowest numberedID
is in the array forwith[:store_ids]
. For example, the ids associated with product1000
are[12,15,41]
, ifwith[:store_ids] = [15,41]
then 0 results are returned, however if12
is in the array then it works.I have stopped, started, restarted, reindexed, rebuilt, generated a new conf file for sphinx, and repeated in various orders and I have had no luck.
原文:https://stackoverflow.com/questions/3935978