Filtering Lookup

Hi all! Good times when I use to write just one line with SQL and get the job done! Now Iā€™m facing a hard time with mongodb. The problem may look simple for some people, but I simply need to to do the following:

I got a collection called PlacedOfferings and another collection titled as ClosedOfferings. Iā€™m not sure if Iā€™m doing this the right way but all that I need is to look for the column OfferingId at the ClosedOfferings and see if the OfferingId is present. If so the Offering is closed, if not the offering is open and it should be rendered at the userā€™s dashboard front end. I tried so many approuches but I always get an sintax error or the wrong output.

I know the code bellow is wrong but Iā€™ll put it here only in order to give you a better idea of what I expect:

Moralis.Cloud.define(ā€œgetOpenOfferingsā€, async (request) => {
const query = new Moralis.Query(ā€œPlacedOfferingsā€);

const pipeline = [  
  	{
      lookup: {
        from: "ClosedOfferings",
        localField: "OfferId",
        foreignField: "OfferingId",
        
        let: {Offering:"$OfferingId"},
        pipeline: [{
          match:{ OfferingId, null },
        }],
        
        
        as: "offers",
      },
    },
  ];

  
return await query.aggregate(pipeline);

})

Iā€™ve tried the same code with or without $, $$, ā€œā€, etc, etc, etc Oh my gosh! It use to be so easy with SQLā€¦

I appreciate any code to help me solve this simple problem.

No idea how filtering work with mongo, have you checked Mongo lookup doc https://www.mongodb.com/docs/v3.6/reference/operator/aggregation/lookup/

1 Like

Yes, I did! Tried lots of different solution but nothing worked so far. It seems that thereā€™s small differences in mongoā€™s syntax when compared to the syntax used in Moralis. Look, Iā€™m completely noob in moralis, so Iā€™m probably wrong. So far I got two smart contracts running very well, but Iā€™m completely stuck in this mongodbā€™s syntax.

With the code bellow I got no error messages but I didnā€™t get the result I was expecting which was only to show the not closed offerings:

Moralis.Cloud.define(ā€œgetOpenOfferingsā€, async (request) => {
const query = new Moralis.Query(ā€œPlacedOfferingsā€);

const pipeline = [  
  	{
      lookup: {
        from: "ClosedOfferings", 
        let: { offering: "$OfferingId"},
              pipeline: [
              	{ $match: 
                	{ $expr:
                  		{ $eq: ["$offering", null ] },
              		}
                }
             ],
        as: "offers",
      },
    },
  ];

  
return await query.aggregate(pipeline);

})

I found this page which converts from SQL to mongodb syntax: https://www.site24x7.com/tools/sql-to-mongodb.html

Basically I want to convert the SQL code bellow:

SELECT *
FROM PlacedOfferings, ClosedOfferings
WHERE (ClosedOfferings.OfferingId <> PlacedOfferings.OfferId)

I got this output from converting at the address link above:

db.PlacedOfferings,ClosedOfferings .find({
ā€œ$whereā€: ā€œthis.ClosedOfferings.OfferingId < > this. PlacedOfferings.OfferIdā€
});

Now Iā€™ll see how it goes with moralis.

I tested this query in my personal server and it works exactly like I expect:

SELECT a., b. FROM PlacedOfferings a, ClosedOfferings b WHERE b.OfferingId <> a.OfferId

All that I need is to get the same result in Moralis.

you can connect directly to the mongo db instance that is used on your Moralis Server too, but I guess that it doesnā€™t help you, it works if you whitelist your IP

1 Like