Filter search with combination of 'OR' and 'AND' conditions

[rajesh]

I need to filter search results on the bases of 'OR' condition.I have
indexed Event model like :

Event--has many--EventUsers

define_index do
indexes event.summary
has is_private, created_by
has event_users(:id), :as => :event_ids
has event_users.user_id, :as => :event_user_id
has event_users.status, :as => :event_user_status
end

Now, I need to search somthing like

Event.search "something"

with following conditions
:conditions => ['(events.starts_at >= ? and events.starts_at <= ?)
AND ((events.is_private = 0 OR (events_users.user_id = ? AND
events_users.status = ?)) or (events.created_by = ?))"]

I tried it with :condtions => {} and :with => {} but both generates
the query with 'AND'.

what is the way to implement above conditions?

[Pat Allan]

Hi Rajesh

Sphinx isn't quite as flexible as SQL - and Thinking Sphinx doesn't imitate ActiveRecord quite to the extent you're trying in the :conditions option.

For fields, you can use boolean logic provided you're using :boolean or :extended match modes:

Event.search "something", :match_mode => :extended

For attributes though, the only way to get OR comparisons happening is multiple values in a single attribute (ie: matching records that have any of specified values in each records' collection of values). Mixing ANDs and ORs is even harder - the only real solution is to craft a custom attribute to filter by.

So, if it needs to match either of these criteria:

- events.is_private = 0

- events.created_by = ?

Then maybe a possible attribute is (assuming you're using MySQL):

  has "IF(is_private = 0, 0, created_by)", :as => :owner, :type => :integer

And you could filter using the following:

  Event.search "something", :with => {:owner => [0, @user.id]}

Also, to get the date range working, you'll need to add the starts_at column as an attribute:

  has starts_at, :type => :datetime

  Event.search "something", :with => {

    :owner     => [0, @user.id],

    :starts_at => 1.week.ago.to_i..Time.now.to_i

  }

I realise your query is more complex than this, but I'm going to leave you with the fun job of figuring out an appropriate SQL snippet that is usable for a custom attribute. Keep in mind, Sphinx stores unsigned integers, so -1 is out as a separate indicator. You can return multiple integer values in a single attribute, but they must be separated by commas, and the explicit type will be :multi, not :integer.

The other thing to note is that you can't filter by attributes when they're strings - because Sphinx stores them as integers, and it's extremely difficult to determine what those integers are reliably. I've assumed the status you want to filter by is 'active' - if you want multiple statuses in your custom attribute, it should be possible, but obviously the code is going to get more complex.

I realise this is a lot to think about, but hopefully it helps.

Cheers

-- 

Pat