Content Fields Indexing (`OrchardCore.ContentFields.Indexing.SQL`)¶

Purpose¶

This module provides database indexing for content fields.

Available Tables¶

Note that types listed are SQL Server data types.
SQLite doesn't have a length limit on text fields.

BooleanFieldIndex¶

Name	Type	Non-Null	Primary Key
`Id`	`int`	`true`	`true`
`DocumentId`	`int`	`false`	`false`
`ContentItemId`	`nvarchar(26)`	`false`	`false`
`ContentItemVersionId`	`nvarchar(26)`	`false`	`false`
`ContentType`	`nvarchar(255)`	`false`	`false`
`ContentPart`	`nvarchar(255)`	`false`	`false`
`ContentField`	`nvarchar(255)`	`false`	`false`
`Published`	`bit`	`false`	`false`
`Latest`	`bit`	`false`	`false`
Boolean	bit	false	false

ContentPickerFieldIndex¶

Name	Type	Non-Null	Primary Key
`Id`	`int`	`true`	`true`
`DocumentId`	`int`	`false`	`false`
`ContentItemId`	`nvarchar(26)`	`false`	`false`
`ContentItemVersionId`	`nvarchar(26)`	`false`	`false`
`ContentType`	`nvarchar(255)`	`false`	`false`
`ContentPart`	`nvarchar(255)`	`false`	`false`
`ContentField`	`nvarchar(255)`	`false`	`false`
`Published`	`bit`	`false`	`false`
`Latest`	`bit`	`false`	`false`
SelectedContentItemId	nvarchar(26)	false	false

DateFieldIndex¶

Name	Type	Non-Null	Primary Key
`Id`	`int`	`true`	`true`
`DocumentId`	`int`	`false`	`false`
`ContentItemId`	`nvarchar(26)`	`false`	`false`
`ContentItemVersionId`	`nvarchar(26)`	`false`	`false`
`ContentType`	`nvarchar(255)`	`false`	`false`
`ContentPart`	`nvarchar(255)`	`false`	`false`
`ContentField`	`nvarchar(255)`	`false`	`false`
`Published`	`bit`	`false`	`false`
`Latest`	`bit`	`false`	`false`
Date	datetime	false	false

DateTimeFieldIndex¶

Name	Type	Non-Null	Primary Key
`Id`	`int`	`true`	`true`
`DocumentId`	`int`	`false`	`false`
`ContentItemId`	`nvarchar(26)`	`false`	`false`
`ContentItemVersionId`	`nvarchar(26)`	`false`	`false`
`ContentType`	`nvarchar(255)`	`false`	`false`
`ContentPart`	`nvarchar(255)`	`false`	`false`
`ContentField`	`nvarchar(255)`	`false`	`false`
`Published`	`bit`	`false`	`false`
`Latest`	`bit`	`false`	`false`
DateTime	datetime	false	false

HtmlFieldIndex¶

Name	Type	Non-Null	Primary Key
`Id`	`int`	`true`	`true`
`DocumentId`	`int`	`false`	`false`
`ContentItemId`	`nvarchar(26)`	`false`	`false`
`ContentItemVersionId`	`nvarchar(26)`	`false`	`false`
`ContentType`	`nvarchar(255)`	`false`	`false`
`ContentPart`	`nvarchar(255)`	`false`	`false`
`ContentField`	`nvarchar(255)`	`false`	`false`
`Published`	`bit`	`false`	`false`
`Latest`	`bit`	`false`	`false`
Html	nvarchar(max)	false	false

LinkFieldIndex¶

Name	Type	Non-Null	Primary Key
`Id`	`int`	`true`	`true`
`DocumentId`	`int`	`false`	`false`
`ContentItemId`	`nvarchar(26)`	`false`	`false`
`ContentItemVersionId`	`nvarchar(26)`	`false`	`false`
`ContentType`	`nvarchar(255)`	`false`	`false`
`ContentPart`	`nvarchar(255)`	`false`	`false`
`ContentField`	`nvarchar(255)`	`false`	`false`
`Published`	`bit`	`false`	`false`
`Latest`	`bit`	`false`	`false`
Url	nvarchar(4000)	false	false
Text	nvarchar(4000)	false	false

NumericFieldIndex¶

Name	Type	Non-Null	Primary Key
`Id`	`int`	`true`	`true`
`DocumentId`	`int`	`false`	`false`
`ContentItemId`	`nvarchar(26)`	`false`	`false`
`ContentItemVersionId`	`nvarchar(26)`	`false`	`false`
`ContentType`	`nvarchar(255)`	`false`	`false`
`ContentPart`	`nvarchar(255)`	`false`	`false`
`ContentField`	`nvarchar(255)`	`false`	`false`
`Published`	`bit`	`false`	`false`
`Latest`	`bit`	`false`	`false`
Numeric	decimal(19,5)	false	false

TextFieldIndex¶

Name	Type	Non-Null	Primary Key
`Id`	`Int`	`true`	`true`
`DocumentId`	`int`	`false`	`false`
`ContentItemId`	`nvarchar(26)`	`false`	`false`
`ContentItemVersionId`	`nvarchar(26)`	`false`	`false`
`ContentType`	`nvarchar(255)`	`false`	`false`
`ContentPart`	`nvarchar(255)`	`false`	`false`
`ContentField`	`nvarchar(255)`	`false`	`false`
`Published`	`bit`	`false`	`false`
`Latest`	`bit`	`false`	`false`
Text	nvarchar(4000)	false	false
BigText	nvarchar(max)	false	false

TimeFieldIndex¶

Name	Type	Non-Null	Primary Key
`Id`	`Int`	`true`	`true`
`DocumentId`	`int`	`false`	`false`
`ContentItemId`	`nvarchar(26)`	`false`	`false`
`ContentItemVersionId`	`nvarchar(26)`	`false`	`false`
`ContentType`	`nvarchar(255)`	`false`	`false`
`ContentPart`	`nvarchar(255)`	`false`	`false`
`ContentField`	`nvarchar(255)`	`false`	`false`
`Published`	`bit`	`false`	`false`
`Latest`	`bit`	`false`	`false`
Time	datetime	false	false

Usage¶

Please look at each index tables to see which fields are available to query on. The following examples are for the TextFieldIndex only.

From a class.

using OrchardCore.ContentManagement;
using OrchardCore.ContentFields.Indexing

public class MyClass(){
    private readonly ISession _session;

        public MyClass(ISession session)
        {
            _session = session;
        }

        public async Task<IEnumerable<ContentItem>> GetTextFieldIndexRecords(string contentType, string contentField){
            return await _session.Query<ContentItem, TextFieldIndex>(x => x.ContentType == contentType && x.ContentField == contentField).ListAsync();
        }
}

From a Razor template.

@using OrchardCore.ContentManagement
@using OrchardCore.ContentFields.Indexing
@inject ISession Session

@{
    var contentItems = await Session.Query<ContentItem, TextFieldIndex>(x => x.ContentType == "Acme" && x.ContentField == "Test").ListAsync();
}

From Liquid you will require to create a SQL Query in Orchard Core to retrieve these records first. Name it "AllCountries" for the current example and don't select the option "Return Documents" on the Query.

SELECT * FROM TextFieldIndex
WHERE ContentType = 'Acme' AND ContentField = 'Country'

In our Liquid template we will now retrieve these records.

{% assign allCountries = Queries.AllCountries | query %}
{% for country in allCountries %}
{{ country.Text }}
{% endfor %}

Please note that Datetimes are stored as UTC so a conversion with the current request culture will be required.

Last update: October 31, 2020

Authors: Dean Marcussen

Content Fields Indexing (OrchardCore.ContentFields.Indexing.SQL)¶