CouchDB equivalent of Sql NOT IN?

Question

I'm looking for the CouchDB JS view equivalent of the following LinQ query :

    var query =
        from f in context.Feed
        where !(from ds in context.DataSource
                select ds.Feed_ID)
        .Contains(f.ID)
        select f;

Where DataSources have a foreign key to Feeds.

In a word : get all Feeds not associated with a DataSource

Thank you

Answer 1

You can use the view collation to join feeds and data sources in map:

function(doc) {
  if (!doc.type) return;
  if (doc.type == "feed") emit(doc._id, null);
  if (doc.type == "ds" && doc.feed) emit(doc.feed, null);
}

and reduce to filter those feed ids which have data source documents linking to them. Eg. use of the build-in _count and query with group_level:

http://127.0.0.1:5984/test/_design/join/_view/not_in?group_level=1

for database:

{"id":"1", "type":"feed"}
{"id":"2", "type":"feed"}
{"id":"3", "type":"ds", "feed":1}
{"id":"4", "type":"ds", "feed":1}}

will give you:

{"rows":[
{"key":"1","value":3},
{"key":"2","value":1}
]}

Values >1 are those feed docs which have reference from data sources. To get pure feed list w/o datasources you can omit records with value>1 in client or list function.

Edit: With list function:

function(head, req) {
  var row;
  while (row = getRow()) {
    if (row.value == 1)
      send(row.key + "\n");
  }
}

and query:

http://127.0.0.1:5984/test/_design/join/_list/not_ds/not_in?group_level=1

You will get the final result with feed documents with out reference from data sources. It is plaint text with list of ids, you can also format it for JSON array.