在 RavenDB 中匹配具有多个外键的项目

发布于 2024-11-24 06:38:40 字数 859 浏览 0 评论 0原文

我之前问过这个关于 SQL Server 的问题：复杂的 SQL 查询--查找与多个不同外键匹配的项目

基本上，我需要能够找到匹配多个条件的产品。我有一个场景，我需要查找与多个类别中的每个类别匹配并在多个发票中找到的产品。

解决方案是一组相当复杂的联合，这相当于计算产品符合条件的次数，并过滤出计数与条件计数匹配的项目。

 ; 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

但现在，我正在考虑使用 NoSQL 提供商。所以我的问题是，如何创建一个索引函数来匹配 RavenDB（或其他一些 NoSQL 项目）中的此类查询？

原文

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)?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

人生戏 2024-12-01 06:38:40

需要进行思维转变才能使用 RavenDB（或任何其他文档数据库）正确设置它。问题出在我们在 SQL 服务器上处理结构化数据时所使用的 hack。

因此，这里的问题是如何对数据进行建模。更准确地说 - 您最常如何使用它；在此基础上，存在关于定义哪些实体以及如何将它们链接在一起的某些准则。

对于一个简单的 Product 对象，使用 String[] 类别，您可以像这样查询数据库：

   // 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;

您可以添加任意多个Where 子句。系统会自动为您创建索引 - 但建议您在确定模型后使用静态索引。

有关此主题的更多信息：

http://ayende.com/blog/第4801

章rel="nofollow">https://github.com/ravendb/docs

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.