CODEWITHSUNDEEP
mysqlmongodbmongodb-query
Rabih
Rabih

Reputation: 318

How to Simulate subquery in MongoDB query condition

Let's suppose that I have a product logs collection, all changes are being done on my products will be recorded in this collection ie :

+------------------------------+
| productId - status - comment |
| 1           0        ....    |
| 2           0        ....    |
| 1           1        ....    |
| 2           1        ....    |
| 1           2        ....    |
| 3           0        ....    |
+------------------------------+

I want to get all products which their status is 1 but hasn't became 2. In SQL the query would look something like :

select productId from productLog as PL1 
where 
   status = 1 
   and productId not in (
       select productId from productLog as PL2 where 
           PL1.productId = PL2.productId and PL2.status = 2
   )
group by productId

I'm using native PHP MongoDB driver.

Upvotes: 1

Views: 2276

Answers (2)

PerihanK
PerihanK

Reputation: 1

This mongo query above doesn't meet the requirements in question, the result of the mongo-query includes documents with productId=1, however the result of the SQL in question doesn't. Because in sample data: there exists 1 record with status=2, and productId of that document is 1.

So, assuming db.productLog.insert executed as stated above, you can use the code below to get the results:

//First: subquery for filtering records having status=2: 
var productsWithStatus2 = db.productLog .find({"status":2}).map(function(rec) { return rec.productId; }); 

//Second:final query to get productIds which there not exists having status=2 with same productId :
db.productLog.aggregate([ {"$match":{productId:{$nin:productsWithStatus2}}},{"$group": {"_id": "$productId"}}])  ;

//Alternative for Second final query:
//db.productLog.distinct("productId",{productId:{$nin:productsWithStatus2}}); 

//Alternative for Second final query,get results with product and status detail:
//db.productLog.find({productId:{$nin:productsWithStatus2}});

Upvotes: 0

Blakes Seven
Blakes Seven

Reputation: 50416

Well since the logic here on the subquery join is simply that exactly the same key matches the other then:

Setup

db.status.insert([
    { "productId": 1, "status": 0 },
    { "productId": 2, "status": 0 },
    { "productId": 1, "status": 1 },
    { "productId": 2, "status": 1 },
    { "productId": 1, "status": 2 },
    { "productId": 3, "status": 0 }
])

Then use .aggregate():

db.status.aggregate([
    { "$match": {
        "status": { "$ne": 2 }
    }},
    { "$group": {
        "_id": "$productId"
    }}
])

Or using map reduce (with a DBRef):

db.status.mapReduce(
    function() {
        if ( this.productId.$oid == 2 ) {
            emit( this.prouctId.$oid, null )
        }
    },
    function(key,values) {
        return null;
    },
    { "out": { "inline": 1 } }
);

But again the SQL here was as simple as:

select productId
from productLog
where status <> 2
group by productId

Without the superfluous join on exactly the same key value

Upvotes: 1

Related Questions