Querying all documents for a company and its projects etc

[Andreas Joseph Krogh <andreak@xxxxxxxxxxxx>]

Hi all.

 

I'm trying to make an efficient query to list all documents related to a company and also documents related to employees and projects for that company.

 

I have this sample-schema:

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
;

 id | document_name | company_name | person_name | project_name
----+---------------+--------------+-------------+--------------
  1 | Doc 1         | ACME         |             |
  1 | Doc 1         | ACME         |             | Development
  2 | Doc 2         | ACME         | Bill        |
  3 | Doc 3         | ACME         |             | Development
(4 rows)

 

 

I'm looking for a more efficient query where I don't have to repeat JOINing with document, document_usage and company all the time, and somehow avoid the UNIONs.

 

Anyone has a better solution respecting the schema?

 

Thanks.

 

--
Andreas Joseph Krogh <andreak@xxxxxxxxxxxx>      mob: +47 909 56 963
Senior Software Developer / CTO - OfficeNet AS - http://www.officenet.no
Public key: http://home.officenet.no/~andreak/public_key.asc