ThinkingSphinx with_all OR query

Question

I'm trying to return results who's start_date or end_date fall within a date range. I'm using with_all in my TS query like such:

range = (start_date..end_date)
with_all = { start_date: [range], end_date: [range] }

However, this does not work because I believe this will require both the start_date and end_date to fall within the range.

What do I need to do so that it will return results if either the start_date or end_date fall within the range?

Answer 1

After much non Rock related head banging I found a working solution.

srange  = date_range[:start].to_datetime.utc.beginning_of_day.to_i
erange  = date_range[:end].to_datetime.utc.end_of_day.to_i

# * full range falls within start_date & end_date
# * start_date & end_date is within the full range
# * end range is within start_date & end_date
# * start range is within start_date & end_date

sphinx_select = "
*, (IF(
#{srange} >= start_date AND
#{srange} <= end_date AND
#{erange} >= start_date AND
#{erange} <= end_date, 1, 0) +

IF(
start_date >= #{srange} AND
start_date <= #{erange} AND
end_date   >= #{srange} AND
end_date   <= #{erange}, 1, 0) +

IF(
#{srange} <= start_date AND
#{srange} <= end_date AND
#{erange} >= start_date AND
#{erange} <= end_date, 1, 0) +

IF(
#{srange} >= start_date AND
#{srange} <= end_date AND
#{erange} >= start_date AND
#{erange} >= end_date, 1, 0)) as in_date_range"

MyModel.search('',
               select: select,
               where: {"in_date_range" => [1,2,3,4]})