Thinking sphinx, filtering with associations?

Question

I have serious problem with Thinking Sphinx in my Rails application. My app specifications are as follows:

• Rails 3.2.6
• Ruby 1.9.3
• Sphinx 2.0.4 (r3135)
• Thinking Sphinx 2.0.12

In my application I have a Page model and a Offer model and pages have many offers. An index defined in the Page model looks like this:

define_index do

  #other indexes defined here
  #...

  has offers.width, as: :offers_width
  has offers.height, as: :offers_height
  has offers.price, as: :offers_price

  set_property delta: true
end

Then I'm doing a search on the Page model, where pages are selected based on search query and conditions. However, when I'm trying to use a :with filter, Sphinx gave me wrong results.

When I use only one filter, for example price or width, the results are OK, however when I'm trying to combine filters, like price and width, I'm getting results containing offers with the given price OR width, not price AND width.

When I'm searching with ranges, most of the time :with parameters are ranges rather than just integer values.

Edit 1
Query im using:

Page.search Riddle.escape(query), conditions: conditions, with: has_cond, star: true, per_page: Page.per_page, page: page

where has_cond is:

{:offers_height=>[175..200], :offers_width=>[175..200], :offers_price=>[10..80]}

It still gives me pages where ANY offer has height in that range or ANY offer has width in that range or same thing with price.

Answer 1

Ok i found a solution. The reason sphinx gave me wrong results was because he was aggregating all offers inside single page. I had to move index from page to offers, and now query works as intended. My index in offer looks like this:

define_index do

  indexes page(:description), as: :page_description
  indexes page(:address), as: :page_address
  indexes page(:title), as: :page_title
  indexes page(:status), as: :page_status
  indexes page.tags(:name), as: :page_tags_name
  indexes page.category(:id), as: :page_category_id
  indexes page.country(:id), as: :page_country_id
  indexes page(:verified), as: :page_verified

  has page(:id), as: :page_id
  has :width
  has :height
  has :price
end

I need to query sphinx first, and then do active record query.

@sphinx_query = Offer.search Riddle.escape(query), with: has_cond, 
    conditions: conditions, group_function: :attr, group_by: 'page_id', 
    star: true, per_page: Page.per_page, page: page
@pages = Page.find(@sphinx_query.map(&:page_id))

Answer 2

I believe the error is going to be within your Search query itself. I just ran a few tests and everything is working as intended... with ranges and without.

The search code in the controller:

@titles = Title.search(params[:q], 
                              :rank_mode => :bm25,
                              :match_mode => :any,
                              :star => true,
                              :limit => 35,
                              :with_all => {
                                :runtime => [75..100],
                                :year => 1976
                              })

Index code:

class Title < ActiveRecord::Base
    ...
    define_index do
      # fields
      indexes clean_name, :sortable => true
      # attributes
      has id, year, runtime, created_at, updated_at
    end
    ...
end

Search Result (JSON)

{
    resultList: [
        {
            director: "Allan Arkush, Joe Dante",
            id: 34089,
            name: "Hollywood Boulevard",
            rating: "R",
            runtime: 83,
            year: 1976,
            text: "Hollywood Boulevard (1976)",
            identify: "title"
        },
        {
            director: "Patrick M. Wright",
            id: 40875,
            name: "Hollywood High",
            rating: "R",
            runtime: 81,
            year: 1976,
            text: "Hollywood High (1976)",
            identify: "title"
        }
    ],
    resultCount: 2
}

With the project I'm working on, it doesn't use a join table -- but that should not matter. The indexing is stil the same with the exception of a join being performed.