List of available users for a specific date
I am looking for a booking engine and I need to display all available users on a specific day / time.
With this in mind, I'm going to create a table to remember that people are NOT available, and let's assume they are available in a different way. The structure I had in mind was to keep the date_start annotation when not available and then date_end.
Now the problem comes up when I try to schedule a meeting, if I want to order something on Tuesday at 11:00 am to 1:00 pm, I need to get a list of those people who are available then there are no vacations booked for that time.
Any ideas or thoughts? I will use Ruby on Rails if it helps / hinders any ideas.
source to share
Here's one way to model it. Let's say we have an Interaction model that has a start datetime, end datetime, and a name. The interaction has many users, through another connection table called "user_engagements" (with the corresponding UserEngagement model). So we have
User
has_many :user_engagements
has_many :engagements, :through => :user_engagements
Engagement
#fields - starts_at, ends_at (both datetime)
has_many :user_engagements
has_many :users, :through => :user_engagements
UserEngagement
belongs_to :user
belongs_to :engagement
We now have a simple simple circuit. Interaction basically simulates something that happens and user_engagements simulates the users who are booked to do so. We have an assumption (not written in the code) that when they do something, they cannot do anything.
Our next task is to write a method that returns users over a period of time, that is, a new interaction. So we are helping and we want all users who do not have a participation that crosses our new participation. I think the easiest way to do this is to find all users that have cross-interaction and then return all users that are not. If you know what I mean. A more accurate way of saying e2 intersecting with e1 is that e2 starts before the end of e1 AND ends after the start of e1.
Let's do it as a method of the interaction object as it depends entirely on the interaction data.
#in Engagement
def unavailable_user_ids
User.find(:all, :include => [:user_engagements], :select => "users.id", :conditions => ["user_engagements.starts_at < ? and user_engagements.ends_at > ?", self.ends_at, self.starts_at]).collect(&:id)
end
def available_users
User.find(:all, :conditions => ["id not in (?)", self.unavailable_user_ids])
end
I feel like there is a more efficient way to get this in a single query, but I cannot put my finger on sql.
source to share