I am building a Ruby on Rails (with PostgreSQL database) website for tracking jackpots, entertainment, and giveaways for casinos.
I currently have a working search for entertainment that uses a range on the search. The entertainment model also uses a range (start_date and end_date). I need a solution where I can provide "blackout dates" so that anyone searching exclusively within those dates will not be shown entertainment that only exists within those dates.
Allow me to provide an example.
* Entertainment #1 starts on the 7th of October, 2012 and ends on the 31st of October, 2012. Entertainment #1 blackout dates are: October 9th, October 10th, and October 11th.
* Entertainment #2 starts on the 7th of October, 2012 and ends on the 31st of October, 2012. Entertainment #2 has no blackout dates.
* User A searches for entertainment happening between October 9th and October 11th. User A only sees Entertainment #2, because of Entertainment #1's blackout dates.
* User B searches for entertainment between October 7th and October 15th. User B is shown both Entertainment #1 and Entertainment #2. Even though there are blackout dates in the middle for Entertainment #1, they do not make up the full duration for the search.
I have a model called EntertainmentBlackout that consists of:
* entertainment_id (integer)
* blackout_date (date)
However, I need someone to help me with the query required to perform this search. I would prefer there would only be 1 query that returns all results needed, instead of having to perform a query on each row that is returned from my existing search functionality.
You will be responsible for the search functionality only. I will take care of GUI elements and model methods to insert dates into the database, as long as the table schema for this doesn't change. If there's a better way to do the table, I am open to suggestions.
If there are any confusions, please ask me a question though the vWorker website here before bidding.