CODEWITHSUNDEEP
ravendb
dna
dna

Reputation: 1498

Get aggregate data from RavenDB after query and/or load of documents

I've the following document model:

public class Product
{
    public int Id {get;set;}
    public string Name {get;set;}
    // ...
}

public class Customer
{
    public int Id {get;set;}
    public string Name {get;set;}
    // ...
}

public class ProductOrder
{
    public int Id {get;set;}
    public int ProductId {get;set;}
    public int CustomerId {get;set;}
    public int Quantity {get;set;}
    // ...
}

When quering for a Product i also want to have the number of ordered products (Quantity) and unique customers. Same for the Customer, i want to get the number of ordered products.

How to achieve this with RavenDB? With an index i've only the possibility to Load<> another entity, not all entities that reference the entity i query for.

Since the number of orders for a product can be a very high number i havn't added them to the Productdocument and so i don't have access to the orders during indexing with Include<> or LoadDocument<>.

Is my Model wrong for a DocumentDB or is there another way to get those information? If so, how to do this better?

Thanks.

Update

Here is my index for the number of orders:

public class ProductOrder_CountByProduct : AbstractIndexCreationTask<ProductOrder, ProductOrder_CountByProduct.Result>
{
    public class Result
    {
        public int ProductId { get; set; }
        public int NumberOfOrders { get; set; }
    }

    public ProductOrder_CountByProduct()
    {
        Map = orders => from order in orders
                          select new
                                     {
                                         ProductId = order.ProductId,
                                         NumberOfOrders = order.Quantity
                                     };

        Reduce = results => from result in results
                            group result by result.ProductId
                            into g
                            select new
                                       {
                                           ProductId = g.Key,
                                           NumberOfOrders = g.Sum(p => p.NumberOfOrders)
                                       };
    }
}

Now i can get the number of orders by product id:

var result = documentSession.Query<ProductOrder_CountByProduct.Result, ProductOrder_CountByProduct>()
                            .FirstOrDefault(p => p.ProductId == product.Id)
                            ?? new ProductOrder_CountByProduct.Result();

var count = result.NumberOfOrders;

Same for the CountByCustomer query/index. So far so good for a single result of Product.

But my goal was to query for products (by name or description) and fill an array of:

public class ProductViewModel
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }

    public int NumberOfOrders { get; set; }
    public int NumberOfCustomers { get; set; }

    //... maybe NumberOfCustomersFromUSA, etc.
}

With the CountByProduct and CountByCustomer indexes i need then to iterate over every result of a product query and execute 2 CountByXYqueries for each result.

Is this the way to go?

Upvotes: 1

Views: 566

Answers (1)

Matt Johnson-Pint
Matt Johnson-Pint

Reputation: 241555

You really don't want to be making a call for each item. That's a "select N+1" issue. Raven will actually prevent you from doing this, by limiting the number of requests you can make in a single session to 30.

There are a few different techniques that you can use instead.

Option 1

If you want to be able to query on name and description fields:

  • Include the name and description in your index map.
  • Include them in the reduce key, and pass them through unmodified.
  • Then you can include them in the Where predicate of a query.

Option 2

If you just want name and description fields in your output, but you don't want to query by them:

  • Add a TransformResults step to your index.
  • In the transform, load the product by its id that you grouped by.
  • Return the name and description fields from the loaded product, and the original aggregated counts.

Option 3

Same scenario as option 2, but you can do it without a transform

  • When querying, specify an Include in the query customization.
  • When you iterate through the results, you can load the product details without actually hitting the server, since they were included. Read more on includes here.

Option 4

You might want to consider using the Indexed Properties Bundle to write the results of these queries back to properties on the product documents. That way, you can just query for products and your data is all in one place - even if they were aggregated in multiple indexes.

--

You may want to combine multiple of the above techniques, for example - you may want to have the product name in the query so you can query by it, but you might want the product description for display only so you transform the query results to return it.

One thing you said didn't sound quite right though. Why would you return a count of your customers in your product view model? Unless its "number of customers that have ordered this product" - then it probably doesn't belong there.

Upvotes: 1

Related Questions