CODEWITHSUNDEEP
nosqlcouchdb
Sylvain
Sylvain

Reputation: 19249

How to define a couchdb view that I can filter on attributes of different types of document?

I have two types of document in a couch database. There are Events and Occurences. One Event has many Occurences.

Event:

{
  "_id": "49bb92b8896515a2994e524b38a041d3",
  "type": "Event",
  "eventID": 1234,
  "area": "m1"
}

Occurence:

{
  "_id": "49bb92b8896515a2994e524b38a041d4",
  "type": "Occurence",
  "occurenceID": 7890,
  "eventID": 1234,
  "date": "2013-01-01"
}

I need to find the count of occurences per date filtered by an area name and by a range of dates. In SQL, I'd use this query:

SELECT Date, count(*)
FROM Event INNER JOIN Occurence ON Occurence.EventID = Event.EventID
WHERE Event.Area = "m1"
AND Occurence.Date BETWEEN '2013-01-01' AND '2013-02-01'
GROUP BY Date

I don't mind executing more than one query but my database has over 300,000 occurence documents (and will grow 10 times that), so I need to get the results by tansfering as few documents as possible. The app that queries this couchdb is written with node.js.

Upvotes: 1

Views: 151

Answers (1)

djc
djc

Reputation: 11711

Yeah, this would require two queries to get right, I think. You should consider denormalizing by copying the event area into the occurrence documents, that would make it a lot easier.

Upvotes: 1

Related Questions