"IS NOT NULL" conditions for associations in Thinking Sphinx search

Question

How do you add an "IS NOT NULL" condition for associations in Thinking Sphinx search? For example if we have an article model which has the following index..

ThinkingSphinx::Index.define :article, :with => :active_record do
  indexes subject, :sortable => true
  indexes content
  has pictures(:id), as: :picture_ids
end

..and we want to search for all articles which contain a certain keyword and have a picture. Articles and pictures are related by a simple has_many relationship

class Article < ActiveRecord::Base
  has_many :pictures, -> { where 'pictures.type' => 'ArticlePicture' }

The following line used to work, as it is described here, but it no longer seems to work :-(

Article.search(keyword, without: {picture_ids: 0})

What is the correct way to do it? I am using Sphinx 2.2.10 and thinking-sphinx 3.2.0

Not a TS expert, but does "has pictures(:id), " imply a relation? Ie the picture ids are loaded from a separate database table. If so possibly two problems, A) ends up running a 'INNER JOIN' rather than 'LEFT JOIN' on the tables, so records without any pictures are simply missing. But also B) if ends up being a Sphinx MVA, then when no pics, is an empty list rather than a list with single 0. — barryhunter, Oct 06 '16 at 11:09

score 2 · Accepted Answer · edited Oct 07 '16 at 07:31

You can add an additional attribute with a SQL snippet:

has "COUNT(DISTINCT pictures.id)", :as => :picture_count, :type => :integer

And then - once you've run rake ts:rebuild - I'd expect the following to work:

Article.search(keyword, :without => {:picture_count => 0})

It's important to note that you'll still need a reference to the pictures association in your index definition to ensure there's a SQL join. This is done by your existing attribute (picture_ids), or otherwise you can force the join using the following line within the index definition:

join pictures

"IS NOT NULL" conditions for associations in Thinking Sphinx search

1 Answers1