I need help with SQL query. Retrieving a record, its latest revision and fields

Question

I am creating a CMS for my own needs and have finished planning my database layout.

Basically, I abstract all possible data models into “partitions” and all records in one table. The final layout is as follows:

Database Diagram: I am not yet allowed to post images, so here is a link to the diagram of my database .

Entries ( section_entries ) are children of their section ( sections ). I save all changes to the record in the new edition ( section_entries_revisions ), and also track changes in sections ( section_revisions ) to match the revision values, to the section fields that existed when the input revision was made. Section changes can have several fields ( section_revision_fields ) that define the attributes of the records in this section. Between fields, there are many, many relationships ( section_revision_fields ) and input revisions ( section_entry_revisions ), which store the attribute values ​​defined in the section revision.

The key icons in the database diagram indicate which columns are part of the primary key. Most tables identify relationships.

Feel free to ask questions if the chart is confusing.

Now this is the most complex SQL I've ever worked with, and the task of getting my data is a bit difficult.

Basically, with what I need help, a record is selected when only known variables are known; section_id, section_entry_id . The request should receive the most recent revision of this record (the last being the revision with the highest id ), and the section_revision model corresponding to section_revision_id in section_entry_revisions . It should also retrieve the field values ​​in the revision section.

, , . , (, , ).

, . , , - .

, - . .: -)

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

  )

, _revision.

Answer 1

, - . , "", , created. , , PK section_entry_revisions 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)
                    )

"" section_entry_revisions.id, " ", .

, , , section_id, section_entry_id Id - ? Id ? , - ( )?

section_revision_fields, section_revision_fields. , , - section_entry_revisions . , , , , , . :

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 
                    )