ThinkinSphinx query not working with sphinx_select with four conditions

Question

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?

score 0 · Accepted Answer · answered Dec 21 '12 at 05:43

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)

ThinkinSphinx query not working with sphinx_select with four conditions

1 Answers1