Tridion Query Criteria AND + OR

Question

I have a query that I want to build with combination of AND and OR criteria. The obvious example is

• PublicationCriteria is always AND
• user specified criteria can be AND or OR

I have written the code below which works fine for

1. no user criteria (only publication)
2. single user criteria

But I cannot get the AND or OR to work when submitting multiple criteria

What am I doing wrong?

    ///PUBLIC API function that parses keywords and then calls private ExecuteQuery
    public IEnumerable<string> FindBySubjectOrSyllabusKeyword(int publicationId, string keywords)
    {
        //TODO - Add logic to constrain to MAXIMUM two words, of which one can be parsed to INT
        //Create search array and add wildcards
        var searchArray = keywords.Split(',');
        searchArray = Array.ConvertAll(searchArray, x => "%" + x.Trim() + "%");

        var criteria = new List<Criteria>
        {
            new KeywordCriteria(_subjectCategory, searchArray, Criteria.Like),
            new KeywordCriteria(_syllabusCategory, searchArray, Criteria.Like)
        };

        var sortParameter = new SortParameter(new ItemTitleColumn(), SortParameter.Ascending);

        return _queryService.ExecuteQuery(publicationId, criteria, sortParameter, _resultFilterMax, false);
    }

    private global::Tridion.ContentDelivery.DynamicContent.Query.Query PrepareQueryCriteria(int publicationId, List<Criteria> criteria, bool isAndQuery)
    {
        Criteria allCriteria;
        // Publication is always present as AND criteria
        PublicationCriteria pubCriteria = new PublicationCriteria(publicationId);

        if (criteria != null)
        {
            Criteria userCriteria;
            if (isAndQuery)
            {
                userCriteria = CriteriaFactory.And(criteria.ToArray());
            }
            else
            {
                userCriteria = CriteriaFactory.Or(criteria.ToArray());
            }

            allCriteria = CriteriaFactory.And(new[] { pubCriteria, userCriteria });
        }
        else
        {
            allCriteria = pubCriteria;
        }


        var query = new global::Tridion.ContentDelivery.DynamicContent.Query.Query()
        {
            Criteria = allCriteria
        };

        return query;
    }

EDIT The above is based on the approach illustrated here.

In response to the comment by @PankajGaur

1. The Tridion categories are used to classify exam resources. So we have various categories such as: SUBJECT = Physics, Chemistry, History ; COURSECODE = 0625, 0657, 0879. Each SUBJECT can have multiple COURSECODE values, but the latter are unique across all subjects

2. The users can search for either SUBJECT and/or COURSECODE

3. The query returns data if user enters PHYSICS, or 0625

4. The query returns nothing work if the user enters PHYSICS, 0625. This should return the single record for the specific course.

5. In all cases, the PUBLICATION criteria has to be included as an AND condition to limit query to a specific Tridion publication

6. The only thing I can think is that for each KeywordCriteria, the match to my searchArray terms is doing an AND rather than an OR match

P.S. As a T-SQL developer I would expect this to be simple to implement no?

Answer 1

So, I went back to my good old SQL Server, started Query Profiler on the Tridion DB and found my answer.

When using multiple search terms, the SQL IN operator is used, and the Tridion Criteria.Like operator is ignored!!!

In particular look at this snippet from the full code:

    searchArray = Array.ConvertAll(searchArray, x => "%" + x.Trim() + "%");

    var criteria = new List<Criteria>
    {
        new KeywordCriteria(_subjectCategory, searchArray, Criteria.Like),
        new KeywordCriteria(_syllabusCategory, searchArray, Criteria.Like)
    };

As you can see I did the following to set up a LIKE search for each term of the searchArray = { "Physics", "0625" } in this particular case

1. Put "%" wilcard at start and end of each search term
2. Used the Criteria.Like operator on both my criteria

Well, here is what I got in Query Profiler:

declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,N'@P0 int,@P1 nvarchar(4000),
                 @P2 nvarchar(4000),@P3 nvarchar(4000),
                 @P4 nvarchar(4000),@P5 nvarchar(4000),@P6 nvarchar(4000)',
N'select distinct top 10000 itemmeta0_.PUBLICATION_ID as col_0_0_,  
itemmeta0_.ITEM_REFERENCE_ID as col_1_0_, itemmeta0_.ITEM_TYPE as col_2_0_,
itemmeta0_.TITLE as col_3_0_ from ITEMS itemmeta0_ 
cross join ITEM_CATEGORIES_AND_KEYWORDS relatedkey1_ 
where itemmeta0_.PUBLICATION_ID=@P0  
and 
(
relatedkey1_.ITEM_REFERENCE_ID=itemmeta0_.ITEM_REFERENCE_ID 
and relatedkey1_.PUBLICATION_ID=itemmeta0_.PUBLICATION_ID 
and relatedkey1_.CATEGORY=@P1 
and (relatedkey1_.KEYWORD in (@P2 , @P3)) 
or 
relatedkey1_.ITEM_REFERENCE_ID=itemmeta0_.ITEM_REFERENCE_ID 
and relatedkey1_.PUBLICATION_ID=itemmeta0_.PUBLICATION_ID 
and relatedkey1_.CATEGORY=@P4 
and (relatedkey1_.KEYWORD in (@P5 , @P6))
) order by itemmeta0_.TITLE ASC',
163,N'TSL Subjects',N'%Physics%',N'%0625%',
N'TSLSyllabus',N'%Physics%',N'%0625%'
select @p1

So, Tridion Content has actually broken my query by allowing me to enter wildcards, but then silently substituting the Criteria.Like with a SQL IN operator:

and (relatedkey1_.KEYWORD in (@P2 , @P3))

OUR SOLUTION: We are going to restructure our Subject Category to include Subjects + Code, and then do a LIKE on a single search term eg "%Physics 0625%".

Happy to hear if anyone has got any alternative suggestions.