Simplifying an inequality for an SQL query.

Question

For reference, this question was boggled, so thanks to everyone who helped unboggle it.

I'm trying to locate potential collisions of events in a booking program where events have varying lengths.

(a ≤ x && e ≥ y) || (a ≥ x && e ≤ y) || (a ≤ x && e ≤ y) || (a ≥ x && e ≥ y)

I'm using DataMapper to accomplish this, but the query I had before was wrong. I then figured out the above, but I'm not sure how to translate that into a query. Below is my incorrect query for reference.

class Table
  def is_available?
     return false if (TableBooking.all(:at.lte => params[:at], :at.gt => params[:ending], :ending.gt => params[:at], :ending.gte => params[:at]).count > 0)
  end
end

Answer 1

The inequality you gave can be simplified to

(a ≤ x || a ≥ x) && (e ≤ y || e ≥ y)  

which can be further simplified to

True

That is probably why the code isn't working.

Answer 2

I think what you actually want is this:

(a ≤ x && e ≥ y) || (a ≥ x && e ≤ y) || (a ≤ x && e ≥ x) || (a ≤ y && e ≥ y)

Since that accounts for (corresponding to each group in order above)...

1. a,e includes x,y
2. a,e is included in x,y
3. a,e starts before x,y but doesn't finish before x,y
4. a,e ends after x,y but doesn't start after x,y

The first two portions are the same as your original, but I had to change the second two portions to make it make sense and be correct.

This can actually be simplified as well, because #3 and #4 will automatically catch #1, so you really only need #2-4:

(a ≥ x && e ≤ y) || (a ≤ x && e ≥ x) || (a ≤ y && e ≥ y)