I have a requirement to sort on optional custom date for components. I have 25 components matching criteria and 1 component with published date (metadata field) empty, but its returns only 24 components.if i remove sortparameter , then it returns all 25 components.I am using below broker query to retrieve components with sortparameter:-

            Query query = new Query();
            Criteria TopNewsArticleCriteria = null;
            PublicationCriteria pubCriteria = new PublicationCriteria(pubID);
            ItemSchemaCriteria IsArticle = new ItemSchemaCriteria(articleID);
            TaxonomyKeywordCriteria taxonomyKeywordCriteria = new TaxonomyKeywordCriteria(strTaxnomyURI, strTaxKeywordURI, false);
            AndCriteria compCriteria = new AndCriteria(pubCriteria, IsArticle);
            AndCriteria KeywordCriteria = new AndCriteria(taxonomyKeywordCriteria, compCriteria);
            TopNewsArticleCriteria = KeywordCriteria;
            query.Criteria = TopNewsArticleCriteria ;

        //First Criteria Sort by published date Meta
        CustomMetaKeyColumn customMetaKeyColumnDate = new CustomMetaKeyColumn("Published_Date", MetadataType.DATE);
        query.AddSorting(new SortParameter(customMetaKeyColumnDate, SortParameter.Descending));


        string[] ItemUris = query.ExecuteQuery();
        results = ItemUris.ToList();

I am not able to fetch components published with empty metadatafield (Published_Date=""). I tried to enable Sql logging as suggested in post (Broker Query with sort criteria over optional field) and tried executing the logged sql. please find the below query got generated:-

select distinct itemmeta0_.PUBLICATION_ID as col_0_0_, itemmeta0_.ITEM_REFERENCE_ID as col_1_0_, itemmeta0_.ITEM_TYPE as col_2_0_, custommeta3_.KEY_DATE_VALUE as col_3_0_, itemmeta0_.LAST_PUBLISHED_DATE as col_4_0_ from ITEMS itemmeta0_ cross join ITEM_CATEGORIES_AND_KEYWORDS relatedkey1_ cross join COMPONENT componentm2_ inner join ITEMS componentm2_1_ on componentm2_.PUBLICATION_ID=componentm2_1_.PUBLICATION_ID and componentm2_.ITEM_REFERENCE_ID=componentm2_1_.ITEM_REFERENCE_ID cross join CUSTOM_META custommeta3_ where itemmeta0_.ITEM_REFERENCE_ID=relatedkey1_.ITEM_REFERENCE_ID and itemmeta0_.PUBLICATION_ID=relatedkey1_.PUBLICATION_ID and relatedkey1_.KEYWORD_ID=8316 and relatedkey1_.TAXONOMY_ID=1528 and relatedkey1_.PUBLICATION_ID=55 and itemmeta0_.PUBLICATION_ID=55 and itemmeta0_.ITEM_REFERENCE_ID=componentm2_.ITEM_REFERENCE_ID and itemmeta0_.PUBLICATION_ID=componentm2_.PUBLICATION_ID and componentm2_.SCHEMA_ID=8228 and custommeta3_.PUBLICATION_ID=itemmeta0_.PUBLICATION_ID and custommeta3_.ITEM_ID=itemmeta0_.ITEM_REFERENCE_ID and custommeta3_.ITEM_TYPE=itemmeta0_.ITEM_TYPE and custommeta3_.KEY_NAME='Published_Date' order by custommeta3_.KEY_DATE_VALUE DESC

which return only 24 components.

further debuging, i tried executing below query to see if CUSTOM_META table has a record with empty field or not:-

select * from CUSTOM_META where CUSTOM_META.KEY_NAME='Published_Date' and PUBLICATION_ID = 55

which didn't return the component with empty metadata field,

It looks like its a crossjoin ( not left join) on CUSTOM_META table which is causing problem.

Is there a way to retrieve components with sortparameter on optional metadata fields?


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.