7

Might be doing something stupid but basically I am not getting the results I would expect when using multiple NotInCriteria in a query. I only ever seem to have the one of the criteria excluded.

The code I am using to create the keywords to exclude part is as follows.

using (TaxonomyFactory tf = new TaxonomyFactory())
{
    foreach (string excludeKeyword in keywordsToExclude)
    {
        Keyword selectedKeyword = tf.GetTaxonomyKeyword(excludeKeyword);
        TaxonomyKeywordCriteria aKeywordCriteria = new 
                                         TaxonomyKeywordCriteria(selectedKeyword.TaxonomyUri,
                                                                 selectedKeyword.KeywordUri, 
                                                                 false);
        exclCiteria.Add(new NotInCriteria(aKeywordCriteria));
    }
}

Then add that to some other criteria and run

criteria.AddRange(exclCiteria);
Criteria queryCriteria = new AndCriteria(criteria.ToArray());

I have completed a stack trace on the cd_core log to see what query was actually being produce/run and found the following

2014-06-18 16:49:30,533 DEBUG SQL - select distinct itemmeta0_.PUBLICATION_ID as col_0_0_, itemmeta0_.ITEM_REFERENCE_ID as col_1_0_, itemmeta0_.ITEM_TYPE as col_2_0_, custommeta2_.KEY_DATE_VALUE as col_3_0_ 

from ITEMS itemmeta0_, COMPONENT componentm1_ inner join ITEMS componentm1_1_ on componentm1_.PUBLICATION_ID=componentm1_1_.PUBLICATION_ID and componentm1_.ITEM_REFERENCE_ID=componentm1_1_.ITEM_REFERENCE_ID, CUSTOM_META custommeta2_ 

where itemmeta0_.ITEM_TYPE=? and itemmeta0_.PUBLICATION_ID=? and (itemmeta0_.ITEM_REFERENCE_ID=componentm1_.ITEM_REFERENCE_ID and itemmeta0_.PUBLICATION_ID=componentm1_.PUBLICATION_ID and componentm1_.SCHEMA_ID=? or itemmeta0_.ITEM_REFERENCE_ID=componentm1_.ITEM_REFERENCE_ID and itemmeta0_.PUBLICATION_ID=componentm1_.PUBLICATION_ID and componentm1_.SCHEMA_ID=? or itemmeta0_.ITEM_REFERENCE_ID=componentm1_.ITEM_REFERENCE_ID and itemmeta0_.PUBLICATION_ID=componentm1_.PUBLICATION_ID and componentm1_.SCHEMA_ID=? or itemmeta0_.ITEM_REFERENCE_ID=componentm1_.ITEM_REFERENCE_ID and itemmeta0_.PUBLICATION_ID=componentm1_.PUBLICATION_ID and componentm1_.SCHEMA_ID=?) 

and (itemmeta0_.ITEM_REFERENCE_ID not in  (select distinct itemmeta3_.ITEM_REFERENCE_ID from ITEMS itemmeta3_, ITEM_CATEGORIES_AND_KEYWORDS relatedkey4_ where itemmeta3_.ITEM_REFERENCE_ID=relatedkey4_.ITEM_REFERENCE_ID and itemmeta3_.PUBLICATION_ID=relatedkey4_.PUBLICATION_ID and relatedkey4_.KEYWORD_ID=? and relatedkey4_.TAXONOMY_ID=? and relatedkey4_.PUBLICATION_ID=?)) 

and (itemmeta0_.ITEM_REFERENCE_ID not in  (select distinct itemmeta5_.ITEM_REFERENCE_ID from ITEMS itemmeta5_, ITEM_CATEGORIES_AND_KEYWORDS relatedkey6_ where itemmeta5_.ITEM_REFERENCE_ID=relatedkey6_.ITEM_REFERENCE_ID and itemmeta5_.PUBLICATION_ID=relatedkey6_.PUBLICATION_ID and relatedkey6_.KEYWORD_ID=? and relatedkey6_.TAXONOMY_ID=? and relatedkey6_.PUBLICATION_ID=?)) 

and (itemmeta0_.ITEM_REFERENCE_ID not in  (select distinct itemmeta7_.ITEM_REFERENCE_ID from ITEMS itemmeta7_, ITEM_CATEGORIES_AND_KEYWORDS relatedkey8_ where itemmeta7_.ITEM_REFERENCE_ID=relatedkey8_.ITEM_REFERENCE_ID and itemmeta7_.PUBLICATION_ID=relatedkey8_.PUBLICATION_ID and relatedkey8_.KEYWORD_ID=? and relatedkey8_.TAXONOMY_ID=? and relatedkey8_.PUBLICATION_ID=?)) 

and (itemmeta0_.ITEM_REFERENCE_ID not in  (select distinct itemmeta9_.ITEM_REFERENCE_ID from ITEMS itemmeta9_, ITEM_CATEGORIES_AND_KEYWORDS relatedkey10_ where itemmeta9_.ITEM_REFERENCE_ID=relatedkey10_.ITEM_REFERENCE_ID and itemmeta9_.PUBLICATION_ID=relatedkey10_.PUBLICATION_ID and relatedkey10_.KEYWORD_ID=? and relatedkey10_.TAXONOMY_ID=? and relatedkey10_.PUBLICATION_ID=?)) 

and (itemmeta0_.ITEM_REFERENCE_ID not in  (select distinct itemmeta11_.ITEM_REFERENCE_ID from ITEMS itemmeta11_, ITEM_CATEGORIES_AND_KEYWORDS relatedkey12_ where itemmeta11_.ITEM_REFERENCE_ID=relatedkey12_.ITEM_REFERENCE_ID and itemmeta11_.PUBLICATION_ID=relatedkey12_.PUBLICATION_ID and relatedkey12_.KEYWORD_ID=? and relatedkey12_.TAXONOMY_ID=? and relatedkey12_.PUBLICATION_ID=?)) 

and (itemmeta0_.ITEM_REFERENCE_ID not in  (select distinct itemmeta13_.ITEM_REFERENCE_ID from ITEMS itemmeta13_, ITEM_CATEGORIES_AND_KEYWORDS relatedkey14_ where itemmeta13_.ITEM_REFERENCE_ID=relatedkey14_.ITEM_REFERENCE_ID and itemmeta13_.PUBLICATION_ID=relatedkey14_.PUBLICATION_ID and relatedkey14_.KEYWORD_ID=? and relatedkey14_.TAXONOMY_ID=? and relatedkey14_.PUBLICATION_ID=?)) 

and custommeta2_.PUBLICATION_ID=itemmeta0_.PUBLICATION_ID and custommeta2_.ITEM_ID=itemmeta0_.ITEM_REFERENCE_ID and custommeta2_.ITEM_TYPE=itemmeta0_.ITEM_TYPE and custommeta2_.KEY_NAME=? order by custommeta2_.KEY_DATE_VALUE DESC

2014-06-18 16:49:30,533 TRACE IntegerType - binding '16' to parameter: 1
2014-06-18 16:49:30,533 TRACE IntegerType - binding '22' to parameter: 2
2014-06-18 16:49:30,533 TRACE IntegerType - binding '98' to parameter: 3
2014-06-18 16:49:30,533 TRACE IntegerType - binding '103' to parameter: 4
2014-06-18 16:49:30,533 TRACE IntegerType - binding '8563' to parameter: 5
2014-06-18 16:49:30,533 TRACE IntegerType - binding '100' to parameter: 6
2014-06-18 16:49:30,533 TRACE IntegerType - binding '7224' to parameter: 7
2014-06-18 16:49:30,533 TRACE IntegerType - binding '55' to parameter: 8
2014-06-18 16:49:30,533 TRACE IntegerType - binding '22' to parameter: 9
2014-06-18 16:49:30,533 TRACE IntegerType - binding '7224' to parameter: 10
2014-06-18 16:49:30,533 TRACE IntegerType - binding '55' to parameter: 11
2014-06-18 16:49:30,533 TRACE IntegerType - binding '22' to parameter: 12
2014-06-18 16:49:30,533 TRACE IntegerType - binding '7224' to parameter: 13
2014-06-18 16:49:30,533 TRACE IntegerType - binding '55' to parameter: 14
2014-06-18 16:49:30,533 TRACE IntegerType - binding '22' to parameter: 15
2014-06-18 16:49:30,533 TRACE IntegerType - binding '7224' to parameter: 16
2014-06-18 16:49:30,533 TRACE IntegerType - binding '55' to parameter: 17
2014-06-18 16:49:30,533 TRACE IntegerType - binding '22' to parameter: 18
2014-06-18 16:49:30,533 TRACE IntegerType - binding '7224' to parameter: 19
2014-06-18 16:49:30,533 TRACE IntegerType - binding '55' to parameter: 20
2014-06-18 16:49:30,549 TRACE IntegerType - binding '22' to parameter: 21
2014-06-18 16:49:30,549 TRACE IntegerType - binding '7224' to parameter: 22
2014-06-18 16:49:30,549 TRACE IntegerType - binding '55' to parameter: 23
2014-06-18 16:49:30,549 TRACE IntegerType - binding '22' to parameter: 24
2014-06-18 16:49:30,549 TRACE StringType - binding 'Date' to parameter: 25

For some reason the same keyword is being searched for in the not in statements i.e. params 7,10,13,16,19 and 22 are all the same. I have checked that the keywords in the keywords to exclude list are all correct and more importantly different but for some reason they are all passed through as the same keyword. Am I doing something stupid or is this a known issue. Any help would be greatly appreciated.

Thanks!

  • Can you confirm which version of Tridion you are using and what type of object exclCiteria is, please? – Jonathan Williams Jun 19 '14 at 12:31
  • Tridion 2011 SP1 and exclCiteria is defined as List<Criteria> excCiteria = new List<Criteria>(); – Matt Hill Jun 19 '14 at 13:16
  • It would be interesting to know which type your 'criteria' variable is and what else you put in the array. So, can you share that bit of code as well please? – Raimond Jun 20 '14 at 9:17
  • Hi Raimond. criteria again is just a List<Criteria> citeria = new List<Criteria>(); These are just separated out initially to try to make the code easier to follow. In the array there are just simple things like publication and schema filtering etc. However these are set based on various different things and the code section is quite large. Plus this code works on it's own and also the exclude criteria fails to work when used exclusively as the query's criteria so it's not an interaction between the two that causes this. – Matt Hill Jun 20 '14 at 11:51
4

Thought I would post this as it may help someone else if you run into this problem. Have received the following response from SDL support who solved the issue.

I have investigated further and observed that the code shared in previous notes was failing to include NotInCriteria when more than one keyword is used in excludeKeywords array. This was because the inner subquery goes on with multiple cross join due to AndCriteria, which can be corrected by using Orcriteria.

Following is the corrected code sample, which was tested and working fine as expected:

int publicationId = 12;
string categoryUri = "tcm:12-32-512";
string[] excludeKeywords = new String[] { "tcm:12-172-1024", "tcm:12-178-1024" };
Criteria includeCriteria = new PublicationCriteria(publicationId);
List<Criteria> excludeCriteria = new List<Criteria>();
foreach (String keywordUri in excludeKeywords)
{
    excludeCriteria.Add(new TaxonomyKeywordCriteria(categoryUri, keywordUri, true));
}
Criteria excludeAnd = new OrCriteria(excludeCriteria.ToArray());
Criteria finalCriteria = new AndCriteria(includeCriteria, new NotInCriteria(excludeAnd));
Query myQuery = new Query(finalCriteria);
string[] itemURIs = myQuery.ExecuteQuery();  

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.