Querying all documents for a company and its projects etc - Mailing list pgsql-general

create table entity(
    id integer primary key,
    entity_type varchar not null,
    check (entity_type IN ('COMPANY', 'PERSON', 'PROJECT'))
);

create table company(
    id integer primary key references entity(id),
    name varchar not null
);

create table person(
    id integer primary key references entity(id),
    name varchar not null,
    company_id integer references company(id)
);

create table project(
    id integer primary key references entity(id),
    name varchar not null,
    company_id integer references company(id)
);

create table document(
    id integer primary key,
    name varchar not null
);

create table document_usage(
    document_id integer not null references document(id),
    entity_id integer not null references entity(id)
);

insert into entity(id, entity_type) values(1, 'COMPANY');
insert into company(id, name) values(1, 'ACME');

insert into entity(id, entity_type) values(2, 'PERSON');
insert into person(id, name, company_id) values(2, 'Bill', 1);

insert into entity(id, entity_type) values(3, 'PROJECT');
insert into project(id, name, company_id) values(3, 'Development', 1);

insert into document(id, name) values(1, 'Doc 1');
insert into document(id, name) values(2, 'Doc 2');
insert into document(id, name) values(3, 'Doc 3');

insert into document_usage(document_id, entity_id) values(1, 1);
insert into document_usage(document_id, entity_id) values(1, 3);
insert into document_usage(document_id, entity_id) values(2, 2);
insert into document_usage(document_id, entity_id) values(3, 3);


So, documents are related to companies, persons or projects thru
the document_usage table.

I have this query to list all documents for a specific company
and related employees and projects (belonging to that company)

select doc.id, doc.name as document_name, comp.name as company_name, null as person_name, null as project_name
from document doc JOIN document_usage du ON doc.id = du.document_id
    JOIN company comp ON du.entity_id = comp.id
WHERE comp.id = 1
UNION
SELECT doc.id, doc.name as document_name, comp.name as company_name, pers.name as person_name, null as project_name
from document doc JOIN document_usage du ON doc.id = du.document_id
    JOIN person pers ON pers.id = du.entity_id JOIN company comp ON comp.id = pers.company_id
WHERE comp.id = 1
UNION
SELECT doc.id, doc.name as document_name, comp.name as company_name, null as person_name, proj.name as project_name
from document doc JOIN document_usage du ON doc.id = du.document_id
    JOIN project proj ON proj.id = du.entity_id JOIN company comp ON comp.id = proj.company_id
WHERE comp.id = 1
order by document_name
;