Ravendb get Latest orderId group by customer

Question

I have customers in RavenDB and orders associated with these customers. Currently, when attempting to use the GroupBy operation in a document query, I encounter an error in RavenDB stating that it is unable to convert the query. As a workaround, I've been using ToList() to retrieve the data into memory and then applying GroupBy, OrderBy, and Select to get the first order for each customer. However, this approach is inefficient as it is done entirely in memory.

Now, my goal is to obtain the latest orders for each customer, disregarding any date filters applied in the lookup. I attempted to achieve this by creating an OrderLookup index, but it caused RavenDB to hang and initiate indexing for the entire day without producing any results.

public class OrderLookup : AbstractIndexCreationTask<Order, OrderLookup.Results>
{
 public class Results
 {
    public string CustomerId { get; set; }
    public DateTimeOffset? MaxCreatedOn { get; set; }
    public DateTimeOffset CreatedOn { get; set; }
    public bool IsAdvancedAgreement { get; set; }
    public bool IsForecastOrder { get; set; }
    public short SoftDeleteStatus { get; set; }
    public string Id { get; set; }
}

public OrderLookup()
{
    Map = orders => from order in orders
                    select new
                    {
                        CustomerId = order.CustomerId.Id,
                        CreatedOn = order.KeyDates.CreatedOn,
                        Id = order.Id,
                        IsForecastOrder = order.IsForecastQuote,
                        SoftDeleteStatus = order.SoftDeleteStatus,
                        IsAdvancedAgreement = order.IsAdvancedAgreement
                    };

    Reduce = results => from result in results
                        orderby result.CreatedOn
                        let maxCreatedOn = results.OrderByDescending(x => x.CreatedOn)
                                                .GroupBy(x => x.CustomerId)
                                                .Select(g => g.First().CreatedOn)
                                                .First()
                        select new Results
                        {
                            CustomerId = result.CustomerId,
                            MaxCreatedOn = maxCreatedOn,
                            CreatedOn = result.CreatedOn,
                            IsAdvancedAgreement = result.IsAdvancedAgreement,
                            IsForecastOrder = result.IsForecastOrder,
                            SoftDeleteStatus = result.SoftDeleteStatus,
                            Id = result.Id
                        };
}

}

Answer 1

The first thing to understand about Map-Reduce index is that its purpose is to keep an aggregated value up-to-date at all times, as documents are added, modified, or removed.

In your case you want the index to maintain the latest order date for each customer,
with a reference to the order document.

Here is the basic implementation you are looking for:

A sample document class:

public class MyOrder
{
    public string Id { get; set; } // document Id
    public string OrderId { get; set; }
    public string CustomerId { get; set; }
    public DateTimeOffset KeyDate { get; set; }
    public bool IsAdvancedAgreement { get; set; }
    public bool IsForecastQuote { get; set; }
    public short SoftDeleteStatus { get; set; }
}

The Map-Reduce index:

public class MyIndex : AbstractIndexCreationTask<MyOrder, MyIndex.MyIndexEntry>
{
    public class MyIndexEntry
    {
        public string CustomerId { get; set; }
        
        public DateTimeOffset LatestDate { get; set; }
        public string LatestOrderId { get; set; }
        public string LatestDocumentId { get; set; }
    }
    public MyIndex()
    {
        Map = myOrders => from myOrder in myOrders
            select new MyIndexEntry
            {
                CustomerId = myOrder.CustomerId,
                
                LatestDate = myOrder.KeyDate,
                LatestOrderId = myOrder.OrderId,
                LatestDocumentId = myOrder.Id
            };

        Reduce = results => from result in results
            group result by result.CustomerId into g
            let latest = g.OrderByDescending(x => x.LatestDate)
            
            select new MyIndexEntry
            {
                CustomerId = g.Key,
                
                // The Map-Reduce index will keep the following updated at all times per customer
                LatestDate = latest.FirstOrDefault().LatestDate,
                LatestOrderId = latest.FirstOrDefault().LatestOrderId,
                LatestDocumentId = latest.FirstOrDefault().LatestDocumentId
            };
    }
}

The query:

using (var session = store.OpenSession())
{
    // Query the index for the customer info
    MyIndex.MyIndexEntry latestInfoAboutCustomer = session
        .Query<MyIndex.MyIndexEntry, MyIndex>()
        .Where(x => x.CustomerId == "Customer1")
        .FirstOrDefault();

    // Load the customer document
    // This document will be LATEST order for that customer
    MyOrder latestOrderOfCustomer = session.Load<MyOrder>(latestInfoAboutCustomer.LatestDocumentId);
}

Note that the output structure of the Map function must be the same as the Reduce function
(both have 'CustomerId', 'LatestDate', 'LatestOrderId' & 'LatestDocumentId')