CODEWITHSUNDEEP
mongodbmongodb-query
Rohit
Rohit

Reputation: 3156

Recursive search in MongoDB

I have a Mongo collection that contains data sets with a pmID and a replyID, where the replyID can be null or a pmID. I'm trying to figure out, if I select by a pmID, how I can also get the PM where the pmID = replyID, and if that set's replyID isn't null, the object that matches that, etc.

{
    pmID: 1,
    replyID: null
},
{
    pmID: 2,
    replyID: null
},
{
    pmID: 3,
    replyID: 1
},
{
    pmID: 4,
    replyID: 3
}

So if I selected on pmID = 4, I also wanna get 3 and 1. I'm debating just doing a one by one select, but I'm hoping there's an easier answer.

Upvotes: 1

Views: 802

Answers (2)

wdberkeley
wdberkeley

Reputation: 11671

In addition to Neil's suggestion to use ancestor arrays, if you're modelling conversations you can group together conversations with a conversation_id and recall all messages connected to a given one by conversation_id:

{
    "pmId": 1,
    "conversationId" : 0
},
{
    "pmId": 3,
    "conversationId" : 0
},    
{
    "pmId": 4,
    "conversationId" : 0
}

> var pm = db.test.findOne({ "pmId" : 3 })
> db.test.find({ "conversationId" : pm.conversationId })

Upvotes: 0

Neil Lunn
Neil Lunn

Reputation: 151170

Not really sure of your use case here, but from a glance I would personally model to store the "ancestors" in order with the object like so:

{
    "pmId": 1,
    "replyIds": [ ]
},
{
    "pmId": 3,
    "replyIds": [ 1 ]
},    
{
    "pmId": 4,
    "replyIds": [ 3, 1 ]
}

In either that order or in reverse depending on how you implement the ancestor history.

The point being that when issuing a "reply" to an existing object, you should already have the data loaded that tells you if there are any existing replies. The new object then pushes the id of the the object it is being issued in reply to onto the the existing "replyIds" array when that new object is created:

// data is "pmId": 3 object

data.replyIds.unshift( data.pmId );
data.pmId = getNewPmId(); // whatever

db.collection.insert(data);

It's a fairly simple pattern which obviates the need to "recursively walk" through queries on read requests, and also allows for simple writes.

Retrieving all ancestors is just a simple matter of passing the array of replies to $in:

var data = db.collection.findOne({ "pmId": 4 });

db.collection.find({ "pmId": { "$in": data.replyIds } })

Upvotes: 2

Related Questions