Question :
I am using Postgres, and I am working on a database for a roster system like this:
drop table if exists sessions;
create table if not exists sessions(
id int GENERATED ALWAYS AS IDENTITY,
name varchar(256) not null,
start_date timestamptz not null,
end_date timestamptz not null,
primary key(id)
);
drop table if exists workers;
create table if not exists workers(
id int GENERATED ALWAYS AS IDENTITY,
name varchar(256) not null,
primary key(id)
);
drop table if exists sessions_workers;
create table if not exists sessions_workers(
id int GENERATED ALWAYS AS IDENTITY,
session_id int not null,
worker_id int not null,
primary key(id),
foreign key(session_id) references sessions(id) ON DELETE CASCADE,
foreign key(worker_id) references workers(id) ON DELETE CASCADE
);
So by associating the worker in the junction table with that session, the worker will attend that session. But what I can figure out is, how can I know if a worker is not available, even if it has not attached to the session due to overlapped timeslot?
For an example the following are the seed.
insert into sessions(name, start_date, end_date) values
(
'Session A', '2021-10-19 09:00:00+02', '2021-10-19 10:00:00+02'
),
(
'Session B', '2021-10-19 09:00:00+02', '2021-10-19 11:00:00+02'
);
insert into workers(name) values
('tom'),
('john');
insert into sessions_workers(session_id, worker_id) values (1, 1), (2, 2);
john
will not be available for Session A
, even though he has not associated with that session, as he should be occupied by Session B
. What kind of query can I use to figure that out?
EDIT
Answer :
I can’t believe that I am able to pull this off by myself. SQL is soooooo hard. Turn out I have to use range to deal with this kind of data.
select * from sessions_workers
join sessions on sessions.id = sessions_workers.session_id
right join workers on sessions_workers.worker_id = workers.id
where (sessions.id <> 1 or sessions.id is null)
and not sessions.duration && (select duration from sessions where sessions.id = 1);