0x4a6f4672 0x4a6f4672 - 18 days ago 6x
Ruby Question

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

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

..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

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
and thinking-sphinx

pat pat

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