Specify custom logic in Thinking Sphinx query?

I have a Thinking Sphinx (V3) index that looks something like this (I'm using Rails 3.2):

ThinkingSphinx::Index.define(:search_table, :with => :active_record) do

    # fields
    indexes public

    # attributes
    has members.id, as: :members
end

The key elements of the search_table index are a boolean field public, and a has_many attribute of integers, named members.

Assume a member_id value exists in the controller. I'd like to fetch all results in a Thinking Sphinx query that satisfy the following predicate:

public = true OR (public = false AND (members CONTAINS member_id))

I have experimented with specifying custom SQL in the Thinking Sphinx query, but I'm stuck. Can anyone point me in the right direction?

Answers


Three parts to this:

Firstly, you need to have public as an attribute rather than a field:

has public

Then, you can construct a dynamic attribute for your search query, using Sphinx's IN function (and note that Sphinx stores true/false as 1/0):

SearchTable.search(
  select: "*, public = 1 OR IN(members, #{member.id} AS public_or_member"
)

The last step is to filter by that dynamic attribute:

SearchTable.search(
  select: "*, public = 1 OR IN(members, #{member.id} AS public_or_member",
  with:   {public_or_member: true}
)

Unrelated, but if your example is an accurate portrayal of your model/database, I would highly recommend changing the name of the public column to something else, as public is a keyword in Ruby, and could lead to bugs/confusion.


Need Your Help

Telerik gridview export images not working winforms

c# image winforms export telerik-grid

I am trying to export the following gridview (winforms project) with Telerik export library:

Android BitmapFactory.decodeResource out of memory if used more than once

android bitmap out-of-memory bitmapfactory

I'm re-writing an Android app where each activity (there are several) displays a background image. The user may change this image and so I've done the following:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.