0

In continuation of Find entry where value does not intersect with other value

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.

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.

Postgres Version: psql (PostgreSQL) 9.4.5

Table Cardinalities:

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)

Event - EventDate: one to many

Statements from \d users

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 ...

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...

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)

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 |
Community
  • 1
  • 1
Vitaly Stanchits
  • 658
  • 2
  • 7
  • 24
  • Please provide basic information. Complete `CREATE TABLE` statements or what you get with `\d tbl` in psql. Your Postgres version and cardinalities. – Erwin Brandstetter Jan 15 '16 at 23:11
  • This is probably a good use for iCalendar RRULEs and EXDATEs . see https://github.com/petropavel13/pg_rrule – Neil McGuigan Jan 16 '16 at 01:22
  • I posted the specific information on tables I currently have. – Vitaly Stanchits Jan 18 '16 at 09:12
  • Edited. Update, it seems I need to have the input of blocked dates in the form of date periods, with starting date and ending date. The question is, given I have the blocked date period object (see info in post), what would be my query to get those events whose dates do not fall into these start-end time periods for the user given his blocked dates? – Vitaly Stanchits Jan 18 '16 at 13:43

1 Answers1

0

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

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.

Vitaly Stanchits
  • 658
  • 2
  • 7
  • 24