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.
Orchard Originator Database is a source for complex information on a large plus growing number of leading a wide range of global classes. these are best features to collecting the data and store it.
Data fields as they speak to the archive's information like a Document DB would store it. The impediment with this bit of the information is that you can't channel on it. This is the place content part records end up noticeably valuable.
A substance part record contains a cut of data for a substance thing. On the off chance that a substance sort is made of a BodyPart and a TitlePart at that point there will be a BodyPartRecord containing the body content, and a TitlePartRecord containing the title.
In case you are using Orchard 1.8 you may starting at now have all the required information in the cir.Data and civr. Data fields as they address the chronicle's data like a Document DB would store it.
When you are substance to be just yourself and don't think about or contend, everyone will regard you. There's an enormous contrast amongst alone and desolate. You could be forlorn in a gathering of individuals. I like being separated from everyone else. I like eating independently from anyone else. I go home during the evening and simply watch a motion picture or hang out with my pooch. I need to strive and truly say good God, I must see my companions 'cause I'm excessively content being independent of anyone else.
Learners are allowable to rapidity through or avoid instruction that is jobless while beginners slow their own development through content.
Really this is the best technique that you have shared. I hope that I will be able to understand the database easily and will be able to make my website more attractive and awesome to view.
Orchard enables originators to grow the credit scene by associating them to a different arrangement of capital suppliers and empowers them to concentrate on making advances and giving borrowers more decisions.
Orchard enables originators to grow the credit scene by associating them to a different arrangement of capital suppliers and empowers them to concentrate on making advances and giving borrowers more decisions.
Orchard enables originators to grow the credit scene by associating them to a different arrangement of capital suppliers and empowers them to concentrate on making advances and giving borrowers more decisions.
Orchard enables originators to grow the credit scene by associating them to a different arrangement of capital suppliers and empowers them to concentrate on making advances and giving borrowers more decisions.
Orchard enables originators to grow the credit scene by associating them to a different arrangement of capital suppliers and empowers them to concentrate on making advances and giving borrowers more decisions.
A few people think the puppy has a real vocabulary yet that isn't in reality evident. Winged creatures have a vocabulary by imitating words, recalling, and having the capacity to rehash the genuine vowels and tones. Puppies are not ready to talk or rehash our dialect but rather they can recall particular orders and play out the activity related
A database isn't generally portable across various DBMSs, however extraordinary DBMSs can interoperate by utilizing standards, for example, SQL and ODBC or JDBC to allow a solitary application to work with more than one DBMS. PC researchers may classify database-management frameworks according to the database models that they bolster; the most popular database frameworks since the 1980s have all upheld the relational model - generally associated with the SQL language.
Greetings from Ohio! I'm bored at work so I decided to check
out your blog on my iphone during lunch break. I enjoy the knowledge you provide here
and can't wait to take a look when I get home. I'm surprised at how quick your blog loaded on my cell phone ..
I'm not even using WIFI, just 3G .. Anyways, excellent site!
Hi there! I know this is somewhat off topic but I was wondering if you knew
where I could find a captcha plugin for my comment form?
I'm using the same blog platform as yours and I'm having problems finding one?
Thanks a lot!
Olá ! Isso é meio off-topic, mas preciso de alguns orientação de um blog estabelecido.
É muito difícil para definir o seu próprio blog?
Eu não sou muito técnico, mas pode resolver as coisas muito rápida.
Estou pensando em criação meu próprio, mas eu não
sei onde começar. Tem algum pontos ou sugestões?
Obrigado
My spouse and I stumbled over here different web address and thought I might check things out.
I like what I see so now i'm following you. Look forward to checking out your web
page again.
I don't even know how I ended up here, but I thought this post
was good. I don't know who you are but certainly you're going to a famous blogger if you are
not already ;) Cheers!
I know this web site offers quality based posts and extra stuff, is there any
other site which presents these kinds of things in quality?
I think everything said made a ton of sense. But, consider this, suppose you added a little content?
I am not saying your information isn't good, but what if you added something that grabbed a person's attention? I mean Understanding Orchard's Database is kinda boring.
You could look at Yahoo's front page and watch how they create article
headlines to get viewers to click. You might add a related video
or a related picture or two to get readers excited about
what you've written. In my opinion, it might make your blog a little bit
more interesting.
Hello, i think that i saw you visited my weblog thus i came to “return the favor”.I am attempting to find things to improve my website!I
suppose its ok to use some of your ideas!!
Great info. Lucky me I came across your website by
accident (stumbleupon). I have book marked it for later!
Woah! I'm really loving the template/theme of this site.
It's simple, yet effective. A lot of times it's
tough to get that "perfect balance" between usability
and appearance. I must say that you've done a great job with this.
In addition, the blog loads extremely fast for me on Firefox.
Outstanding Blog!
of course like your website however you have to take a look at the spelling on several of
your posts. Several of them are rife with spelling problems
and I to find it very bothersome to inform the truth on the
other hand I'll surely come again again.
Asking questions are really pleasant thing if you are not understanding something entirely, except this piece of writing gives
nice understanding even.