searching multiple polymorphic items using thinking sphinx

Question

I have an attachments table that has multiple values (via polymorphic assoc). I'd like to be able to search for multiple values (like an AND in SQL) via thinking-sphinx.

For example:

class FieldValue < ActiveRecord::Base
  belongs_to :customized, :polymorphic => true
end

class Attachment < ActiveRecord::Base
  has_many  :field_values, :as => :customized, :dependent => :destroy

  define_index do
    has field_values.field_id, :as => :field_ids, :type => :multi

    indexes field_values.value, :as => :field_values, :type => :multi

    set_property :enable_star   => 1
    set_property :min_infix_len => 3
  end
end

My FieldValue model has one field (value), so using the above index definition, I could do something like:

Attachment.search :conditions => { :field_values => ["*5100*", "1"] }, :with => { :field_ids => [23, 24] }

But this doesn't technically do what I'm hoping for. The field_values should match the field_ids (similar to a select * from attachments where (field_value.id = 23 and field_value.value like '*5100*) and (field_value.id = 23 and field_value.value = '1')

(i know there are joins missing above :P)

Is it possible to do a search similar to this?

score 1 · Answer 1 · answered Feb 28 '12 at 22:36

1

Sphinx has no concept of key/value pairs, so you can't ask it to match values and field_ids on given field_values.

If you wanted to search on attachments matching a single value/id pair, a possible way around this would be to search on FieldValue instead, and group by customized_id:

class FieldValue < ActiveRecord::Base
  define_index do
    indexes value
    has field_id, customized_id
  end
end

FieldValue.search :conditions => {:value => '1'}, :with => {:field_id => 23},
  :group_by => 'customized_id', :group_function => :attr

The grouping will ensure you only get one match per Attachment.

However, this is no help matching multiple value/id pairs for an attachment, I'm afraid. I can't think of a way to do that with Sphinx off the top of my head.

answered Feb 28 '12 at 22:36

pat

16,116
5
40
46

I used to use the indexes on the FieldValue to do the searching, but it seemed like what I ended up with was a bit of a 'hack' so was hoping there was an 'official' way to do it. I'm basically trying to replicate something like: ``` define_index do indexes :value_one indexes :value_two indexes :value_three end ``` but since the association is polymorphic, value_[one..three] are defined as key/value (as you noted above). I can work around this using a standard Attachment.search, then individual FieldValue.search calls and merge the results, but that's the hack I want to avoid :P – Alex B. Mar 02 '12 at 13:54
Sorry, tried to edit a bunch of times but forgot you can't really format comments :P – Alex B. Mar 02 '12 at 13:56
Would it be possible to get the results separately and intersect them? See this question: http://stackoverflow.com/questions/30305981/intersect-thinking-sphinx-results – Jonathan May 18 '15 at 14:48

score 0 · Answer 2 · edited May 23 '17 at 11:51

This can be done in several steps.

You'll need to reference FieldValue's ids in the Attachment index:

has field_values.id, :as => :field_values_ids
Get the ids for the FieldValue pairs you need:

v1 = FieldValue.search_for_ids(conditions: { value: "*5100*" }, with: { field_id: 23 })

v2 = FieldValue.search_for_ids(conditions: { value: "1" }, with: { field_id: 24 })
Search for Attachments that have these pairs:

r1 = Attachment.search(with: { field_values_ids: v1 })

r2 = Attachment.search(with: { field_values_ids: v2 })
Intersect the results:

r1.to_a & r2.to_a

Caveat: you lose pagination and ordering (See Pat's answer to my question).

searching multiple polymorphic items using thinking sphinx

2 Answers2

Linked