Hi Tyler
Can you try the following:
Product.search('+Green +Beans',
:with => {:store_ids => [12, 41]}
).first.sphinx_attributes['store_ids']
This should tell us what Sphinx has stored.
--
Pat
On 22/10/2010, at 9:15 AM, Tyler Smith wrote:
> #Search for data from original Store (id=12)
>>> Product.search('+Green +Beans', :with => {:store_ids => [12]}, :without =>
>>> {}, :per_page => 20, :page =>1).count
> => 20
>
> #Search for data from one of the new stores (id=41)
>>> Product.search('+Green +Beans', :with => {:store_ids => [41]}, :without =>
>>> {}, :per_page => 20, :page =>1).count
> => 0
>
> #Search for data from original Store and new store
>>> Product.search('+Green +Beans', :with => {:store_ids => [12, 41]}, :without
>>> => {}, :per_page => 20, :page =>1).count
> => 20
>
> #Showing stores ids from data taken inside of the the search by old
> store
>>> Product.search('+Green +Beans', :with => {:store_ids => [12, 41]}, :without
>>> => {}, :per_page => 20, :page =>1).first.stores.collect{|s| s.id}
> => [12, 39, 40, 41, 15, 38]
>
> Any thoughts? I'm going to try creating a new product only assigned
> to id 41, reindexing, and try it again. I'll update if that is
> successful.
>
>
> On Oct 18, 8:19 pm, Pat Allan <[email protected]> wrote:
>> Hmm, the right results are coming through... what happens if the store_ids
>> in the filter are [41], or [12, 41]?
>>
>> --
>> Pat
>>
>> On 19/10/2010, at 3:01 AM, Tyler Smith wrote:
>>
>>
>>
>>> Thanks for the recommendation, Here is the response.
>>
>>> mysql> SELECT `products`.`id` AS `product_id`,
>>> -> GROUP_CONCAT(DISTINCT IFNULL(`stores`.`id`, '0') SEPARATOR
>>> ',') AS `store_ids`
>>> -> 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`)
>>> -> WHERE `products`.`id` = 72071
>>> -> GROUP BY `products`.`id`;
>>> +------------+-------------------+
>>> | product_id | store_ids |
>>> +------------+-------------------+
>>> | 72071 | 12,39,40,41,15,38 |
>>> +------------+-------------------+
>>> 1 row in set (0.02 sec)
>>
>>> On Oct 15, 8:31 pm, Pat Allan <[email protected]> wrote:
>>>> Right, not spotting anything obviously wrong there... but let's
>>>> double-check the SQL for that attribute and product. Try running the
>>>> following from script/dbconsole (but keep in mind I've built it without
>>>> testing - as it's your db :)
>>
>>>> SELECT `products`.`id` AS `product_id`,
>>>> GROUP_CONCAT(DISTINCT IFNULL(`stores`.`id`, '0') SEPARATOR ',') AS
>>>> `store_ids`
>>>> 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`)
>>>> WHERE `products`.`id` = 1000
>>>> GROUP BY `products`.`id`;
>>
>>>> What's the stores_id value?
>>
>>>> --
>>>> Pat
>>
>>>> On 16/10/2010, at 2:29 AM, Tyler Smith wrote:
>>
>>>>> 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
>>>>> }
>>
>>>>> On Oct 14, 5:34 pm, Pat Allan <[email protected]> wrote:
>>>>>> Hi Tyler
>>
>>>>>> What does the source for the product model look like in your
>>>>>> config/development.sphinx.conf file? (Make sure you remove your database
>>>>>> password).
>>
>>>>>> Cheers
>>
>>>>>> --
>>>>>> Pat
>>
>>>>>> On 15/10/2010, at 4:42 AM, Tyler Smith wrote:
>>
>>>>>>> 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
>>
>>>>>>> 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 numbered ID is in the array for
>>>>>>> with[:store_ids]. For example, the ids associated with product 1000
>>>>>>> are [12,15,41], if with[:store_ids] = [15,41] then 0 results are
>>>>>>> returned, however if 12 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.
>>
>>>>>>> --
>>>>>>> You received this message because you are subscribed to the Google
>>>>>>> Groups "Thinking Sphinx" group.
>>>>>>> To post to this group, send email to [email protected].
>>>>>>> To unsubscribe from this group, send email to
>>>>>>> [email protected].
>>>>>>> For more options, visit this group
>>>>>>> athttp://groups.google.com/group/thinking-sphinx?hl=en.
>>
>>>>> --
>>>>> You received this message because you are subscribed to the Google Groups
>>>>> "Thinking Sphinx" group.
>>>>> To post to this group, send email to [email protected].
>>>>> To unsubscribe from this group, send email to
>>>>> [email protected].
>>>>> For more options, visit this group
>>>>> athttp://groups.google.com/group/thinking-sphinx?hl=en.
>>
>>> --
>>> You received this message because you are subscribed to the Google Groups
>>> "Thinking Sphinx" group.
>>> To post to this group, send email to [email protected].
>>> To unsubscribe from this group, send email to
>>> [email protected].
>>> For more options, visit this group
>>> athttp://groups.google.com/group/thinking-sphinx?hl=en.
>
> --
> You received this message because you are subscribed to the Google Groups
> "Thinking Sphinx" group.
> To post to this group, send email to [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> For more options, visit this group at
> http://groups.google.com/group/thinking-sphinx?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"Thinking Sphinx" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/thinking-sphinx?hl=en.
