Matching items with multiple foreign keys in RavenDB

Question

I asked this question previously regarding SQL Server: Complicated SQL Query--finding items matching multiple different foreign keys

Basically, I need to be able to find products that match multiple criteria. I have a scenario where I need to find products that match each of multiple categories and are found in multiple invoices.

The solution was a rather complex set of unions, which amounts to counting the number times a product matched the criteria and filtering for items whose count matched the count of criteria.

 ; with data (ID, Count) as (
      select pc.ProductID, count(*) from ProductCategories pc (nolock)
           inner join @categoryIDs /*table valued param*/ c on c.ID = pc.CategoryID
      union all
      select ip.ProductID, count(*) from InvoiceProducts ip (nolock)
           inner join @invoiceIDs i on i.ID = ip.InvoiceID 
 )
      select d.ID from data d
           group by d.ID
           having sum(d.Count) = @matchcount

But now, I am considering a NoSQL provider. So my question is, how would I create an index function to match this kind of query in RavenDB (or some other NoSQL project)?

score 1 · Answer 1 · answered Jul 17 '11 at 20:25

A mental shift is required to properly set this up with RavenDB (or any other document DB). The problem is with the hacks we all used to make when working with structured data against an SQL server.

Therefore, the question here is how your data is modeled. To be more exact - how are you going to use it most often; based on that there are certain guidelines on which entities to define and how to link them together.

For a simple Product object, with String[] of categories, you can query the DB like this:

   // Query on nested collections - will return any product with category "C#"
    products = from p in session.Query<Product>()
               where p.Categories.Any(cat => cat == "C#")
               select c;

You can add as many Where clauses as you want. An index will be automatically created for you - but it is recommended to use static indexes when you've settled on a Model.

Matching items with multiple foreign keys in RavenDB

1 Answers1