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