I Need to convert mysql script to mongodb scheduled events

This is mysql query:

DELETE FROM HNG_BASKET_ITEM WHERE PRODUCT_ID NOT IN (
  SELECT DISTINCT PRODUCT_ID FROM HNG_PRODUCT_MASTER WHERE IS_VISIBLE = 'Y'
); 

Please help me to join these tables

I’m new to MongoDB please share some references on same.

db.basket.remove(
   db.basket.aggregate({ 
      $lookup: {
         from: "product_master",
         let:{
            productHighlights.isVisible :"Y",
            localField:"items.skuid",
            foreignField:"_id",as:"Deleted items" 
         }
     })
)

I have tried something like this please verify is this valid

Hi @Varun_Shetty ,

Doing this shell syntax with 2 inner queries doesn’t sound right and the returned data from inner aggregation needs to be turned into an array and passed to a $nin for the removal.

I would suggest coding this in 2 commands:

var deleted_list = db.product_master.aggregate([{$match : { "productHighlights.isVisible" :"Y"}},
{$group : { _id : null , list : { $addToSet : "$product_id"}}}])

db.basket.remove({product_id : { $nin : deleted_list.list}})

PLEASE NOTE THAT THIS CODE WAS NEVER TESTED AND IT MAY NOT FIT YOUR EXACT FIELD NAMES OR COLLECTIONS , SO PLEASE TEST IT CAREFULLY IN A DEV ENVIRONMENT. ITS MAINLY A PSEDOCODE

Thanks

1 Like

@Pavel_Duchovny Thank you for the suggestion will try this .

@Pavel_Duchovny There is one scenario we need to create a trigger and when i insert a data into one collection and i need to insert same matching columns data to one more collection .can you share some sample functions on same .

Hi @Varun_Shetty ,

I have no.clue on what exactly is your request

You have to.be more specific, what you insert where and where do you expect that data to ve written to…

– ADD BELOW TRIGGER ON ORDER_PAYMENT_DETAILS TABLE FOR AFTER INSERT/UPDATE EVENTS

– USAGE: INSERTS CURRENT PAYMENT STATUS IN ORDER_PAYMENT_HISTORY TABLE WHEN ON CHANGE OF ROW IN ORDER_PAYMENT_DETAILS TABLE

BEGIN

INSERT INTO ORDER_PAYMENT_HISTORY (ORDER_ID, STATUS_ID, PAYMENT_METHOD_TYPE_ID, AMOUNT, GATEWAY_NAME, GATEWAY_STATUS, GATEWAY_MESSAGE, GATEWAY_ISSUER_CODE, REFERENCE_NUM, MERCHANT_TXN_ID)

VALUES (NEW.ORDER_ID, NEW.STATUS_ID, NEW.PAYMENT_METHOD_TYPE_ID, NEW.AMOUNT, NEW.GATEWAY_NAME, NEW.GATEWAY_STATUS, NEW.GATEWAY_MESSAGE, NEW.GATEWAY_ISSUER_CODE, NEW.REFERENCE_NUM, NEW.MERCHANT_TXN_ID);

END

So this is the query and i need to replicate same query into mongodb triggers and i have understood how to add triggers to collection and add document.
So here on change of order payment details data needs to be inserted to order payment history how we can add same inserted document to one more collection and sql we do have New concept.This is the concern i have

Hi @Varun_Shetty ,

So basically you need to set “on insert” trigger with fullDocument flag enabled.

You get the full document extracted from the event variable.

Then you take it and insert it into the target collection.

Thanks
Pavel

@Pavel_Duchovny yes correct

1 Like