Horton

Thread

  1. Querying all documents for a company and its projects etc

    Andreas Joseph Krogh <andreak@officenet.no> — 2014-04-14T19:21:27Z

    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 referencesentity(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 
tabledocument_usage( document_id integer not null references document(id), 
entity_idinteger 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 
intodocument_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.nameas company_name, null as person_name, null as project_name from 
documentdoc JOIN document_usage du ON doc.id = du.document_id JOIN company comp 
ONdu.entity_id = comp.id WHERE comp.id = 1 UNION SELECT doc.id, doc.name as 
document_name, comp.nameas company_name, pers.name as person_name, null as 
project_name from document doc JOIN document_usage du ON doc.id = du.document_id
JOINperson 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.nameas company_name, null as person_name, proj.name as 
project_name from document doc JOIN document_usage du ON doc.id = du.document_id
JOINproject 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

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

    David G. Johnston <david.g.johnston@gmail.com> — 2014-04-14T19:55:26Z

    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.

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

    Andreas Joseph Krogh <andreak@officenet.no> — 2014-04-16T08:11:39Z

    På mandag 14. april 2014 kl. 21:55:26, skrev David G Johnston <
david.g.johnston@gmail.com <mailto: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  

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

    Fede Martinez <federicoemartinez@gmail.com> — 2014-04-16T13:07:34Z

    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
>