Orchard CMS: HQL, Or How To Query Based On Content Fields
I know, I know; you're thinking - "Why do I even need to write my own queries, when there's a perfectly awesome Query and Projection module in Orchard I can leverage? Well, if you're building an event calendar, for example, you might want to generate your queries on the fly based on query string parameters; in which case, I don't know if creating a bunch of Query items is the nicest way to go. (If there's a nicer, better way of working with parameters/tokens in Queries, let me know; ie if you can add your own custom Tokens / handling to the Query Edit page to have more than Url.Absolute, but I digress)
EDIT: Pretty much immediately after I wrote this post, I noticed that there is a Request / Query String you can hook into when using your queries.
Which pretty much makes this post a little redundant for the original problem it was supposed to solve, but I'll leave it if you're interested in the internals. But basically, you can create a Query that varies based on query string, which you can use in a Projection. Problem solved!
So after much step-through debugging and source code diving, I present to you "how Orchard handles Projections / Queries in the back end and how you can too, with HQL".
Let's say I want to get a bunch of content items, where the field MyDate lies in a specific month.
First of all, you create a HQL query:
var matchingEventItems = ContentManager.HqlQuery().ForType("EventItem").ForVersion(VersionOptions.Published);
Then you add some where conditions (you can also add sort conditions, but I leave that as an exercise to work out from the Orchard source code; it's pretty similar to the Where conditions)
year = 2013; //just imagine I pulled these in from the query string ;)
month = 3;
long minTicks = new DateTime(year, month, 1).ToUniversalTime().Ticks;
long maxTicks = new DateTime(year, month, 1).AddMonths(1).AddDays(-1).ToUniversalTime().Ticks;
matchingItems = matchingItems.Where(
a => a.ContentPartRecord<FieldIndexPartRecord>().Property("IntegerFieldIndexRecords", "MyContentTypeMyDate"), //gone through "safename", whatever that is - see ContentFieldsFilter.cs in projections
p => p.And(
x => x.Eq("PropertyName", "MyContentType.MyDate."), //this comes from the following code in Orchard Source, but I don't know what storage name is for; propertyName = String.Join(".", part.Name, field.Name, storageName ?? "");
y => y.And(
date => date.Ge("Value", minTicks),
date => date.Le("Value", maxTicks)
)
)
);
It's a little bit like CAML from SharePoint, in which you provide lots of Ands, Ors and Eqs to build up your query tree. Note that you'll need to add a reference to Orchard.Projections for your module (so don't forget to add a dependency too).
And finally, you slice it to actually execute and get some items for your query. You'll get back IEnumerable<ContentItem>, which you can then use to your hearts' content. For example, you could pull out the Title for all these items:
foreach(var item in items)
{
string theTitle = item.As<TitlePart>().Title;
}
If you're looking to make different queries, the parameters / syntax for the HQL might be a little different - I suggest you put a breakpoint in ProjectionPartDriver and step through to see what's required.
Soren Ullidtz said
storageName is for when you're accessing data which isn't in the Value of the field. (Try querying the Text on a LinkField)
rtpHarry said
Thank you so much, you have dug me out of a hole I had been stuck in for 2 days.
I didn't think I would ever solve this issue but your snippet pointed me in the right direction!
Roland said
Thanks for this. This is very useful, however I think instead of using HqlQuery(), you can use Query() instead if not for making the code a bit shorter. E.g:
var matchingEvents = ContentManager.Query(VersionOptions.Published, "MyContentType")
.Join<FieldIndexPartRecord>()
.Where(
fieldIndex => fieldIndex.IntegerFieldIndexRecords.Any(
integerField => integerField.PropertyName == "MyContentType.MyDate."
&& integerField.Value >= minTicks
&& integerField.value <= maxTicks
)
)
.List();