请注意,本站并不支持低于IE8的浏览器,为了获得最佳效果,请下载最新的浏览器,推荐下载 Chrome浏览器
欢迎光临。交流群:166852192

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 ContentItemRecordcontaining 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.IdWHERE 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_idINNER JOIN Orchard_Framework_ContentItemVersionRecord 
    AS civr ON civr.ContentItemRecord_id = cir.IdWHERE 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 TitlePartRecordcontaining 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 asContentItemVersionRecord.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_idINNER JOIN Orchard_Framework_ContentItemVersionRecord 
    AS civr ON civr.ContentItemRecord_id = cir.IdINNER JOIN Orchard_Autoroute_AutorouteParRecord 
    AS apr ON apr.Id = civr.IdWHERE 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_idINNER JOIN Orchard_Framework_ContentItemVersionRecord 
    AS civr ON civr.ContentItemRecord_id = cir.IdINNER JOIN Common_CommonPartRecord 
    AS cpr ON cpr.Id = cir.IdWHERE civr.Published = 1ORDER 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 Coremodule, and AutoroutePartRecord is defined in Orchard.Autoroute.



作者原创内容不容易,如果觉得内容不错,请点击右侧“打赏”,赏俩给作者花花,也算是对作者付出的肯定,也可以鼓励作者原创更多更好内容。
更多详情欢迎到QQ群 166852192 交流。