I've got a question in regards to a particular query Let me execute against a PostgreSQL table. Although I welcome critique on the table plan I have used I am likely to be more grateful for solutions to my actual question!

I am while using uuid-ossp postgresql-contrib module and also have the following table structure:

       Column        |            Type             | Modifiers | Storage  | Description
---------------------+-----------------------------+-----------+----------+-------------
 revision_id         | uuid                        | not null  | plain    |
 document_id         | uuid                        | not null  | plain    |
 user_id             | uuid                        | not null  | plain    |
 datetime_edited     | timestamp without time zone | not null  | plain    |
 contents            | text                        | not null  | extended |
Indexes:
    "document_pkey" PRIMARY KEY, btree (revision_id)

The concept is the fact that:

  • A document might have a number of revisions. Revisions aren't erased. To be able to update a document a brand new row is placed with a brand new revision_id but the same document_id.
  • revision_id is exclusive across all revisions for those documents.
  • contents is really a blob of information that signifies the document, and user_id identifies who up-to-date the document.

I am battling to develop a question that returns all of the latest revisions for those documents produced by a specific user. I understand I'm able to do, for instance:

select * from document where user_id = '6a2aabc417b34ef99b14b10eaa8e9313';

but this returns all of the documents. How do you drill lower and request for any grouping by document_id, as well as LIMIT 1 and return the latest revision_id according to datetime_edited?

EDIT: Since a document might have a number of revisions I have been far to vague in saying "all documents produced with a user". By produced I am talking about the user has led a number of revisions towards the documents, i.e. there's a minumum of one revision in which the user edited the document.

Is one thing such as this even achievable in a single query, or should i hit the database several occasions to do this?

EDIT: revision_id isn't monotonically growing. It is a random UUID. Hence, max(revision_id) != max(datetime_edited).

Obtain the greatest revision id for every document, then choose individuals documents:

select *
from document
where revision_id in (
  select max(revision_id)
  from document
  where user_id = '6a2aabc417b34ef99b14b10eaa8e9313'
  group by document_id
)

Update:

Because the version id isn't incremental, you'll have to choose the document_id and it is latest datetime_edited, and join from the document table:

select d.*
from document d
inner join (
  select document_id, max(datetime_edited) as datetime_edited
  from document
  where user_id = '6a2aabc417b34ef99b14b10eaa8e9313'
  group by document_id
) x on x.document_id = d.document_id and x.datetime_edited = d.datetime_edited
Select ...
From document As D
    Join    (
            Select D1.document_id, Max( datetime_edited ) As datetime_edited
            From document As D1
            Group By D1.document_id
            ) As LastRevision
        On LastRevision.document_id = D.document_id
            And LastRevision.datetime_edited = D.datetime_edited
Where Exists    (
                Select 1
                From document As D2
                Where D2.document_id = D.document_id
                    And D2.user_id = '6a2aabc417b34ef99b14b10eaa8e9313'
                )

Another form:

Select ...
From document As D
    Join    (
            Select D1.document_id, Max( datetime_edited ) As datetime_edited
            From document As D1
            Group By D1.document_id
            ) As LastRevision
        On LastRevision.document_id = D.document_id
            And LastRevision.datetime_edited = D.datetime_edited
    Join    (
            Select D2.document_id
            From document As D2
            Where D2.user_id = '6a2aabc417b34ef99b14b10eaa8e9313'
            Group By D2.document_id
            ) As UserDocs
        On UserDocs.document_id = D.document_id