Search code examples
sphinxthinking-sphinx

ThinkinSphinx query not working with sphinx_select with four conditions

I'm trying to use ThinkingSphinx to return records that have a start date within a range OR an end date within the same range, basically any record that starts or ends within this range.

To do this, I am using a computed attribute and sphinx_select as per the documentation in combination with what this post suggests for date ranges, as follows (assume there are two records, record_a starts outside the range, but ends within the range and record_b starts and ends within the range):

with_display = "*, IF(start_at >= #{range_start.to_i}, 1, 0) + " + 
                  "IF(start_at <= #{range_end.to_i}, 1, 0) + " + 
                  "IF(end_at   >= #{range_start.to_i}, 10, 0) + " + 
                  "IF(end_at   <= #{range_end.to_i}, 10, 0) AS display"
{
  sphinx_select: with_display,
  with: {'display' =>  [2, 20, 22]},
}
=> [record_b]

However, if I only use the start_at conditions, I get one record, and if I use only the end_at conditions, it returns both records. 

with_display = "*, IF(start_at >= #{range_start.to_i}, 1, 0) + " + 
                  "IF(start_at <= #{range_end.to_i}, 1, 0)  AS display" 
=> [record_b]

with_display = "*, IF(end_at >= #{range_start.to_i}, 10, 0) + " + 
                  "IF(end_at <= #{range_end.to_i}, 10, 0) AS display"
=> [record_a, record_b]

If I'm understanding this correctly, having all four conditions, should result in both record_a and record_b being returned, since record_a should have a display value of 20, while record_b should have a display value of 22.

Am I missing something?

Solution

  • I just realized my math was wrong, given the cases I want to handle:

    • record_a will have a display of 21
    • record_b will have a display of 22

    What I needed to do was change my array to:

    {
  sphinx_select: with_display,
  with: {'display' =>  [12, 21, 22]},
}

    in order the handle the cases of records that end within the range (21), records that start within range (12), and records than start and end within the range (22)