SQL Indexing¶

Content Item Indexing¶

Here are some SQL tables that you can query and their columns.

ContentItemIndex¶

Name	Type	Non-Null	Primary Key
`Id`	`int`	`true`	`true`
`DocumentId`	`int`	`false`	`false`
`ContentItemId`	`nvarchar(26)`	`false`	`false`
`Published`	`bit`	`false`	`false`
`Latest`	`bit`	`false`	`false`
`ModifiedUtc`	`datetime`	`false`	`false`
`PublishedUtc`	`datetime`	`false`	`false`
`CreatedUtc`	`datetime`	`false`	`false`
`Owner`	`nvarchar(255)`	`false`	`false`
`Author`	`nvarchar(255)`	`false`	`false`
`DisplayText`	`nvarchar(255)`	`false`	`false`

LocalizedContentItemIndex¶

Name	Type	Non-Null	Primary Key
`Id`	`int`	`true`	`true`
`DocumentId`	`int`	`false`	`false`
`ContentItemId`	`nvarchar(26)`	`false`	`false`
`Published`	`bit`	`false`	`false`
`Latest`	`bit`	`false`	`false`
LocalizationSet	nvarchar	false	false
Culture	nvarchar	false	false

Content Fields Indexing¶

The OrchardCore.ContentFields.Indexing.SQL module provides database indexing for content fields.

Note that the listed types are SQL Server data types.

SQLite doesn't have a length limit on text fields.

Available Content Fields Tables¶

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(766)	false	false
BigUrl	nvarchar(max)	false	false
Text	nvarchar(766)	false	false
BigText	nvarchar(max)	false	false

MultiTextFieldIndex¶

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`
Value	nvarchar(766)	false	false
BigValue	nvarchar(max)	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(766)	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

UserPickerFieldIndex¶

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`
SelectedUserId	string	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 be required 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.

GraphQL Usage¶

Enabling the OrchardCore.ContentFields.Indexing.SQL module allows building filters based on dynamic content fields in GraphQL queries.

The following examples demonstrate filtering products based on a numeric content field named Amount attached to a content part named PricePart where the Amount is greater than 10:

Normal usage:

product(where: {price: {amount_gt: 10}}) {
    contentItemId
    displayText
    price {
        amount
    }
}

If the PricePart is collapsed:

product(where: {amount_gt: 10}) {
    contentItemId
    displayText
    amount
}

If the PricePart is collaped with prevent field name collision option enabled:

product(where: {priceAmount_gt: 10}) {
    contentItemId
    displayText
    priceAmount
}