Search code examples
ruby-on-railsthinking-sphinx

How do you search for different conditions from the same join table?

With these associations only the magnification results returns the correct results, but when I try and search for the second association it will return 0 results.

has_one :magnification,
  :class_name => 'ProductAttribute',
  :foreign_key => 'product_id',
  :conditions => {:key => 'Magnification'}
has_one :objective_lens,
  :class_name => 'ProductAttribute',
  :foreign_key => 'product_id',
  :conditions => {:key => 'Objective Lens Diameter'}

define_index do
  has magnification(:value), :type => :float, :as => :magnification
  has objective_lens(:value), :type => :float, :as => :objective_lens_diameter
end

Sample Code Used

# returns expected results
Product.search(nil, :with => {:magnification => (8.0..9.0)})

# returns 0 results
Product.search(nil, :with => {:objective_lens_diameter => (31.0..61.0)})

But when I reverse the order of the define_index the opposite occurs. So the objective lens diameter results returns the right results and the magnification results returns 0.

Using Rails v2.2, Thinking-Sphinx as a plugin v1.2.12 and Sphinx 0.9.8

Edit: Looking at the generated sql_query value shows the 2nd attribute's join uses the wrong associations so it will not return the expected results.

Simplified results:

SELECT
  `products`.`id` * 2 + 1 AS `id`,
  `products`.`id` AS `sphinx_internal_id`,
  1234567890 AS `class_crc`,
  `product_attributes`.`value` AS `magnification`,
  `objective_lens_products`.`value` AS `objective_lens_diameter`
FROM `products`
  LEFT OUTER JOIN `product_attributes` ON product_attributes.product_id = products.id
    AND `product_attributes`.`key` = 'Magnification'
  LEFT OUTER JOIN `product_attributes` objective_lens_products ON objective_lens_products.product_id = products.id
    AND `product_attributes`.`key` = 'Objective Lens Diameter'
WHERE `products`.`id` >= $start
  AND `products`.`id` <= $end
GROUP BY `products`.`id`
ORDER BY NULL
Solution

  • Can you share the sql_query generated for your Product model inside development.sphinx.conf? Really, what you're doing should work for both attributes, so it could be there's a bug in the generated SQL command.