实施用户事件ORM模型的阻止日期 [英] Implementation of blocked dates for a user-event ORM model
问题描述
我有一个应用程序（Ruby on Rails，ActiveRecord，Postgresql），它按顺序使用用户表，日期表和事件表跟踪用户及其事件以及发生这些事件的日期。事件有很多日期，用户可以通过不同的表格登录事件。
I have an application (Ruby on Rails, ActiveRecord, Postgresql) that uses a user table, date table and an event table in order to track users and their events as well as dates on which these events take place. Event has many dates, and user has the ability to sign into events via a different table.
我在这里工作的功能是让用户能够阻止某些日期，以便稍后找到1）X []中没有任何日期的用户被阻止，以及2）从用户的一边，所有不包含用户阻止的日期的事件。
The feature I am working on here is for users to be able to block certain dates, so that I can later find 1) users that do not have any of the dates in X[] blocked, and 2) from the side of the user, all events that do not consist of any dates that the user has blocked.
我正在使用原始方法，日期作为简单的字符串存储给用户和事件和运算符：
I am currently using a primitive approach, dates are stored as simple strings for both users and events and operators:
User.where.not("string_to_array(blocked_dates, ',') && string_to_array(?, ',')", "date1,date2...")
我想知道是否有任何数据库导向的方法来解决这个问题，比比较数组有更好的性能重叠。
I am wondering if there are any database-oriented approaches of solving this issue that have better performance than comparing array overlaps.
用户 - 事件：一对多（如果用户是状态管理员）
User - Event: one to many (if the user is of status manager)
用户 - 事件：通过称为邀请的不同表格（如果用户是状态客户端），可以通过多个对象
User - Event: many to many through a different table called invitations (if the user is of status client)
事件 - EventDate：一对多
Event - EventDate: one to many
id | integer | not null default nextval('users_id_seq'::regclass)
created_at | timestamp without time zone |
updated_at | timestamp without time zone |
email | character varying(255) | not null default ''::character varying
encrypted_password | character varying(255) | not null default ''::character varying
...
and other 100 or so fields like first_name, last_name ...
来自\d事件的陈述
Statements from \d events
id | integer | not null default nextval('events_id_seq'::regclass)
title | character varying(255) |
description | text |
user_id | integer (this refers to another type of user, capable of creating events, not the type of user we are referring to here) |
project_status | character varying(255) | default 'create_project'::character varying
created_at | timestamp without time zone |
updated_at | timestamp without time zone |
due_time | timestamp without time zone |
...
and other fields such as address, referrer number...
< h1>来自\d event_dates的陈述
Statements from \d event_dates
id | integer | not null default nextval('event_dates_id_seq'::regclass)
title | character varying(255) |
event_id | integer |
created_at | timestamp without time zone |
updated_at | timestamp without time zone |
date | character varying(255) (I need to explicitly store a string value) |
formatted_date | timestamp without time zone (String value is converted to the timestamp on save) |
Indexes:
"event_dates_pkey" PRIMARY KEY, btree (id)
"index_event_dates_on_event_id" btree (event_id)
新字段的声明\d blocked_date_periods
Statements from new field \d blocked_date_periods
id | integer | not null default nextval('blocked_date_periods_id_seq'::regclass)
user_id | integer |
created_at | timestamp without time zone |
updated_at | timestamp without time zone |
start_date | timestamp without time zone |
end_date | timestamp without time zone |
推荐答案
这是我选择的解决方案。诀窍在于使用用户和blocked_date_periods表使用 left outer join （ruby command eager_load），并且包括连接表中的start_date字段为NULL的用户，显然是因为它们不有与自己关联的任何被阻止的日期对象。我使用的查询：
Here is the solution I chose. The trick was using left outer join (ruby command eager_load) for users and blocked_date_periods table, and including those users whose start_date field in the joined table is NULL, obviously because they do not have any blocked date objects associated with themselves. The query I use:
User.eager_load(:blocked_date_periods).
where("blocked_date_periods.start_date is null OR
not tsrange(
blocked_date_periods.start_date - '00:59:59'::interval,
blocked_date_periods.end_date + '00:59:59'::interval
) @> ?::timestamp",
Date.parse(DATE_STRING)).count
我不得不从开始和结束日期添加和减去1小时，因为查询不希望由于某种原因包含确切的结束日期，所以12-26-2015是不包括在12-22-2015到12-16-2015期间，由于某些原因我还没有明白。
I had to add and subtract 1 hour from the start and end date because the query did not want to encompass exact end dates for some reason, so that 12-26-2015 was not included inside the period of from 12-22-2015 to 12-16-2015 for some reason I am yet to understand.
由于某种原因我不喜欢并且想知道是否有比我所拥有的更好更快的查询。
For some reason I do not like that solution and would like to know whether there is a query that is better and faster than what I have.
这篇关于实施用户事件ORM模型的阻止日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
