Обсуждение: Querying all documents for a company and its projects etc

Поиск
Список
Период
Сортировка

Querying all documents for a company and its projects etc

От
Andreas Joseph Krogh
Дата:
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@officenet.no>      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

Re: Querying all documents for a company and its projects etc

От
David G Johnston
Дата:
A couple of thoughts:

1) The "union" really only pertains to the entity table rows; once you
"union all" those (duplicates should not matter and probably will not even
be present so using "all" avoids an unnecessary sort) you can join that
sub-query to the document_usage table.

2) Since every entity must have an associated company moving the company_id
field to the entity table will allow a direct search for company objects
using entity alone and remove the need to perform the union.  The "company"
table omits the implicit self-referencing company_id but it is still there
in reality.

David J.






--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Querying-all-documents-for-a-company-and-its-projects-etc-tp5799967p5799978.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: Querying all documents for a company and its projects etc

От
Andreas Joseph Krogh
Дата:
På mandag 14. april 2014 kl. 21:55:26, skrev David G Johnston <david.g.johnston@gmail.com>:
A couple of thoughts:

1) The "union" really only pertains to the entity table rows; once you
"union all" those (duplicates should not matter and probably will not even
be present so using "all" avoids an unnecessary sort) you can join that
sub-query to the document_usage table.

2) Since every entity must have an associated company moving the company_id
field to the entity table will allow a direct search for company objects
using entity alone and remove the need to perform the union.  The "company"
table omits the implicit self-referencing company_id but it is still there
in reality.

David J.
 
 
Thanks for having a look at it.
 
I'm afraid I don't understand what you mean in 1); Can you give an example of the query you're suggesting, joining "that subquery"?
 
2) Note that I need the names of the company owning each entity(person, project etc.), and the details about those entities (name and ID), and in my real app there are more entities which are not directly related to company, but indirectly thru a project (ie. a task) and I want to be able to list those entities' documents too.
 
Do you have a suggestion for a better $subject for this kind of problem?
 
thanks.
 
--
Andreas Joseph Krogh <andreak@officenet.no>      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
 

Re: Querying all documents for a company and its projects etc

От
Fede Martinez
Дата:
what about resolving the join "document doc JOIN document_usage du ON
doc.id = du.document_id" using WITH? I think this won't work because
the result of this CTE would be huge,
but you could get the company with that id first using a WITH clause
too, then you would have the name and you wouldn't have to join with
the company table.



2014-04-16 5:11 GMT-03:00 Andreas Joseph Krogh <andreak@officenet.no>:
> På mandag 14. april 2014 kl. 21:55:26, skrev David G Johnston
> <david.g.johnston@gmail.com>:
>
> A couple of thoughts:
>
> 1) The "union" really only pertains to the entity table rows; once you
> "union all" those (duplicates should not matter and probably will not even
> be present so using "all" avoids an unnecessary sort) you can join that
> sub-query to the document_usage table.
>
> 2) Since every entity must have an associated company moving the company_id
> field to the entity table will allow a direct search for company objects
> using entity alone and remove the need to perform the union.  The "company"
> table omits the implicit self-referencing company_id but it is still there
> in reality.
>
> David J.
>
>
>
> Thanks for having a look at it.
>
> I'm afraid I don't understand what you mean in 1); Can you give an example
> of the query you're suggesting, joining "that subquery"?
>
> 2) Note that I need the names of the company owning each entity(person,
> project etc.), and the details about those entities (name and ID), and in my
> real app there are more entities which are not directly related to company,
> but indirectly thru a project (ie. a task) and I want to be able to list
> those entities' documents too.
>
> Do you have a suggestion for a better $subject for this kind of problem?
>
> thanks.
>
> --
> Andreas Joseph Krogh <andreak@officenet.no>      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
>