How to create custom HqlQuery with order by content item field?

Topics: Core
Jul 1, 2014 at 8:05 AM
Edited Jul 1, 2014 at 2:02 PM
Orchard 1.6

I need to select 3 content item ordered by custom field.
I start from code
var itm = _icm.HqlQuery().ForType("VideoItem").ForVersion(VersionOptions.Published);
This content type I made from web interface. There are field PubDate.
I need sort it desc by PubDate and slice first 3 item.

How I can order it by field PubDate in my Query?
Jul 2, 2014 at 11:51 AM
Working solution was found here
And example:
var matchingEventItems = ContentManager.HqlQuery().ForType("EventItem").ForVersion(VersionOptions.Published);

var year = 2013; //just imagine I pulled these in from the query string ;)
var month = 3;

long minTicks = new DateTime(year, month, 1).ToUniversalTime().Ticks;
long maxTicks = new DateTime(year, month, 1).AddMonths(1).AddDays(-1).ToUniversalTime().Ticks;

var 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)              
        )
    )                
);