CODEWITHSUNDEEP
kentico
John K
John K

Reputation: 28917

How to nest WHERE clause conditions using the Kentico Pages API without using raw SQL strings?

I'd like to use the Kentico Pages API to recreate this SQL Where clause and to keep the parenthesis intact for altered order of operations: 

WHERE ((EventEnd IS NULL OR EventEnd = '') AND EventStart < getdate()) 
     OR EventEnd < getdate()

Note: Do not rearrange this example to work properly without parenthesis because my question is about generating complex conditions with parenthesis using the Pages API.

This example is based on a theoretical Calendar Event Page Type that has:

The purpose of this WHERE clause is to filter Past Events by looking for a Start Date in the past if an end date value does not exist; otherwise it looks for the End Date value in the past.

However I cannot find good Kentico examples about how to achieve this nesting of conditions without dropping raw SQL strings into the pages query (which an be done).

Instead, I'm looking for a clever way to use .Where(..), .And(..), .Or(..) and other relevant API methods to generate the above WHERE clause with altered order of operations intact. The WhereBuilder class looks promising too, but doesn't seem to fit inline with the flow of a pages query.

Here's an example of a Pages query into which I'm trying to recreate the above WHERE clause without dropping in raw SQL strings for the conditions: 

using CMS.DataEngine;
using CMS.DocumentEngine;
using CMS.Helpers;
using CMS.Base;

var pages = new TreeProvider().SelectNodes()
    .Published()
    .OnSite(MySiteName)
    .Types(CalendarEventPageType)
    .NestingLevel(1)
    .Path(calendarEventsPath, PathTypeEnum.Children)

 /* Looking for clever code after this line...
    How do I group these methods to get the desired order of operations with parenthesis?
   */

    .Where(..) 
    .And(..) 
    .Or(..);

Upvotes: 0

Views: 864

Answers (2)

John K
John K

Reputation: 28917

Brenden's answer pointed me to the Kentico documentation about nested WHERE conditions.

The following answer is what I'm using to exactly match the WHERE clause requirements stated in the question by using nested WHERE conditions through the API: 

DateTime rightNow = DateTime.Now;

var pagesQuery = tree.SelectNodes()
    .OnSite(Mysite)
    .Types(CalEventPageType)
    .Path("/Events/Calendar", PathTypeEnum.Children)
    .NestingLevel(1)

    /* My WHERE Conditions: */
    .Where(w1 => w1.Where(w => w.WhereEmpty("EventEnd").And().WhereLessThan("EventStart", rightNow))
        .Or().WhereLessThan("EventEnd", rightNow))

    .OrderBy(OrderDirection.Descending, "EventStart")
    .Culture("en-ca");

The outer w1 seems frivolous but was needed to have a set of outer parenthesis added around all my generated conditions so that they do not conflict with Kentico's process. You can play with the condition to see various outcomes. See full output below.

The generated SQL WHERE clause output acquired by calling pagesQuery.ToString(true) is this. It includes all my conditions (in the second line of output) and Kentico's conditions (all the other lines): 

WHERE [NodeSiteID] = 1 AND

/* My WHERE Conditions: */
((([EventEnd] = N'' OR [EventEnd] IS NULL) AND [EventStart] < '10/16/2018 10:02:13 PM') OR [EventEnd] < '10/16/2018 10:02:13 PM') 

AND (([DocumentCanBePublished] = 1 
AND ([DocumentPublishFrom] IS NULL OR [DocumentPublishFrom] <= '10/16/2018 10:02:13 PM') 
AND ([DocumentPublishTo] IS NULL OR [DocumentPublishTo] >= '10/16/2018 10:02:13 PM')) 
AND [NodeAliasPath] LIKE N'/Events/Calendar/%' 
AND [NodeLevel] <= 3 AND [DocumentCulture] = N'en-ca')

Upvotes: 1

Brenden Kehren
Brenden Kehren

Reputation: 6117

You should be able to add a nested WhereCondition like so:

.Where(new WhereCondition()
        .WhereNull("EventEnd")
        .Or()
        .WhereEmpty("EventEnd")
        .And()
        .WhereLessThan("EventStart", DateTime.Now)
    )
.Or()
.WhereLessThan("EventEnd", DateTime.Now);

Reference Kentico docs https://docs.kentico.com/k11/custom-development/retrieving-database-data-using-objectquery-api#RetrievingdatabasedatausingObjectQueryAPI-Nestedwhereconditions

Upvotes: 3

Related Questions