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

Cranny

You mean 'get all Feeds not associated with a specific Datasource' or 'get all feeds not associated with ANY DataSource'? – Wretched

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.

Barrio

I'm facing the same issue. #61845495 – Discoverer

As well as >>> List functions are deprecated in CouchDB 3.0, and will be removed in CouchDB 4.0. – Discoverer

Answer 2

The straight answer to CouchDB equivalent of Sql NOT IN is: There is no equivalent. There is no querying option in the couchdb view API for NOT IN.

However, there may be alternative ways to achieve your objectives depending if you are interested in getting feeds associated to no datasource or not associated to a specific datasource.

Wretched

Answer 3

I know this is an old post, but having been confronted with the same problem I think that Marcin's answer is correct but incomplete.

In my case I used the following list function to select get all rows except the ones with a certain excludeId:

function(head, req) {
start({ 'headers': { 'Content-Type': 'application/json' } });
 var rows = [];
 while (row = getRow()) {
    if (!req.query.exceptId || doc.documentId != req.query.exceptId) {
        rows.push(row);
    }
 }
 send(JSON.stringify({ total_rows: rows.length, rows: rows }));
}

you can read more here

Sabu