Querying Orchard fields with HQL

Before projections, the official word on fields was that they weren’t for querying (they are stored in an XML blob on the content item record). Projections enabled field querying for the first time, through special tables that index field contents: Orchard_Projections_DecimalFieldIndexRecord, Orchard_Projections_DoubleFieldIndexRecord, Orchard_Projections_IntegerFieldIndexRecord, and Orchard_Projections_StringFieldIndexRecord. Each table is specialized for one underlying value type.

Let’s take the example of a Boolean field named Archived, that I use on the Nwazet to retire products. We want to build a query returning the products that have not been archived. Booleans are indexed into the same table as integers, Orchard_Projections_IntegerFieldIndexRecord. We need to join on this table, through the Orchard_Projections_FieldIndexPartRecord table, with a constraint on the property name that we want:

join ci.FieldIndexPartRecord fieldIndexPartRecord
left outer join fieldIndexPartRecord.IntegerFieldIndexRecords ProductPartArchived
  with ProductPartArchived.PropertyName = 'ProductPart.Archived.'

Notice that the join is a left outer join. This is because if the Archived bit has never been set on a product, we still want the product to appear in the list of results.

Once the join is done, all we have to do is to restrict the results to null and 0 values:

(ProductPartArchived.Value is null or ProductPartArchived.Value = 0)

Here’s the complete query:

select distinct civ.Id
from Orchard.ContentManagement.Records.ContentItemVersionRecord civ
  join civ.ContentItemRecord ci
  join ci.ContentType ct
  join ci.FieldIndexPartRecord fieldIndexPartRecord
  left outer join fieldIndexPartRecord.IntegerFieldIndexRecords ProductPartArchived
    with ProductPartArchived.PropertyName = 'ProductPart.Archived.'
  join ci.CommonPartRecord as commonPartRecord
where (ct.Name = 'Product')
  AND (ProductPartArchived.Value is null or ProductPartArchived.Value = 0)
  AND (civ.Published = True)