Broker Query with sort criteria over optional field

Question

I'm obtaining a list of 5 components from the broker using a simple Query:

Query myQuery = new Query();

Criteria myCriteria = null;
PublicationCriteria pubRelCriteria = new PublicationCriteria(iPublicationID);
ItemSchemaCriteria schemaRelCriteria = new ItemSchemaCriteria(id_schema);
ItemTemplateCriteria templateCriteria = new ItemTemplateCriteria(id_template);

// glue the metadata together
AndCriteria aCriteria = new AndCriteria(schemaRelCriteria, pubRelCriteria);
aCriteria = new AndCriteria(aCriteria, templateCriteria);
myCriteria = aCriteria;
myQuery.Criteria = myCriteria;

//Sort it
CustomMetaKeyColumn customMetaKeyColumnDate = new CustomMetaKeyColumn("Date", MetadataType.DATE);

myQuery.AddSorting(new SortParameter(customMetaKeyColumnDate, SortParameter.Descending));

// Display
String[] itemURI = myQuery.ExecuteQuery();

The problem is that the metadatafield "Date" that I need to use as the sort criteria is not mandatory, then if some component is published with this metadatafield empty, this component is not returned in the query.

Is there some way to return all the components despite the field of the sort criteria is empty?

Thanks!

Answer 1

Not sure, if you have already found the answer.

A workaround would be not to include the optional field in the search criteria.

ComponentFactory cpf = new ComponentFactory();
Query query = new Query();

//16 for component, 32 for CTs, 64 for Page, here we are dealing with type 16 i.e. components only
ItemTypeCriteria componentsOnly = new ItemTypeCriteria(ItemType.Component.GetHashCode());
SchemaTitleCriteria basedOnSchema = new SchemaTitleCriteria(“Schema Name”);
PublicationCriteria basedOnPublication = new PublicationCriteria(pubId); 
//pubId needs to be provided
//Add template criteria if required
Criteria criteria = CriteriaFactory.And(new Criteria[] { componentsOnly, basedOnSchema, basedOnPublication });

//Setting Criteria for the query object, Schema Name, Pub Ids etc
query.Criteria = criteria;

//Condition for latest published item for multiple returns, which will be available for all copms
SortParameter sortParameter = new SortParameter(
SortParameter.ItemLastPublishedDate,
SortParameter.Descending);
query.AddSorting(sortParameter);

//Executes Query on DB
var results = query.ExecuteQuery().ToList();
 List<IComponent> componentList = new List<IComponent>();
//Now apply custom sorting on the results retrieved
if (results != null && results.Count > 0)
{
    foreach (string tcmId in results)
    {
        try
        {
            componentList.Add(cpf.GetComponent(tcmId));
        }
        catch
        {
        }
    }
}

 var actualComplist = componentList.OrderByDescending(x => x.MetadataFields["Date"].Value); //Assuming Date is your field name

For setting up the criteria for any broker query you can refer here.

Let me know if this helps or how you have solved it !! Thanks !!