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.
Why users still use to read news papers when in this technological world all is available on web?
Do you have a spam issue on this website; I also am a blogger, and I was wondering your situation; we have developed some
nice practices and we are looking to trade strategies with other
folks, be sure to shoot me an e-mail if interested.
Howdy! This is my 1st comment here so I just wanted
to give a quick shout out and tell you I really enjoy
reading through your blog posts. Can you recommend
any other blogs/websites/forums that cover the same topics?
Thanks!
Hello, i believe that i noticed you visited my website so i came to return the favor?.I am attempting to to find things to improve my site!I suppose its adequate to
use some of your ideas!!
Nice post. I learn something new and challenging on websites I stumbleupon on a daily basis.
It's always interesting to read content from other writers
and practice something from other sites.
It's a shame you don't have a donate button! I'd definitely donate to
this outstanding blog! I guess for now i'll settle for bookmarking
and adding your RSS feed to my Google account.
I look forward to brand new updates and will share
this website with my Facebook group. Chat soon!
Thanks for your marvelous posting! I actually enjoyed reading
it, you can be a great author. I will ensure that I bookmark your blog and definitely will come back very soon. I want
to encourage you to ultimately continue your great work, have a nice weekend!
I every time spent my half an hour to read this webpage's articles or reviews all the time
along with a mug of coffee.
Wow that was strange. I just wrote an very long comment but
after I clicked submit my comment didn't show up. Grrrr...
well I'm not writing all that over again. Anyways,
just wanted to say fantastic blog! Bayern München Trikot
Hi there! This is my first visit to your blog!
We are a collection of volunteers and starting a new project in a
community in the same niche. Your blog provided us beneficial
information to work on. You have done a extraordinary job!
Monaco Trøje
Your means of telling all in this post is really fastidious, every
one be capable of effortlessly be aware of it, Thanks a lot.
I'll immediately clutch your rss as I can't to find your e-mail subscription hyperlink or e-newsletter service.
Do you have any? Kindly let me recognize in order that I may subscribe.
Thanks. Tjeck Tröja
Hi, i read your blog occasionally and i own a similar one
and i was just wondering if you get a lot of spam feedback?
If so how do you reduce it, any plugin or anything you can suggest?
I get so much lately it's driving me mad so any support is very
much appreciated.
We stumbled over here different web page and thought I might as well check things out.
I like what I see so now i am following you. Look forward to finding out about your
web page yet again. atletico madrid drakt
Attractive component to content. I just
stumbled upon your site and in accession capital to assert that I
acquire actually loved account your weblog posts. Any way I will
be subscribing on your feeds and even I fulfillment you access persistently quickly.
Fotbollströjor Barn Med Namn Och Nummer
I think everything said was very reasonable.
But, think about this, suppose you added a little content?
I mean, I don't want to tell you how to run your
website, however suppose you added a title that makes
people want more? I mean Understanding Orchard's Database is
kinda plain. You ought to look at Yahoo's home page and note how they create article
titles to grab people interested. You might try adding a video
or a related pic or two to get readers excited about everything've
written. In my opinion, it would make your website a
little bit more interesting.
Right here is the perfect webpage for anyone who would like to find out about this topic.
You realize so much its almost tough to argue with you (not that I personally
will need to…HaHa). You definitely put a new spin on a topic that's been written about for ages.
Wonderful stuff, just great! Kjøp Barcelona Fotballdrakt
Hi, just wanted to tell you, I liked this post. It was funny.
Keep on posting! Köp Juventus Matchtröjor På Nätet
Everything is very open with a clear description of the challenges.
It was truly informative. Your site is very helpful.
Thank you for sharing!
Hi there I am so grateful I found your site, I really found you
by mistake, while I was looking on Aol for something else, Nonetheless I am here now and would
just like to say many thanks for a tremendous post and
a all round exciting blog (I also love the theme/design), I don’t have time to go through it all at
the minute but I have book-marked it and also included your RSS feeds, so when I
have time I will be back to read more, Please do keep up the excellent b.
atletico madrid trøje luis
Very good article. I will be facing a few
of these issues as well.. Maglie calcio poco prezzo
I've been surfing online more than 4 hours today, yet
I never found any interesting article like yours. It is pretty worth enough for me.
Personally, if all website owners and bloggers made good content as
you did, the web will be much more useful than ever before.
Maglia Roma Radja Nainggolan Poco Prezzo
Hello there, I believe your website could possibly be having
web browser compatibility issues. Whenever I take a look at your web site
in Safari, it looks fine however when opening in I.E., it has some overlapping issues.
I merely wanted to give you a quick heads up! Aside from
that, excellent blog!
This paragraph will assist the internet users for setting up new website or even a blog from start to end.
An outstanding share! I have just forwarded this onto a coworker who was conducting a little homework on this.
And he actually bought me lunch because I discovered it for him...
lol. So let me reword this.... Thank YOU for the meal!! But yeah, thanks for spending some time to discuss this
issue here on your internet site.
I'm really enjoying the theme/design of your site. Do you
ever run into any internet browser compatibility
problems? A number of my blog visitors have complained about my blog not
operating correctly in Explorer but looks great in Firefox.
Do you have any suggestions to help fix this issue?
Thanks for sharing your thoughts on how to download minecraft.
Regards
There is certainly a lot to find out about this subject.
I really like all the points you made.
This design is incredible! You obviously know how to keep a reader amused.
Between your wit and your videos, I was
almost moved to start my own blog (well, almost...HaHa!) Wonderful job.
I really loved what you had to say, and more than that, how you presented it.
Too cool!
Hello, Neat post. There's an issue with your site in web explorer, may test this?
IE nonetheless is the marketplace leader and
a large section of people will leave out your fantastic writing because of this problem.
You made some good points there. I checked on the net
to learn more about the issue and found most
people will go along with your views on this website.
Very good post! We will be linking to this great content
on our site. Keep up the great writing.
My brother recommended I might like this website. He was
totally right. This put up actually made my day. You cann't believe simply
how much time I had spent for this information!
Thanks!
Hello I am so delighted I found your website, I really found you by error, while I was searching on Askjeeve for something else, Anyways I am here now and would just like to say cheers for a tremendous
post and a all round exciting blog (I also love the theme/design), I don’t have time to look over it all at
the minute but I have book-marked it and also added your RSS feeds,
so when I have time I will be back to read much more, Please do keep up the superb work.
Great article. I am going through a few of these issues
as well..
You ought to take part in a contest for one of the finest blogs on the net.
I most certainly will recommend this web site!
This blog was... how do I say it? Relevant!!
Finally I have found something which helped me.
Cheers!
Excellent post. I was checking continuously this blog and I am impressed!
Very useful info specifically the last part :) I care for
such info much. I was looking for this certain info for a very long time.
Thank you and good luck.
Hi there, this weekend is nice designed for me, because this occasion i am reading this great educational post here at
my home.
Quality articles or reviews is the crucial to invite the people to go to see the web site, that's what this
site is providing.
Hey there! This is kind of off topic but I need some guidance from an established blog.
Is it hard to set up your own blog? I'm not very techincal but I can figure things out pretty quick.
I'm thinking about making my own but I'm not sure where to start.
Do you have any tips or suggestions? Appreciate it
Thanks , I have just been looking for information approximately this topic for ages and yours is the greatest I've discovered so far.
But, what concerning the bottom line? Are you sure concerning the supply?
When some one searches for his required thing,
so he/she wishes to be available that in detail, so that thing
is maintained over here.
It's genuinely very complex in this busy life to listen news on Television, so I only use world wide web for that reason, and
obtain the most up-to-date information.
Howdy very nice website!! Guy .. Excellent .. Amazing
.. I'll bookmark your web site and take the feeds additionally?
I'm glad to search out numerous useful information here in the put up, we'd like develop extra
techniques on this regard, thank you for sharing.
. . . . .
This paragraph offers clear idea designed for the new users of blogging, that really how to do blogging and site-building.
hi!,I like your writing so a lot! proportion we keep up a correspondence more approximately
your post on AOL? I need a specialist in this house
to solve my problem. May be that's you! Having a look ahead to peer you.
Hi there! Would you mind if I share your blog with my twitter group?
There's a lot of people that I think would really appreciate your content.
Please let me know. Many thanks
Thanks , I've recently been searching for info approximately
this topic for ages and yours is the greatest I have found out so far.
But, what about the bottom line? Are you sure in regards to the source?
Its like you read my mind! You appear to know so much about this, like you wrote the book in it or
something. I think that you could do with some pics to drive the
message home a little bit, but other than that, this is
wonderful blog. A great read. I will definitely be back.
Hello, i think that i saw you visited my blog so i came to “return the favor”.I'm trying to find things
to enhance my website!I suppose its ok to use some of your ideas!!
Hello, i think that i saw you visited my blog so i came to “return the favor”.I
am trying to find things to improve my website!I suppose its ok to use a few of
your ideas!!
I was wondering if you ever considered changing the structure of your site?
Its very well written; I love what youve got to say.
But maybe you could a little more in the way of content
so people could connect with it better. Youve got an awful lot of text for only having one
or 2 images. Maybe you could space it out better?
Appreciation to my father who shared with
me on the topic of this web site, this web site is genuinely remarkable.
Great website. Lots of helpful information here. I'm sending it to a few pals ans additionally sharing in delicious.
And obviously, thanks for your sweat!
Heya i'm for the first time here. I found this board and I
find It truly useful & it helped me out much. I hope to give something back and help others like you aided me.
Hey just wanted to give you a quick heads up. The text in your content seem to be running off the screen in Safari.
I'm not sure if this is a format issue or something to do with
web browser compatibility but I thought I'd post to let you
know. The style and design look great though!
Hope you get the issue fixed soon. Cheers
Hi there, You have done an excellent job. I will definitely digg it and personally recommend
to my friends. I'm sure they'll be benefited from this website.
Very quickly this web page will be famous amid all blog people, due to it's nice articles or reviews
Ahaa, its fastidious conversation on the topic of this piece of writing here at this webpage, I have
read all that, so at this time me also commenting at
this place.
If you would like to grow your familiarity only keep visiting this site and be
updated with the hottest news update posted here.
Pretty nice post. I just stumbled upon your blog and wished to say that I have really enjoyed browsing your blog posts.
After all I'll be subscribing to your rss
feed and I hope you write again soon!
Hey There. I discovered your weblog the usage of msn. That is a really smartly
written article. I will be sure to bookmark it and return to read extra of your useful information.
Thanks for the post. I'll certainly comeback.
I think this is one of the most important info for me.
And i'm glad reading your article. But wanna remark
on few general things, The site style is perfect, the articles is
really great : D. Good job, cheers
It's an awesome paragraph in support of all the web viewers; they will obtain benefit from it I am sure.
Hello, its pleasant article concerning media print, we all be familiar with media is a impressive source of information.
Pretty! This was an extremely wonderful post. Thank you for providing this
information.
Undeniably imagine that which you said. Your favourite reason seemed to be at the
web the easiest factor to remember of. I say to
you, I certainly get irked while people think about issues that they just do not realize about.
You controlled to hit the nail upon the top and also outlined out the entire thing without having side-effects , other people can take a signal.
Will probably be again to get more. Thanks
Saved as a favorite, I like your blog!
It's going to be end of mine day, except before ending I am reading this
wonderful article to improve my knowledge.
Hi, yes this paragraph is truly good and I have learned lot of things from it on the topic of blogging.
thanks.
Good answer back in return of this difficulty with firm arguments and
describing all concerning that.
Your method of explaining everything in this article is truly nice,
all be able to effortlessly understand it, Thanks a lot.
I every time used to read paragraph in news papers but now as I am a user of web therefore from now I am using
net for posts, thanks to web.
Hi, I do think this is an excellent web site. I stumbledupon it
;) I may revisit once again since I book-marked it.
Money and freedom is the greatest way to
change, may you be rich and continue to help others.
I don't even know how I ended up here, but I thought this post was good.
I do not know who you are but certainly you're going to a famous blogger if you are not already
;) Cheers!
It's not my first time to go to see this site, i am visiting this web site dailly and get good
facts from here every day.
It's a shame you don't have a donate button! I'd without a doubt
donate to this fantastic blog! I guess for now i'll
settle for book-marking and adding your RSS feed to
my Google account. I look forward to fresh updates and will talk about
this website with my Facebook group. Chat soon!
I am actually pleased to read this weblog posts which consists
of tons of helpful information, thanks for providing these data.