I need help with a SQL query. Fetching an entry, it's most recent revision and it's fields.

Question

Hi there,

I'm building a CMS for my own needs, and finished planning my database layout.

Basically I am abstracting all possible data-models into "sections" and all entries into one table. The final layout is as follows:

Database diagram: I have yet to be allowed to post images, so here is a link to a diagram of my database.

Entries (section_entries) are children of their section (sections). I save all edits to the entries in a new revision (section_entries_revisions), and also track revisions on the sections (section_revisions), in order to match the values of a revision, to the fields of the section that existed when the entry-revision was made. The section-revisions can have a number of fields (section_revision_fields) that define the attributes of entries in the section. There is a many-to-many relationship between the fields (section_revision_fields) and the entry-revisions (section_entry_revisions), that stores the values of the attributes defined by the section revision.

The key-icons on the database-diagram indicate what columns are part of the primary key. Most of the tables have identifying relationships.

Feel free to ask questions if the diagram is confusing.

Now, this is the most complex SQL I've ever worked with, and the task of fetching my data is a little daunting.

Basically what i want help with, is fetching an entry, when the only known variables are; section_id, section_entry_id. The query should fetch the most recent revision of that entry (most recent, being the revision with the highest id), and the section_revision model corresponding to section_revision_id in the section_entry_revisions table. It should also fetch the values of the fields in the section-revision.

I was hoping for a query result, where there would be as many rows as fields in the section. Each row would contain the information of the entry and the section, and then information for one of the fields (e.g. each row corresponding to a field and it's value).

I tried to explain the best I could. Again, feel free to ask questions if my description somehow lacking.

I hope someone is up for the challenge. Best regards. :-)

The Query So Far

SELECT ser.*, sersrf.value

FROM section_entry_revisions AS ser

JOIN section_entry_revisions_section_revision_fields AS sersrf
  ON sersrf.section_entry_revision_id = ser.id

WHERE ser.section_id = 1
  AND ser.section_entry_id = 1
  AND ser.id = (

    SELECT MAX(subser.id)

    FROM section_entry_revisions AS subser

    WHERE subser.section_id = ser.section_id
      AND subser.section_entry_id = ser.section_entry_id

  )

Now I want to join the field values, with the field names from the particular section_revision.

Answer 1

It sounds like the real trick is finding the last revision. You did not specify how "last" is determined so I assumed it was based on created. Your diagram also did not specify primary keys so I assumed that the PK of section_entry_revisions was Id.

Select ...
From section_entry_revisions As SER
    Join section_entry_revisions_section_revision_fields As SRF
        On SRF.section_revision_id = SER.Id
    ...
Where SER.sectionId = @SectionId
    And SER.section_entry_id = @SectionEntryId
    And SER.id =    (
                    Select Max(SER1.id)
                    From section_entry_revisions As SER1
                    Where SER1.section_Id = SER.section_Id
                        And SER1.section_entry_id = SER.section_entry_id 
                    Group By SER1.sectionId, SER1.section_entry_id
                    Having SER.created= Max(SER1.created)
                    )

ADDITION

If "last" is solely determined by section_entry_revisions.id, then you can eliminate the Having and Group By clause I used in the subquery in my original example.

If the key icon represents the primary key, then that means that section_id, section_entry_id and id all makeup the primary key? Is Id in that table an identity column? If so, why isn't it the lone primary key and the other two part of a candidate key (unique constraint)?

To get to the section_revision_fields values, you should be able to join directly to the section_revision_fields table. All that the subquery has done is filter the section_entry_revisions rows down to a single row. Now that you have that, you can follow the relationships to get the data from the related tables using standard joins unless there is an additional problem to be solved here. Here is the revised query:

Select ...
From section_entry_revisions As SER
    Join section_revision_fields As SRF
        On SRF.section_id = SER.section_id
            And SRF.section_revision_id = SER.section_revision_id
    ...
Where SER.sectionId = @SectionId
    And SER.section_entry_id = @SectionEntryId
    And SER.id =    (
                    Select Max(SER1.id)
                    From section_entry_revisions As SER1
                    Where SER1.section_Id = SER.section_Id
                        And SER1.section_entry_id = SER.section_entry_id 
                    )