How to do in MongoDB what would have been perfect for a stored procedure in SQL - recursive query

[Tim Hardy]

I have the following scenario in a CMS I am building in NodeJs with MongoDb.  I have three collections, customData, queries, and templates.  Queries can depend on customData, and templates can depend on customData, queries, and other templates.  What I need to do is to be able to very quickly figure out all of the documents that depend on a particular item when that item changes. e.g. If a particular customData item changes, I need a list of all the queries and templates that depended on that customData, as well as recursively all the templates that depend on those queries and templates.  I need to then take that list and flag all of those documents for processing/regeneration.  This is accomplished by setting a regenerate property equal to true on each of the documents in the list.  This is the sort of thing that would be perfect for a stored procedure in any database with stored procedures, but I'm struggling to figure out the best solution using MongoDb.  Every other need of my project is perfectly suited for Mongo.  This is the only scenario that I'm having trouble with.

One solution I've come up with is to store the dependencies on each document as an array of named items as follows (e.g. a doc in the templates collection):

   

    {
        name: "SomeTemplate",
        ...
        dependencies: [{type: "query", name: "top5Products"}, {type: "template", name: "header"}]
    }

The object above denotes a template that depends on the query named "top5Products" as well as the template named "header".  If either of those documents change, this template needs to be flagged for regeneration.  I can accomplish the above with a getAllDependentsOfItem function that calls the following on both the queries and templates collections, unioning the results, then recursively calling getAllDependentsOfItem on each result. 

   

this.collection.find({dependencies: item })

For instance, if the query changes, I can call it as follows, then call something else to flag all of those results...

   

let dependents = this.dependencyService.getAllDependentsOfItem({type: "query", name: "top5Products"});

This just feels very messy to me, especially wrestling with Promises and the recursion above.  I haven't even finished the above, but the whole idea of Promises and recursion just seems like a whole lot of cruft for something that would have been so simple in a stored procedure.  I just need the dependent documents flagged, and having to wade through all my layers of NodeJs code (CustomDataService, QueryService, TemplateService, DependencyService) to accomplish the above just feels wrong. Not to mention the fact that I keep coming up with a circular dependency between DependencyService and the others.  DependencyService needs to call the QueryService and TemplateService to actually talk to those collections, and they need to notify the DependencyService when something changes.  I know there are ways around that like using events or not having a DependencyService at all, or just talking directly to the Mongo driver from the DependencyService, but I haven't found a solution that feels right yet.

Another idea I had was to record the dependencies in a completely new collection called "dependencies".  Perhaps using a document schema like the following...

   

    {
        name: "SomeTemplate",
        type: "template",
        dependencies: [{type: "query", name: "top5Products"}, {type: "template", name: "header"}]
    }

This way the dependencies can be tracked completely separately from the documents themselves.  I haven't gotten very far on that solution though.

Any ideas will be greatly appreciated.

[Tim Hardy]

Anyone?

I've since written all the javascript in mongo shell that, given the type and name of a changed item, will recursively find all the dependents of that item and set their regenerate flags on those documents to "1".

My problem now is - how do I run this code on the MongoDb server by calling it from NodeJs?  I need NodeJs to control when this happens and pass the changed item into it.  I've been looking at the eval command, and that just looks like a bad idea.  I think it's been deprecated in MongoDb versions > 3.  

I can't imagine how this recursive code I wrote using cursors in mongo shell could be anything but MUCH slower when run from inside NodeJs on a different server than the database.  All the queries recursively getting each document, incurring the latency back and forth across servers, then looping through the results to update the regenerate flag on all the dependent documents...  I just can't wrap my brain around why this can't and shouldn't be done on the server somehow.  It seems like the perfect scenario for some sort of batch, server-side mechanism, like, I dunno, a stored procedure!

Please help me figure out either how to do this, or how to do it the "Mongo way".

[Kevin Adistambha]

Hi Tim

I’ve since written all the javascript in mongo shell that, given the type and name of a changed item, will recursively find all the dependents of that item and set their regenerate flags on those documents to “1”.

Sorry if I misunderstood what you are trying to achieve, but it sounds like the design you’re working with still contain traces of RDBMS best practices. Namely, the multiple collections, the need to synchronize data between those collections, and the stored procedure solution that comes naturally in the design. Typically, MongoDB schema design usually focuses on how the data is to be read/used, and how the data can be effectively sharded. However, without deep knowledge of the design process and your goals, I cannot say how the process can be improved.

There is a use case example in the Use cases page that may be of interest to you: Metadata and Asset Management. That particular use case may be similar to your goal.

Best regards,
Kevin

​