CODEWITHSUNDEEP
c#indexingnosqlravendbravendb4
Alex Klaus
Alex Klaus

Reputation: 8934

RavenDb index to filter&sort on properties of the nested structure/collection (fanout index)

I'm looking for a way to create a static index to serve filtering/sorting queries for a combination of property values in the nested structure (a collection of objects) along with the structure-container. It seems to be not trivial due to the following reasons:

Given the following persistent model:

public class Document
{
    public string Title { get; set; }

    public List<UserChange> RecentModifications { get; set; }
}

where

public class UserChange
{
    public string UserId { get; set; }
    public DateTime Timestamp { get; set; }
}

Question: How to build the index for Document to filter/sort by a combination of all the fields: Title, UserId and Timestamp?

Possible use cases:

P.S. I understand that the indexing restrictions can by bypassed by restructuring the persistence model - storing a structure for recently modified documents in the User document, but it'd impose some other limitations, which I'd like to avoid.

Upvotes: 2

Views: 797

Answers (2)

Alex Klaus
Alex Klaus

Reputation: 8934

The problem can be solved by using Indexes with Dynamic Fields. It allows to keep logical data structure and avoids creating a fanout index.

Solution

Create the following index for the Document collection from the above:

public class MyIndex : AbstractIndexCreationTask<Document, DocumentIndDto>
{
    public MyIndex()
    {
        // Add fields that are used for filtering and sorting
        Map = docs =>
            from e in docs
            select new
            {
                Title = e.Title, 
                _ = e.RecentModifications.Select( x => CreateField ($"{nameof(Document.RecentModifications)}_{x.UserId}", x.Timestamp))
            };
    }
}

public class DocumentIndDto
{
    public string Title { get; set; }
    public Dictionary<string,DateTime> RecentModifications { get; set; }
}

Query on MyIndex like

var q = s.Query<DocumentIndDto, MyIndex>()
                .Where(p => p.Title == "Super" && p. RecentModifications["User1"] < DateTime.Now);

Explanation

The specified index with dynamic fields will generate extra fields and terms for each record in the following format:

RecentModifications_User1 = '2018-07-01';
RecentModifications_User2 = '2018-07-02';

The format is important, because when you use a dictionary in your high-level query like myDic[key], it gets transformed to myDic_key in the generated RQL. Hence, it will allow us to use these fields in queries.

If you query with using the usual Query rather than DocumentQuery (see docs), then you need a proper data type for LINQ to work. For that purpose I created DocumentIndDto class, where my RecentModifications has become a dictionary, so I could use it in a high-level query and get correct RQL like

from index 'MyIndex' where Title = $p0 and RecentModifications_User1 = $p1

For more details, see my discussion on the subject with Oren Eini (aka Ayende Rahien).

Upvotes: 2

Danielle
Danielle

Reputation: 3839

Use the following RQL in your index definition:

from doc in docs.Documents
from modification in doc.RecentModifications 
select new {
    modification.UserId,
    modification.Timestamp
}

Note: 'UserId' & 'timestamp' are NOT separated in the underlying index-entry.

So filtering on a combination UserId='A' AND Timestamp='2018-01-01' WILL return records modified by user 'A' on '2018-01-01'.

See also Fanout Indexes

Note2: The 'Title' can also be indexed and searched on using:

from doc in docs.Documents
from modification in doc.RecentModifications 
select new {
    doc.Title,
    modification.UserId,
    modification.Timestamp
}

So each resulting 'index entry' will contain the 'UserId' & 'Timestamp' as before -and- the relevant 'Title'

Upvotes: 0

Related Questions