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?
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);