2

I am having with broker query in web 8.5, the fields names are getting lost while forming the query.

Query build using API:

var query = new Query();
var criteria = new List<Criteria>();
var bySchema = new ItemSchemaCriteria(300);
criteria.Add(new PublicationCriteria(8));
var templateCriteria = new ItemTemplateCriteria(4787);
criteria.Add(templateCriteria);
var validityStart = new CustomMetaValueCriteria(new CustomMetaKeyCriteria("ValidityStart"), DynConfig.GetCurrentCMSDate(), Criteria.GreaterThanOrEqual);
var validityEnd = new CustomMetaValueCriteria(new CustomMetaKeyCriteria("ValidityEnd"), DynConfig.GetCurrentCMSDate(), Criteria.LessThanOrEqual);
criteria.Add(CriteriaFactory.NotIn(CriteriaFactory.Or(validityStart, validityEnd)));
criteria.Add(bySchema);
query.Criteria = CriteriaFactory.And(criteria.ToArray());
var componentsUris = query.ExecuteQuery();

But, when I ran the profiler, I could see that ValidityStart and ValidityEnd are not coming the query.

declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,N'@P0 int,@P1 int,@P2 int,@P3 datetime2,@P4 datetime2,@P5 int',N'select distinct TOP(@P0)
 itemmeta0_.PUBLICATION_ID as col_0_0_, itemmeta0_.ITEM_REFERENCE_ID as col_1_0_, itemmeta0_.ITEM_TYPE as col_2_0_,
 itemmeta0_.ITEM_REFERENCE_ID as col_3_0_ from ITEMS itemmeta0_ cross join COMPONENT componentm1_ inner join 
 ITEMS componentm1_1_ on componentm1_.PUBLICATION_ID=componentm1_1_.PUBLICATION_ID and 
 componentm1_.NAMESPACE_ID=componentm1_1_.NAMESPACE_ID and componentm1_.ITEM_REFERENCE_ID=componentm1_1_.ITEM_REFERENCE_ID 
 cross join COMPONENT_PRES_META_DATA componentp2_ where itemmeta0_.PUBLICATION_ID=@P1 and itemmeta0_.ITEM_REFERENCE_ID=componentp2_.COMPONENT_REF_ID and 
 itemmeta0_.PUBLICATION_ID=componentp2_.PUBLICATION_ID and componentp2_.COMPONENT_TEMPLATE_ID=@P2 and 
 (itemmeta0_.ITEM_REFERENCE_ID not in  (select distinct itemmeta3_.ITEM_REFERENCE_ID from ITEMS itemmeta3_
 cross join CUSTOM_META custommeta4_ where itemmeta3_.PUBLICATION_ID=custommeta4_.PUBLICATION_ID and itemmeta3_.ITEM_REFERENCE_ID=custommeta4_.ITEM_ID and itemmeta3_.ITEM_TYPE=custommeta4_.ITEM_TYPE and custommeta4_.KEY_DATE_VALUE>=@P3 or 
 itemmeta3_.PUBLICATION_ID=custommeta4_.PUBLICATION_ID and itemmeta3_.ITEM_REFERENCE_ID=custommeta4_.ITEM_ID 
 and itemmeta3_.ITEM_TYPE=custommeta4_.ITEM_TYPE and custommeta4_.KEY_DATE_VALUE<=@P4)) 
 and itemmeta0_.ITEM_REFERENCE_ID=componentm1_.ITEM_REFERENCE_ID and itemmeta0_.PUBLICATION_ID=componentm1_.PUBLICATION_ID 
 and componentm1_.SCHEMA_ID=@P5 order by itemmeta0_.ITEM_REFERENCE_ID ASC                                              
 ',300,8,4787,'2017-03-22 07:48:37.7670000','2017-03-22 07:48:38.2360000',4755
select @p1
  • 1
    CriteriaFactory and the combination of and/or/etc. criterias has a lot of problems, as it needs a lot of table joins in the database. I recommend you to try a simple criteria to test CustomMetaValueCriteria like var criteria = new CustomMetaValueCriteria(new CustomMetaKeyCriteria("ValidityStart"), DynConfig.GetCurrentCMSDate(), Criteria.GreaterThanOrEqual); query.Criteria = criteria; – Raúl Escudero Mar 22 '17 at 11:09
  • @Rafeeq: It an issue with mirco services API but it's working fine on legacy SDL we8 .NET + Java on the same worker process. We raised the same to SDL support we will update here once we got the response from SDL. – Velmurugan Mar 22 '17 at 12:23
  • @Velu, great.. we will have multiples tickets to SDL then, I just wanted to know if anyone in the community faced the same issue.. , we will wait and see then – Rafeeq Mar 22 '17 at 13:37
  • @Rafeeq: This is issue very specific scenario implementation to fail due to any dcp component has multiple meta fields with the same kind of value then this issue will occur. but still it's an issue with broker query API For example: Is breaking news? Yes Is featured as photos? Yes Is featured as Videos? Yes – Velmurugan Mar 22 '17 at 14:10
  • @Velu, Yes.. The query give result if you have different values, but ideally query is getting formed wrongly even with single or multiple meta criteria... – Rafeeq Mar 22 '17 at 14:30
2

I have faced same type issue with multiple CustomMetaValueCriteria. So to overcome that, I have changed my query like below. For me it is working fine now. Could you try the below code once -

This is Just a sample, you can change it according to your requirement -

Query query = new Query();
            ItemSchemaCriteria schemaCriteria = new ItemSchemaCriteria(300); PublicationCriteria publicationCriteria = new PublicationCriteria(8);
            ItemTemplateCriteria templateCriteria = new ItemTemplateCriteria(4787);
            CustomMetaValueCriteria validityStart = new CustomMetaValueCriteria(new CustomMetaKeyCriteria("ValidityStart"), DynConfig.GetCurrentCMSDate(), Criteria.GreaterThanOrEqual);
            CustomMetaValueCriteria validityEnd = new CustomMetaValueCriteria(new CustomMetaKeyCriteria("ValidityEnd"), DynConfig.GetCurrentCMSDate(), Criteria.LessThanOrEqual);

            Criteria[] criteriaList1 = { schemaCriteria, publicationCriteria, templateCriteria, validityStart };
            Criteria[] criteriaList2 = { schemaCriteria, publicationCriteria, templateCriteria, validityEnd };
            AndCriteria firstCriteria = CriteriaFactory.And(criteriaList1);
            AndCriteria secondCriteria = CriteriaFactory.And(criteriaList2);
            OrCriteria mainCriteriaCriteria = new OrCriteria(firstCriteria, secondCriteria);
            NotInCriteria finalCriteria = new NotInCriteria(mainCriteriaCriteria); 
query.Criteria = finalCriteria; 
var componentsUris = query.ExecuteQuery();

Let me know if it works fine for you.

  • Thank you Sayantan :-) It takes a long time to execute and returns nothing.. – Rafeeq Mar 23 '17 at 11:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.