Specify custom logic in Thinking Sphinx query?

Question

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?

Answer 1

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.