Dynamic Content Query generates incorrect SQL

Question

I have used the following code to create a content delivery query on 2011 SP1 - HR1.

string[] results = null;
using (Criteria isOrganisatie = new ItemSchemaCriteria(this.OrganisatieSchemaID))
using (Criteria matchesKeyword = new TaxonomyKeywordCriteria(
                                         this.CategoryURI, organisatieFilterURI, true))
using (Criteria isOrganisatieAndMatchesKeyword 
                                   = CriteriaFactory.And(isOrganisatie, matchesKeyword))
using (Query organisatiesQuery = new Query(isOrganisatieAndMatchesKeyword))
{                                
    results = organisatiesQuery.ExecuteQuery();                
}

When executed, this gives the following error.

org.hibernate.hql.ast.QuerySyntaxException: unexpected token: ) 
 near line 1, column 392 [select distinct im.publicationId, im.itemId,  
 im.itemType,im.itemId from com.tridion.storage.ItemMeta im, 
 com.tridion.storage.TaxonomyItem as JOIN3TF, com.tridion.storage.TaxonomyItem as 
 JOIN3TF1, com.tridion.storage.RelatedKeyword as JOIN3    
 ,com.tridion.storage.ComponentMeta as JOIN2  where (im.itemId=JOIN2.itemId and 
 im.publicationId=JOIN2.publicationId and JOIN2.schemaId=:schemaId4 AND )  ORDER BY 
 im.itemId ASC]

The interesting part of this is AND ) ORDER BY - that AND has obviously got no business being there.

Is this caused by the way I've written my query? (I've tried various other approaches, but with similar results). Is this a known issue?

Answer 1

As mentioned in the other answers here, the code you used should be working. My preference would be to use the AndCriteria over CriteriaFactory.And but like you, I also don't know why these two are both there and considering they are, they should work.

Looking at the error message and the Broker SQL statement in there, it sounds to me like this.CategoryURI and organisatieFilterURI might not contain a valid URI. As in the SQL statement you are missing values for these.

So make sure that this.CategoryURI is a valid TCM URI for a Category (i.e. something like tcm:1-2-512) and organisatieFilterURI is a valid Keyword TCM URI, i.e. tcm:1-3-1024.

Answer 2

I am using it like the below and working fine. Take a look at the code, the diff I see is I am using criteria's slightly differently.

Query qry = new Query();
List<Criteria> criterias = new List<Criteria>();
criterias.Add(new ItemSchemaCriteria(233));  // schema ID
criterias.Add(new TaxonomyKeywordCriteria(40, 69 , 1844,true)); //PubId, CatId, Kwdid

Criteria criteria = new AndCriteria(criterias.ToArray());
qry.Criteria = criteria;

string[] results = qry.ExecuteQuery();

return "Result" + results.Length;

Update : I used the same code you posted (except replaced with IDs) and It worked perfectly for me too.. Tridion Version : 2011 SP1 HR1 and SQL Server 2008 R2.

This suggests me something wrong with your CD jars - version.. may be ? Not sure?

string[] results = null;
using (Criteria isOrganisatie = new ItemSchemaCriteria(233))
using (Criteria matchesKeyword = new TaxonomyKeywordCriteria("tcm:40-69-512", "tcm:40-1844-1024", true))
using (Criteria isOrganisatieAndMatchesKeyword
                                    = CriteriaFactory.And(isOrganisatie, matchesKeyword))
using (Query organisatiesQuery = new Query(isOrganisatieAndMatchesKeyword))
{
    results = organisatiesQuery.ExecuteQuery();
}

return "Result : " + results.Length;

Answer 3

Have you tried changing Criteria to AndCriteria on line 4? i.e.

Criteria isOrganisatieAndMatchesKeyword = new AndCriteria(isOrganisatie, matchesKeyword)