I think my scenario is a common scenario in any application. I will exemplify using an common used domain: Products and Orders with Items (of products).
What I am trying to do is search orders by the product name, considering that the order has a list of itens and each item has a related productId.
I did some searches and read the Raven documentation, but I was unable to find the answer to my problem.
Please consider the code below:
public class Product
{
public string Id { get; set; }
public string ProductName { get; set; }
public decimal Price { get; set; }
}
public class Order
{
public string Id { get; set; }
public string OrderNumber { get; set; }
public decimal Total { get; set; }
public string Customer { get; set; }
public Item[] Items { get; set; }
}
public class Item
{
public string ProductId { get; set; }
public decimal Price { get; set; }
public int Quantity { get; set; }
}
public class Order_ByProductName : AbstractMultiMapIndexCreationTask<Order_ByProductName.Result>
{
public class Result
{
public string ProductId { get; set; }
public string ProductName { get; set; }
public string[] OrdersIds { get; set; }
public string[] OrdersNumbers { get; set; }
}
public Order_ByProductName()
{
AddMap<Product>(products => from product in products
select new
{
ProductId = product.Id,
ProductName = product.ProductName,
OrderId = default(string),
OrderNumber = default(string)
});
AddMap<Order>(orders => from order in orders
group order by order.Items.Select(c => c.ProductId)
into g
select new
{
ProductId = g.Key,
ProductName = default(string),
OrdersIds = g.Select(c => c.Id),
OrdersNumbers = g.Select(c => c.OrderNumber)
});
Reduce = results => from result in results
group result by result.ProductId
into g
select new
{
ProductId = g.Key,
ProductName = g.Select(r => r.ProductName).Where(t => t != null).First(),
OrdersIds = g.Where(r => r.OrdersIds != null).SelectMany(r => r.OrdersIds),
OrdersNumbers = g.Where(r => r.OrdersNumbers != null).SelectMany(r => r.OrdersNumbers)
};
Sort("ProductName", SortOptions.String);
Index(x => x.ProductName, FieldIndexing.Analyzed);
}
}
class Program
{
static void Main(string[] args)
{
var documentStore = new DocumentStore
{
Url = "http://localhost:8080",
DefaultDatabase = "MyDatabase"
};
documentStore.Initialize();
new Order_ByProductName().Execute(documentStore);
using (var session = documentStore.OpenSession())
{
var product1 = new Product() { Price = 100, ProductName = "Phone" };
var product2 = new Product() { Price = 1000, ProductName = "Laptop" };
var product3 = new Product() { Price = 200, ProductName = "Windows Phone" };
session.Store(product1);
session.Store(product2);
session.Store(product3);
session.SaveChanges();
}
using (var session = documentStore.OpenSession())
{
var products = session.Query<Product>().ToList();
var order1 = new Order();
order1.Customer = "Jhon Doe";
order1.OrderNumber = "001";
order1.Items = new Item[] {
new Item { ProductId = products[0].Id, Price = products[0].Price, Quantity = 1 },
new Item { ProductId = products[1].Id, Price = products[1].Price, Quantity = 1 }
};
order1.Total = order1.Items.Sum(c => (c.Quantity * c.Price));
var order2 = new Order();
order2.Customer = "Joan Doe";
order2.OrderNumber = "002";
order2.Items = new Item[] {
new Item { ProductId = products[2].Id, Price = products[2].Price, Quantity = 1 }
};
order2.Total = order2.Items.Sum(c => (c.Quantity * c.Price));
session.Store(order1);
session.Store(order2);
session.SaveChanges();
}
using (var session = documentStore.OpenSession())
{
var results = session
.Query<Order_ByProductName.Result, Order_ByProductName>()
.Where(x => x.ProductName == "Phone")
.ToList();
foreach (var item in results)
{
Console.WriteLine($"{item.ProductName}\t{string.Join(", ", item.OrdersNumbers)}");
}
Console.ReadKey();
}
}
}
I found a related issue here How to index related documents in reverse direction in Ravendb, but this scenario is a bit different. The code above runs if you comment the index creation, but thorws an exception otherwise: "Could not understand query: Variable initializer must be a select query expression".
I need somthing like below SQL but in RavenDb:
select pro.ProductName, ord.OrderNumber from orders ord
inner join items itm on itm.OrderId = ord.Id
inner join products pro on pro.Id = itm.ProductId
where pro.ProductName like '%Phone%';
How to create a search by ProductName and return a list or Orders in RavenDb? Im using version 3.5(.1)
Consider the main problem as search "entities by related entity property".
Thanks in advance!
Consider storing the product name directly in the order items. That will have two advantages - the order items won't be affected by later product changes, and indexing will be cheaper.
The following code demonstrates both approaches: