列出特定日期的可用用户 [英] Listing available users on a certain date

问题描述

我正在寻找一个预订系统，我需要显示特定日期/时间的所有可用用户.

I'm looking at making a booking system and I need to display all the available users on a particular day/time.

考虑一下，我将建立一个表格以记录何时没有人可用，并假定其他人可用.我想到的结构是要记下它们何时不可用的date_start，然后是date_end.

Thinking about it, I'm going to set up a table to keep note of when people AREN'T available, and assume that they are otherwise available. The structure I had in mind was to keep a note of the date_start of when they won't be available, and then the date_end.

现在，当我尝试预订会议时，就会出现问题，如果我想在星期二11:00到13:00预订一些东西，那么我需要获取那些有空的人的列表，即不要在这段时间内没有任何假期预订.

Now the problem arises when I'm trying to book a meeting, if I want to book something on Tuesday for 11:00 until 13:00 I need to fetch a list of those people that are available then, i.e. don't have any holidays booked at during that time.

有什么想法或想法吗?如果可以帮助/支持任何人的想法，我将使用Ruby on Rails.

Any ideas or thoughts? I'll be using Ruby on Rails if that helps/hinders anyone's ideas.

推荐答案

这里是对其建模的一种方法.假设我们有一个模型"Engagement"，其中包含开始日期时间，结束日期时间和名称.参与通过另一个称为"user_engagements"(具有相应的UserEngagement模型)的联接表具有许多用户.所以我们有

Here's one way to model it. Lets say we have a model 'Engagement' which has a start datetime, end datetime and name. An engagement has many users, through another join table called 'user_engagements' (with 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

现在，我们有了一个不错的简单架构.参与基本上是对正在发生的事情进行建模，而user_engagements则是对已预订要执行该操作的用户进行建模.我们有一个假设(没有写到代码中)是，当他们做某事时，他们无能为力.

Now we have a nice simple schema. An engagement basically models something that's happening, and user_engagements model users who are booked to do that thing. We have an assumption (not written into the code) that when they are doing something they aren't available to do anything else.

我们的下一个任务是编写一种方法，该方法返回给定时间段内可用的用户，即新的参与度.因此，我们进行了互动，我们希望所有没有互动的用户都与我们的新互动交叉.我认为最简单的方法可能是找到所有具有交叉参与的用户，然后返回所有不是他们的用户.如果你明白我的意思.说e2与e1相交的一种更精确的方法是e2在e1结束之前开始，而e1在开始之后结束.

Our next task is to write a method that returns users available within a given time period, ie a new engagement. So, we make an engagement and we want all the users who don't have an engagement which crosses over with our new engagement. I think the simplest way of doing this might be to find all the users who do have a crossing-over engagement and then return all the users who aren't them. If you know what i mean. A more precise way of saying e2 crosses over with e1 is that e2 starts before the end of e1 AND ends after the start of e1.

由于它完全依赖于参与的数据，因此使它成为一种参与对象的方法.

Let's make this a method of an engagement object since it's totally dependent on an engagement's 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

我觉得有一种更有效的方法可以在一个查询中获取此信息，但我不太愿意在sql上使用.

I feel like there's a more efficient way to get this in one query but i can't quite put my finger on the sql.

这篇关于列出特定日期的可用用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！