Delete soft linked documents using couchbase onDelete events

Couchbase Server
,
1

I am using couchbase enterprise 7.1, I have JSON documents stored in couchbase collection and each document is softlinked to each other with parent child relationship being established using customId.
here docId is meta().id
example s

--- doc-1
{
   "docId": "81b064e7-a32b-4712-b669-79bec0db81b6",
   "docCategory" : "A"
   "customId" : 1
   "otherInfo" : {},
   "relationship": {
     "children" : [{"customId" : 2, "docCategory" : "B"}]
   } 
}


--- doc-2
{
   "docId": "81b064e7-a32b-4712-b669-79bec0db81a7",
   "docCategory" : "B"
   "customId" : 2
   "otherInfo" : {},
   "relationship": {
     "parents" : [{"customId" : 1, "docCategory" : "A"}],
     "children" : [{"customId" : 3, "docCategory" : "C"}],
   } 
}


--- doc-3
{
   "docId": "81b064e7-a32b-4712-b669-79bec0db81c7",
   "docCategory" : "C"
   "customId" : 3
   "otherInfo" : {},
   "relationship": {
     "parents" : [{"customId" : 2, "docCategory" : "B"}]
   } 
}

I am deleting doc-1, now onDelete of doc-1 i want to delete doc-2 and doc-3 which are softlinked based on relationship object.

2

You probably want to look at Eventing functions to achieve what is [Edit for clarity: effectively] a cascading foreign key delete:

https://docs.couchbase.com/server/current/eventing/eventing-examples-cascade-delete.html

HTH.

3

@dh, cascading foreign key requires relationship at DB Object level, I have manually defined relationship

4

@gotroyilmi, since CB doesn’t support explicit foreign keys (schema-less), you have to use a solution such as the Eventing example I provided. Conceptually it is still a “cascading delete based on a foreign key” - just you’re defining “foreign key” (in the Eventing function code) rather than the DB (schema) defining it, and making use of the Eventing service to automate the deletion.

5

@dh, i have put cascading select and delete logic, but
I am not able to execute below select query in eventing, it is somehow not executing

function OnUpdate(doc, meta) {
    try {
        const idSet = new Set();
        idSet.add("1");
        idSet.add("2");
        var joinedStr = Array.from(idSet).join(",");
        log("idStr : " + joinedStr);
        var queryParamIdsStr = joinedStr.split(',').join('\',\'');
        log("queryParamIdsStr : " + queryParamIdsStr);

        var results = N1QL("SELECT c.customId, meta().id " +
            "FROM `bucket-name`.`scope-name`.`collection-name` AS tbl " +
            "WHERE tbl.docCategory = \"C\" " +
            "AND (ANY v IN ( " +
            "  SELECT DISTINCT customId AS customId" +
            "  FROM `bucket-name`.`scope-name`.`collection-name` AS tbl_a" +
            "  WHERE tbl_a.docCategory = \"C\" " +
            "  AND ANY x IN relationship.parents SATISFIES x.customId IN ['$1'] END" +
            ") SATISFIES v.customId = tbl.customId END);",
            [queryParamIdsStr],
            {'isPrepared': true}
        );

        for (var item of results) {
            log("item : " + item.customId + " meta-id : " + item.id);
            // delete meta.id // LATER
        }
        results.close();
    } catch (error) {
        log("Error processing document " + meta.id + " and Title : " + title + " : " + error);
    }
}

I think, some issue with queryParamIdsStr, being passed to N1QL object, I am not able to log the query being created and executed in eventing.

6

Because you’re passing $1 as an argument, it isn’t expanded in-line to be interpreted as part of the query string.

You don’t need any of the quoting with parameters; it can detect you’ve passed an array of strings and will deal with them appropriately.

So try:

var queryParamIds = Array.from(idSet)
log("queryParamIds : " + queryParamIds);

        var results = N1QL("SELECT c.customId, meta().id " +
            "FROM `bucket-name`.`scope-name`.`collection-name` AS tbl " +
            "WHERE tbl.docCategory = \"C\" " +
            "AND (ANY v IN ( " +
            "  SELECT DISTINCT customId AS customId" +
            "  FROM `bucket-name`.`scope-name`.`collection-name` AS tbl_a" +
            "  WHERE tbl_a.docCategory = \"C\" " +
            "  AND ANY x IN relationship.parents SATISFIES x.customId IN $1 END" +
            ") SATISFIES v.customId = tbl.customId END);",
            [queryParamIds],
            {'isPrepared': true}
        );

Alternatively you could expand into string constants within pure JS code and not pass a parameter (that said, typically we’d recommend a parameter for data that isn’t likely to be constant across invocations).

HTH.

1 Like
7

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.