Understanding Orchard's Database

I recently found a question on Orchard's forums asking for some explanations to query data directly from the database. Here is my take on this.

Querying Content Items

Each content item is described in Orchard_Framework_ContentItemRecord, you will always need this table. You will also always need Orchard_Framework_ContentItemVersionRecord as it represents each version, even if the content type is not versionable. Therefore a content item is always represented by the combination of a ContentItemRecord containing the information that doesn't change per version, and a ContentItemVersionRecord describing each version.

So the minimum of required SQL is be

SELECT * 
FROM Orchard_Framework_ContentItemRecord AS cir
INNER JOIN Orchard_Framework_ContentItemVersionRecord 
    AS civr ON civr.ContentItemRecord_id = cir.Id

Then you will always want to get the Published versions of a content item. This is done by filtering on the version's Published flag. There is another interesting flag which is Latest and is true for the latest available version. If the version record is the latest but not published it means it's a draft.

SELECT * 
FROM Orchard_Framework_ContentItemRecord AS cir
INNER JOIN Orchard_Framework_ContentItemVersionRecord 
    AS civr ON civr.ContentItemRecord_id = cir.Id
WHERE civr.Published = 1

Now the next logical step is to select a specific content type. The Orchard_Framework_ContentTypeRecord table lists all of them. Because the type doesn't vary from version to version the information is stored in the ContentItemRecord table.

It is highly recommended for performance reasons to cache this table and use the identifier to filter out the content items. I am not doing this in these example to focus on what matters here.

SELECT * 
FROM Orchard_Framework_ContentItemRecord AS cir
INNER JOIN Orchard_Framework_ContentTypeRecord 
    AS ctr ON ctr.Id = cir.ContentType_id
INNER JOIN Orchard_Framework_ContentItemVersionRecord 
    AS civr ON civr.ContentItemRecord_id = cir.Id
WHERE civr.Published = 1

Getting Actual Data

If you are using Orchard 1.8 you might already have all the needed information in the cir.Data and civr.Data fields as they represent the document's data like a Document DB would store it. The limitation with this portion of the data is that you can't filter on it. This is where content part records become useful.

A content part record contains a slice of information for a content item. If a content type is made of a BodyPart and a TitlePart then there will be a BodyPartRecord containing the body text, and a TitlePartRecord containing the title.

When a record class inherits from ContentPartRecord, it's Id will have the same value as ContentItemRecord.Id. When the record class inherits from ContentPartVersionRecord its Id will have the same value as ContentItemVersionRecord.Id.

The logic behind this mapping for Nhibernate can be found in the class Orchard.ContentManagement.Records.ContentPartAlteration

Let's say you want to get the specific content item with a slug 'contact-us', you would join the Orchard_Autoroute_AutoroutePartRecord id with the content item version one like this:

SELECT * 
FROM Orchard_Framework_ContentItemRecord AS cir
INNER JOIN Orchard_Framework_ContentTypeRecord 
    AS ctr ON ctr.Id = cir.ContentType_id
INNER JOIN Orchard_Framework_ContentItemVersionRecord 
    AS civr ON civr.ContentItemRecord_id = cir.Id
INNER JOIN Orchard_Autoroute_AutorouteParRecord 
    AS apr ON apr.Id = civr.Id
WHERE civr.Published = 1 AND apr.DisplayAlias = 'contact-us'

And in order to order items by their create date, you would use Common_CommonParRecord with the id of the content item:

SELECT * 
FROM Orchard_Framework_ContentItemRecord AS cir
INNER JOIN Orchard_Framework_ContentTypeRecord 
    AS ctr ON ctr.Id = cir.ContentType_id
INNER JOIN Orchard_Framework_ContentItemVersionRecord 
    AS civr ON civr.ContentItemRecord_id = cir.Id
INNER JOIN Common_CommonPartRecord 
    AS cpr ON cpr.Id = cir.Id
WHERE civr.Published = 1
ORDER BY cpr.CreatedUtc

As a last note you might have noticed that each table is prefixed by the module they belong to. ContentItemRecord is defined in the Orchard.Framework project, CommonPartRecord is defined in the Core module, and AutoroutePartRecord is defined in Orchard.Autoroute.

No Comments