Orchard - Query Custom Fields in Content Item

Question

I have a Content Type "News" with custom field "NewsDate" (DateTimeField) and "Active" (BooleanField)

Now I'm need to get 3 active atimes order desc by NewsDate

Get all news, make them toList() and from there manipulate the data is not a solution.

P.S. I need to do something like:

        var items = contentManager
            .Query(Entities[PageType.Press])
            .OrderByDescending<CommonPartRecord, DateTime?>(record => record.PublishedUtc)
            .Slice(0, 3);

but instead of PublishedUTC use my custom field "NewsDate" and add Active == true, However it is not possible due to Orchard architecture of storing custom data in a separate field as XML data.

UPDATED: In a nutshell I want to generate from code behind the following Query:

DECLARE @temp as TABLE(id int, xmldata xml)
INSERT @temp VALUES(1,'<Data><News><NewsDate>07/14/2011 11:42:00</NewsDate><Link Title="" DisplayText="" Link="www.example.com" OpenInNewTab="True">www.example.com</Link></News></Data>')
INSERT @temp VALUES(2,'<Data><News><NewsDate>07/11/2011 12:11:00</NewsDate><Link Title="" DisplayText="" Link="www.example.com" OpenInNewTab="True">www.example.com</Link></News></Data>')
INSERT @temp VALUES(3,'<Data><News><NewsDate>02/21/2012 16:56:00</NewsDate><Link Title="" DisplayText="" Link="www.example.com" OpenInNewTab="True">www.example.com</Link><NewsLink></NewsLink></News></Data>')

SELECT  
 TOP 3 [id],
[xmldata].value('(Data/News/NewsDate)[1]', 'datetime') as NewsDate
FROM @temp
ORDER BY NewsDate DESC

P.S. I looked through the code for DynamicContentQueryTests, however all the examples uses the Part, and in my case Fields are just in the ContentItem: E.g. News content type contains NewsDate field (datetime field) and some parts as well

Any suggestions are greatly appreciated.

Answer 1

To get the values of fields that have been attached directly to a Content Item, you need to first look for the Content Part with the same name as the item, which is created by Orchard. So in your case in the Parts list for each News Content Item you'll find a part called "NewsPart", and inside this the Fields property will have your NewsDate and Active fields.

However, like you say Orchard serializes the field values into XML for storage to prevent it having to change the database structure every time you add/remove a field to/from a content type. For this reason, querying and ordering by fields is not recommended because all the serialized data needs to be de-serialized for each Content Item. If you want to be able to do this, the best way is to make your own Content Part with a Content Part Record and use your own table for storage, then you can do this:

contentManager.Query<NewsPart, NewsPartRecord>()...

...and query/sort on whatever values you like.

Answer 2

Querying fields is possible since 1.4 through Projector and underlying index tables and new APIs on Content Manager. Your simplest bet actually may be to create a projection.

Answer 3

Bertrand is correct. Check this link : http://www.davidhayden.me/blog/projector-module-in-orchard-cms. You can implement your requirements easily with Projection Module. The link will tell you the steps to do that. If you don't want a widget, you can create a page with your query too. Once the module is enabled, you will see the option to create a Projection Page in the left hand side navigation.

EDIT :

Can't you simply parse the XML? You can query your content type 'News'. Something like -
var contentItems = contentManager.Query<ContentPart>("News").Join<TitlePartRecord>().Join<AutoroutePartRecord>().Join<CommonPartRecord>().List();

Once you have that, you can access the XML data by :

foreach (var item in contentItems)
  {
    var contentItem = (ContentItem)item.ContentItem;
    ContentItemVersionRecord contentItemRecord = contentItem.VersionRecord; 
    string data = contentItemRecord.Data; 
    //Call some function here to parse 'data' and store the object in the list. 
  }

'data' has the information in XML format that you need. You can parse it and then store the objects in your list that you can order by your custom field.