Problem with variant filters(type = multi and facet = true)

[Ranjithkumar]

Hi

I am using sphinx 2.0.4, spree 0.70.4 and Ruby 1.9.3.

when I am doing,

searcher = Spree::Search::ThinkingSphinx.new({:keywords => "rub*"}) products = searcher.retrieve_products

I am getting this error, NoMethodError: undefined method SELECT DISTINCT p.id, ov.id FROM option_values AS ov LEFT JOIN option_types AS ot ON (ov.option_type_id = ot.id) LEFT JOIN option_values_variants AS ovv ON (ovv.option_value_id = ov.id) LEFT JOIN variants AS v ON (ovv.variant_id = v.id) LEFT JOIN products AS p ON (v.product_id = p.id) WHERE (ot.name = 'color' AND p.id>=$start AND p.id<=$end); SELECT IFNULL(MIN(id), 1), IFNULL(MAX(id), 1) FROMproducts`' for #Product:0x007fb905921d08

from /Users/admin/.rvm/gems/ruby-1.9.3-p0/gems/activemodel-3.1.4/lib/active_model/attribute_methods.rb:385:in method_missing' from /Users/admin/.rvm/gems/ruby-1.9.3-p0/gems/activerecord-3.1.4/lib/active_record/attribute_methods.rb:60:inmethod_missing' from /Users/admin/.rvm/gems/ruby-1.9.3-p0/gems/activesupport-3.1.4/lib/active_support/core_ext/object/try.rb:32:in try' from /Users/admin/.rvm/gems/ruby-1.9.3-p0/gems/thinking-sphinx-2.0.11/lib/thinking_sphinx/facet.rb:110:intranslate' from /Users/admin/.rvm/gems/ruby-1.9.3-p0/gems/thinking-sphinx-2.0.11/lib/thinking_sphinx/facet.rb:82:in value' from /Users/admin/.rvm/gems/ruby-1.9.3-p0/gems/thinking-sphinx-2.0.11/lib/thinking_sphinx/facet_search.rb:127:inblock in add_from_results'

Any idea?

[pat]

Hi there

What does your define_index block look like for the model?

Pat

On 09/05/2012, at 4:20 PM, Ranjithkumar Ravi wrote:

Hi

I am using sphinx 2.0.4, spree 0.70.4 and Ruby 1.9.3.

when I am doing,

searcher = Spree::Search::ThinkingSphinx.new({:keywords => "rub*"}) products = searcher.retrieve_products

I am getting this error, NoMethodError: undefined method SELECT DISTINCT p.id, ov.id FROM option_values AS ov LEFT JOIN option_types AS ot ON (ov.option_type_id = ot.id) LEFT JOIN option_values_variants AS ovv ON (ovv.option_value_id = ov.id) LEFT JOIN variants AS v ON (ovv.variant_id = v.id) LEFT JOIN products AS p ON (v.product_id = p.id) WHERE (ot.name = 'color' AND p.id>=$start AND p.id<=$end); SELECT IFNULL(MIN(id), 1), IFNULL(MAX(id), 1) FROMproducts`' for #Product:0x007fb905921d08

from /Users/admin/.rvm/gems/ruby-1.9.3-p0/gems/activemodel-3.1.4/lib/active_model/attribute_methods.rb:385:in method_missing' from /Users/admin/.rvm/gems/ruby-1.9.3-p0/gems/activerecord-3.1.4/lib/active_record/attribute_methods.rb:60:inmethod_missing' from /Users/admin/.rvm/gems/ruby-1.9.3-p0/gems/activesupport-3.1.4/lib/active_support/core_ext/object/try.rb:32:in try' from /Users/admin/.rvm/gems/ruby-1.9.3-p0/gems/thinking-sphinx-2.0.11/lib/thinking_sphinx/facet.rb:110:intranslate' from /Users/admin/.rvm/gems/ruby-1.9.3-p0/gems/thinking-sphinx-2.0.11/lib/thinking_sphinx/facet.rb:82:in value' from /Users/admin/.rvm/gems/ruby-1.9.3-p0/gems/thinking-sphinx-2.0.11/lib/thinking_sphinx/facet_search.rb:127:inblock in add_from_results'

Any idea?

---

Reply to this email directly or view it on GitHub: https://github.com/freelancing-god/thinking-sphinx/issues/357

[Ranjithkumar]

Hi

Thanks for your response.

my define_index block is,

define_index do is_active_sql = "(products.deleted_at IS NULL AND products.available_on <= NOW() #{'AND (products.count_on_hand > 0)' unless Spree::Config[:allow_backorders]} )"

option_sql = lambda do |option_name| sql = <<-eos SELECT DISTINCT p.id, ov.id FROM option_values AS ov LEFT JOIN option_types AS ot ON (ov.option_type_id = ot.id) LEFT JOIN option_values_variants AS ovv ON (ovv.option_value_id = ov.id) LEFT JOIN variants AS v ON (ovv.variant_id = v.id) LEFT JOIN products AS p ON (v.product_id = p.id) WHERE (ot.name = '#{option_name}' AND p.id>=$start AND p.id<=$end);

{source.to_sql_query_range}

eos
sql.gsub("\n", ' ').gsub('  ', '')

end

indexes :name indexes :description indexes :meta_keywords indexes :meta_description

indexes variants_including_master(:sku) indexes variants_including_master(:ean)

indexes brand(:value), :as => :brand_property, :facet => true indexes product_properties(:value) indexes properties(:name)

indexes taxons.name, :as => :taxon, :facet => true

has master(:price), :as => :price, :sortable => true

group_by :available_on

has is_active_sql, :as => :is_active, :type => :boolean has option_sql.call('color'), :as => :color_option, :source => :ranged_query, :type => :multi, :facet => true

set_property :delta => true end

Early waiting for your reply :)

[pat]

The issue is that your color_option attribute is also a facet - and facets don't work with SQL snippets.

A workaround that may help, if you're using a recent Thinking Sphinx release (2.0.7 or better): add :all_ints => true to that attribute definition. It should stop Thinking Sphinx from trying to interpret results and just use the (correct) integers that Sphinx has provided.

[Ranjithkumar]

hey

Thanks a lot... Its working fine now :)

[pat]

Great :)