使用CouchDB视图替换SQL中的多个联接 [英] Replace multiple joins in SQL with CouchDB views

问题描述

我正在为我的应用程序实现一个过滤器功能，并且在CouchDB上编写视图时遇到麻烦。而在SQL中，这将是一个多重连接的语句。如何替换CouchDB中的多个连接。本文涵盖单一连接： http://www.cmlenz.net/archives/2007 / 10 / couchdb-join ..然而，对我来说，如何将这种方法扩展到多个连接并不明显。

想象一下，我的对象有十几个属性，每个属性都可以有其单个过滤器。为了简单起见，我们假设它只有两个属性。如果我们可以解决这个问题，我们可以将其扩展到任何数量的属性。

我的应用程序需要支持一堆AND过滤器。例如。查找所有会话（西雅图，纽约）和开始时间> = 1336608413。我还需要持续过滤器设置。

在SQL中我有两个表：会话和过滤器。然后我在Session和Filter表之间加入一个过滤条件，以获取所有过滤的会话。

 会话表
位置start-epoch 
西雅图1336608413 
 
过滤器表
名称值
位置Seattle 
位置纽约
 start-epoch 1336608413 
 
 
查询：
从会话s 
中选择* 
其中存在（从Filter f中选择1，其中f.name ='location'和f.value = s。位置）
并存在（从f.name ='start-epoch'和s.start-epoch> = f2.value中的Filter f中选择1）
  pre> 
 
 
在CouchDB中：
  {type：session ，location：Seattle，start-epoch：1336608413} 
  
然后如何模型过滤器并写入视图？
 
 
 
过滤器可能是这样的：
  {type：filter，location：[Seattle，New York]，start-epoch：1336608413} 
  
然后如何编写视图？
 
 
 
我想在CouchDB中实现这一点。我希望创建一个名为FilteredSessions的视图，并在一个HTTP调用Couch中检索过滤的会话。原因是因为我有5个不同的客户端（iOS，Android，Silverlight，Ruby on Rails和Windows 8），我不想重写逻辑5次。如果我使用sqlite，我可以创建一个名为FilteredView的视图来实现。我希望写在地图上的等价物。
解决方案
这是我的想法。在地图函数中，发出会话和过滤文档。然后使用列表函数枚举地图输出，以读取持久化的过滤器和会话进行过滤。
 
I am implementing a filter feature for my application and having trouble with writing the view on CouchDB.  While in SQL, this would be a statement with multiple join.  How to replace multiple join in CouchDB.  This article covers single join: http://www.cmlenz.net/archives/2007/10/couchdb-joins.  However, it's not obvious to me how to extend this approach to multiple join.

Imagine my object has a dozen properties and each property can have its individual filter.  For simplicity, let's assume it has only two properties.  If we can solve that, we can probably extend it to any number of properties.

My application needs to support a bunch of AND filters.  E.g. find all sessions where location in ("Seattle", "New York") AND start-epoch >= 1336608413.  And I also need to persist filter settings.

In SQL, I have two tables: Session and Filter.  Then I have one join per filter condition between Session and Filter table to get all filtered sessions.
Session Table
location    start-epoch    
Seattle     1336608413     

Filter Table
Name        Value
location    Seattle
location    New York
start-epoch 1336608413     


Query:
select * 
from Session s 
where exists (select 1 from Filter f where f.name = 'location' and f.value = s.location)
     and exists (select 1 from Filter f on f.name = 'start-epoch' and s.start-epoch >= f2.value)
In CouchDB:
{"type":"session", "location":"Seattle", "start-epoch":1336608413}
Then how to model filter and write the view?

Filter could be something like this:
{"type":"filter", "location":["Seattle", "New York"], "start-epoch":1336608413}
Then how to write the view?

I want to achieve this inside CouchDB.  I am hoping to create a view called FilteredSessions and retrieve the filtered session in one http call to Couch.  The reason is because I have 5 different clients (iOS, Android, Silverlight, Ruby on Rails and Windows 8) and I don't want to rewrite the logic 5 times.  If I use sqlite, I can create a view called "FilteredView" to achieve that.  I hope to write the equivalent in map reduce.
解决方案
Here is the idea I have.  In map function, emit both session and filter documents.  Then use the list function to enumerate the map output to read the persisted filters and sessions to do the filtering.

                        
这篇关于使用CouchDB视图替换SQL中的多个联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！