How to use content query API where clause on nested content items
I have a reusable content type called Event that has a field called EventRegion, this field is a content item selector to select a reusable content type called Region. As we know, in the database the EventRegion stores a JSON string of an array of the selected content item GUID's.
So the EventRegion field would contain data like this for example: [{"Identifier":"656eef94-63y1-4d2h-b0b5-05kdb4654342"}]
I want to do the following query to retrieve events that are in the region I selected:
var eventIds = (await executor.GetMappedResult<Entities.Event>(
new ContentItemQueryBuilder().ForContentType(Entities.Event.CONTENT_TYPE_NAME, config => config
.Where(where => where.WhereContains(nameof(Entities.Event.EventRegion), region.SystemFields.ContentItemGUID.ToString())) // improve this line
.Columns("ContentItemID")
),
options: new ContentQueryExecutionOptions { ForPreview = true },
cancellationToken: cancellationToken
))
.Select(e => e.SystemFields.ContentItemID)
.ToList();
but using the WhereContains and converting the GUID to a string isnt the best route. Anyone have any ideas on how I can improve that code? Maybe a different content retriever API would be better?
Environment
Xperience by Kentico version: 30.8.2
.NET version: 8
Answers
I think you want to use the .Linking() Content item query method.
Your code would look something like this...
var eventIds = (await executor.GetMappedResult<Entities.Event>(
new ContentItemQueryBuilder().ForContentType(Entities.Event.CONTENT_TYPE_NAME, config => config
.Linking(nameof(Entities.Event.EventRegion), [region.SystemFields.ContentItemGUID])
.Columns("ContentItemID")
),
options: new ContentQueryExecutionOptions { ForPreview = true },
cancellationToken: cancellationToken
))
.Select(e => e.SystemFields.ContentItemID)
.ToList();
The Linking() method here is saying "Give me all Event content items that link to the Region content items in the collection [region.SystemFields.ContentItemGUID] from the field Event.EventRegion.
However, I would recommend using the newer ContentRetriever API, which went from preview to production-ready in June's Refresh.
Using that API you can still perform the same .Linking() query but it will handle caching, mapping, preview context, etc... for you.
An additional note: You can avoid the awkward requirement to specify the namespace every time you refer to the Event type by not using a name for a content type that is likely going to be used by someone else's code (external library, .NET, ASPNET Core).
Instead give the Event content type name a bit more domain context and name it something like "BusinessEvent", "CorporateEvent", "RegionalEvent" (since each event is in a specific region(s)).
To answer this question, you have to login first.