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!
exclCiteria
is, please?exclCiteria
is defined asList<Criteria> excCiteria = new List<Criteria>();
criteria
again is just aList<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.