HQL query help needed

Topics: Customizing Orchard, Writing modules
Mar 31, 2012 at 11:50 AM

Hi,

I am writing a custom module that will make my (and many others, i believe) life easier. But I am stuck somewhere. Lets say, I want to get the list of 'female' 'authors' in a controller. But, author is a content type created using Orchard admin panel. And, it has field of enumeration type named Gender. So, the best thing I came up with is:

var femaleAuthors = _contentManager.HqlQuery().ForType("Author").List().Cast<dynamic>().Where(x => x.Author.Gender.Value == "Female").ToList();

That works pretty fine. But, as you can see, it is actually fetching all contents of type Author and then filtering in memory. Can I do the whole thing in HQL so that it fetches exactly the contents I want? I believe it is possible, right?

Then, there is a second problem. Say, I would like to fetch the authors who has the word "Custom" in their title (from TitlePart). I know I can do that easily by using ForPart<T>() when building HQL query. But, what if the I dont know the actual type of part, just the name? Well, it is still possible to do using the approach above:

var customAuthors = _contentManager.HqlQuery().ForType("Author").List().Cast<dynamic>().Where(x => x.TitlePart.Title.Contains("Custom")).ToList();

It has the same problem of fetching things in memory and then filtering it.

Well, the reason I am doing things this way, I will have 4 string variables, namely contentType, partName, propertyName, valueToCompareWith and a boolean variable indicating whether the property is a field of the content type or it comes from a part of the content type. Because, if it's a field, then i can access the property value as ContentItem.ContentTypeName.PropertyName.Value. And, if it is a part, i can access by ContentItem.PartName.PropertyName.

Well, given content type, part name, and property name as string variables, I can access the fields using reflection in the above two code samples. I did not include them for cleanliness of code. But, is it possible to generate HQL query to get the exact result for above explained scenario?

By the way, once this module is complete, I will announce it here too.

And, did I ever thank you guys for Orchard? Thanks for such a wonderful thing. :)

- Zp Bappi.

Apr 2, 2012 at 7:02 PM
Edited Apr 5, 2012 at 4:26 PM

Anyone? I mean, it sounds like a basic thing. That is, get ContentItems- filtering based on either field value or part record. Where all of them are dynamic. That is, content type is created from admin panel, fields are added from admin panel, and part is also created from admin panel. Is it really impossible to do with HqlQuery in 1.4? Or, do I have to do the filtering in memory?

Jan 22, 2013 at 5:19 PM

I have the same problem. Please help.

Jan 22, 2013 at 6:03 PM

Maybe you can do this using projections instead of HQL? 

Jan 22, 2013 at 6:48 PM
Edited Jan 22, 2013 at 6:52 PM

I do this :)

    var contentManager = WorkContext.Resolve();
    var propertyName = String.Join(".", "Project", "DateEnd", "");
    var query = contentManager.HqlQuery().ForType("Project").ForVersion(VersionOptions.Published);
    query = query.Where(
        alias => alias.ContentPartRecord().Property("IntegerFieldIndexRecords", propertyName.ToSafeName()),
        predicate => predicate.Eq("PropertyName", propertyName));
    query = query.OrderBy(
        alias => alias.ContentPartRecord().Property("IntegerFieldIndexRecords", propertyName.ToSafeName()),
        order => order.Desc("Value"));
    var items = query.Slice(0, 0);
Mar 21, 2013 at 7:48 AM
Edited Mar 21, 2013 at 7:49 AM
For anyone struggling to make sense of neTp9c's post above, the best way to get your head around it is to create a Query through the Admin UI in Orchard and then step through the code. You can see it flow through the ProjectionPart driver where it calls the ProjectionManager's __GetContentItems__(), GetContentQueries and GetContentItems, and then at some stage the ApplyFilter method in ContentFieldsFilter.cs (in Orchard.Projections/Providers/Filters), and in this case since it's for a Date Time field, GetFilterPredicate method in DateTimeFilterForm.cs

Have fun following the rabbit hole!
Jul 2, 2014 at 11:53 AM
In Orchard 1.6 it not work (wrong syntax).

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