Oct 17, 2009

Quick Tip: Search for null dates in Thinking Sphinx

In one of my projects, when a record is deleted I set a deleted_at field with the timestamp rather than actually destroying it. Because that model is indexed by Thinking Sphinx I want to make sure that it doesn't then show up in any search results (the user says she deleted it, right?) I had thought the way to do that would be as simple as passing an extra condition to my query, but I was wrong.

Instead, what you have to do is create an attribute in your model for the datetime field in question and use :with => to filter it down. It might look like this.

  # Search Index.
define_index do
indexes :name, :sortable => true
indexes :description, :sortable => true

has :deleted_at

set_property :delta => true

In the above, I'm not indexing deleted_at, rather it's set as an attribute. When I do my search, I can then use it as follows:

Foo.search(params[:search], :with => {:deleted_at => 0})

Now why did I use 0 instead of nil? Because Sphinx equates nil values with 0.. you'll find out about that if you ever decide to sort any integers that involve null values..

One other thing I want to mention. In my case, I allow admins to search for all the records, including the deleted ones. If I never wanted those records even indexed I would instead add a where clause to my index itself. ('where "deleted_at IS NOT NULL"') This way I wouldn't have to specify the (:with =>) in my controller.

No comments:

Post a Comment