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

Question

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

Answer 1

The sphinx stuff is mostly correct. The nils in the searches are superfluous. But I honestly don't think that's what's causing your problem.

I think your problem stems from your model relationships. Particularly your hacked together version of Single Table Inheritance (STI), and the way Sphinx handles indexes.

It seems that you are essentially trying to duplicate an index so it ignores the second one.

I'm not entirely sure how to fix it, adding where queries to the define_index block doesn't work because all where statements apply to all indexes defined afterwards and cannot be over written, only added to. Thinking sphinx also doesn't work well with multiple indexes on a model, so you can't fix it by redefining the define_index block.

Thinking-Sphinx 1.2 provides Sphinx Scopes, which you may want to look into as a potential solution. Unfortunately it's poorly documented, so I can't tell if it will work or not.

Given the lack of hits with regard to googlings of Thinking-Sphinx STI and "Single Table Inheritance". I expect redefining your relationships, letting rails handle the STI, to solve your problems.

It involves doing something like this:

class ProductAttribute < ActiveRecord::Base
  belongs_to :product
  inheritance_column => :key
  ...
  common methods and validations to objective\_lens\_diameters and and magnifications
  ...
end

class Magnification < ProductAttribute
  ...
  methods and validations unique to magnifications.
  ...
end

class ObjectLensDiameter < ProductAttribute
  ...
  methods and validations unique to object lens diameters
  ...
end

class Product < ActiveRecord::Base
  has_one :magnification
  has_one :objective_lens

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

You may need to to a migration to bring existing key values in the ProductAttributes table inline with what the STI expects them to be.

Answer 2

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.

Answer 3

I figured a work around until the sql_query associations gets fixed. The performance is worse than using the left joins but at the same time it reduces the amount of external code needed to make it work.

So the define index was changed to use the SQL fragment to get the specific column directly instead of relying on any joins.

define_index do
  has "(SELECT `value` " +
    "FROM `product_attributes` " +
    "WHERE `product_id` = `products`.`id` " +
    "  AND `key` = 'Magnification' " +
    "LIMIT 0, 1)", 
    :type => :float,
    :as => :magnification
  has "(SELECT `value` " +
    "FROM `product_attributes` " + 
    "WHERE `product_id` = `products`.`id` " +
    "  AND `key` = 'Objective Lens Diameter' " +
    "LIMIT 0, 1)", 
    :type => :float,
    :as => :objective_lens_diameter
end

Generated sql_query

SELECT `products`.`id` * 2 + 1 AS `id`,
  `products`.`id` AS `sphinx_internal_id`,
  123456789 AS `class_crc`,
  IFNULL('987654321', 0) AS `subclass_crcs`,
  0 AS `sphinx_deleted`,
  (SELECT `value`
    FROM `product_attributes`
    WHERE `product_id` = `products`.`id`
      AND `key` = 'Magnification'
    LIMIT 0, 1) AS `magnification`,
  (SELECT `value`
    FROM `product_attributes`
    WHERE `product_id` = `products`.`id`
      AND `key` = 'Objective Lens Diameter'
    LIMIT 0, 1) AS `objective_lens_diameter`
FROM `products`
WHERE `products`.`id` >= $start
  AND `products`.`id` <= $end
GROUP BY `products`.`id`
ORDER BY NULL