How add nested where conditions in ContentTypeQueryParameters

2024/09/19 1:59 PM

Hello,

I would like to add a nested condition to a ContentTypeQueryParameters object: where a and (b or c)

q.Where(w => w.WhereEquals(‘field0’, ‘value’)); // add the condition to q

This doesn't work, it doesn't add any ‘action’ to the q object (q is ContentTypeQueryParameters) :

q.Where(w => new WhereCondition().WhereLike(‘field1’, ‘value’).Or().WhereLike(‘field2’, ‘value’)); // no effect on q

Please, can you tell me how to write this type of request with xperience by kentico ? Thanks for help!

Environment

Answers

2024/09/20 1:56 PM

Before digging into the specifics of the type of query you are trying to execute, I recommend exploring the content query API with a demo project, using the kentico-xperience-sample-mvc Dancing Goat sample app and combining that with our MiniProfiler integration.

The benefit of this setup for query exploration is

  1. You already have a standard set of content types and data to try querying against
  2. MiniProfiler will show the SQL executed to render a page

You can set up a custom _Layout_Custom.cshtml with no styling or content that needs querying, create a Page Template or Razor view that references this layout, and then execute your test query in the Controller action that renders this page.

You should see very few queries in the MiniProfiler UI except for the query you are testing, making it easy to explore the API and iterate.

If you want to see more query examples "in action", check out the Kentico Community Portal project on GitHub.

2024/09/25 9:11 PM
Answer

TLDR;

I think you need to change your .Where() syntax to q.Where(w => w.WhereLike(‘field1’, ‘value’).Or().WhereLike(‘field2’, ‘value’));

I just tried an example query using Xperience v29.5.0 and the Dancing Goat sample project.

var builder = new ContentItemQueryBuilder()
    .ForContentTypes(q => q
        .OfContentType(ArticlePage.CONTENT_TYPE_NAME)
        .WithContentTypeFields())
    .Parameters(q => q
        .Where(w => w.WhereGreater(nameof(ArticlePage.ArticlePagePublishDate), "2023/08/12")
            .And()
            .Where(w => w
                .WhereStartsWith(nameof(ArticlePage.ArticleTitle), "o")
                .Or()
                .WhereStartsWith(nameof(ArticlePage.ArticleTitle), "coffee"))))
    .InLanguage("en");
	
var options = new ContentQueryExecutionOptions 
{ 
	ForPreview = false, 
	IncludeSecuredItems = true 
};

var pages = await executor.GetMappedWebPageResult<ArticlePage>(
	builder, 
	options);

foreach (var p in pages)
{
    Console.WriteLine(p.ArticleTitle);
}

This prints the following titles:

Coffee Beverages Explained
Coffee processing techniques
Origins of Arabica Bourbon

Using the following SQL (with some SELECT columns removed for brevity)

SELECT [ArticleTitle], [CMS_T]
FROM (
    SELECT [ArticleTitle], [ArticlePagePublishDate], ('DancingGoat.ArticlePage') AS [CMS_T]
    FROM CMS_ContentItem 
    INNER JOIN CMS_ContentItemCommonData 
        ON [CMS_ContentItem].[ContentItemID] = [CMS_ContentItemCommonData].[ContentItemCommonDataContentItemID] 
    INNER JOIN DancingGoat_ArticlePage 
        ON [CMS_ContentItemCommonData].[ContentItemCommonDataID] = [DancingGoat_ArticlePage].[ContentItemDataCommonDataID]
    WHERE [ContentItemCommonDataContentLanguageID] = 1 AND [ContentItemCommonDataVersionStatus] = 2
) AS SubData
WHERE [ArticlePagePublishDate] > '2023/08/12' AND ([ArticleTitle] LIKE 'o%' OR [ArticleTitle] LIKE 'coffee%')

This all looks correct to me!

To answer this question, you have to login first.