Collation for aggregate([<pipeline>]) not supported by Moralis?

Technical Issue Reporting
#1

I’m trying to order stringNumbers as Numbers (price in wei). You can set the interpreted type of the sort value to things like date or locale character interpreation, but also to interpret stringNumbers as Numbers for sorting:

Mongodb:

db.collection.aggregate([<pipeline>]).collation({locale:"en_US", numericOrdering:true})

In Moralis Cloud this gives an error:

queryResults = await query.aggregate(pipeline).collation({locale:"en_US", numericOrdering:true})
...
Uncaught (in promise) Error: query.aggregate(...).collation is not a function

I’m trying to find a workaround, but wondering why it seems not supported in Moralis.

#2

We are working on exposing the raw MongoDB connection so more complex queries like this can be done. Will update once we have it :raised_hands:

Should not be long!

2 Likes
#3

For now I’ve managed to sort on wei prices with this query, maybe useful for others:

// get all items including market data and sorting and filter by owner
Moralis.Cloud.define('getAllItems', async (request) => {
  const query = new Moralis.Query('EthNFTOwners')
  query.equalTo('token_address', request.params.token_address)
  // optional filter for owner (my nfts)
  if (request.params.owner_of) {
    query.equalTo('owner_of', request.params.owner_of)
  }
  // sorting params
  const sort = request.params.sort && request.params.sort === 'asc' ? 1 : -1
  const sortBy = request.params.sortBy ?request.params.sortBy : 'block_number'
  const pipeline = [
    {
      lookup: {
        from: request.params.network+'ItemsForSale',
        localField: "token_id",
        foreignField: "token_id",
        as: "marketItem"
      }
    },
    {
      project: {
        createdAt: 1,
        block_number: 1,
        token_id: 1,
        token_address: 1,
        token_uri: 1,
        owner_of: 1,
        askingPrice: { $first: "$marketItem.askingPrice" },
        sort_price: {
          $cond: {
            if: { $eq: [ [], "$marketItem" ] },
            then: 0,
            else: {
              $toInt: {
                $substr: [{ $first: "$marketItem.askingPrice" }, 0, { $add: [ { $strLenCP: { $first: "$marketItem.askingPrice" } }, -15 ] }]
              }
            }
          }
         },
      }
    },
    { sort : { [sortBy]: sort } },
  ]
  const queryResults = await query.aggregate(pipeline)
  let results = []
  if (queryResults) {
    for (let i = 0; i < queryResults.length; i++) {
      const userQuery = new Moralis.Query(Moralis.User)
      userQuery.equalTo('accounts', queryResults[i].owner_of)
      const userItem = await userQuery.first({useMasterKey:true})
      if (userItem) {
        queryResults[i].username = userItem.attributes.username
        queryResults[i].avatar = userItem.attributes.avatar ? userItem.attributes.avatar.url() : null
      }
      results.push(queryResults[i])   
    }
  }
  return results
},{
  fields : ['token_address'],
})

What is does:

  1. get all NFTs under my contract
  2. optionally filter out items from a certain owner by using request.params
  3. set optional sort and sortBy from request.params to sort on block_number(*) or askingPrice
  4. inject market data from ItemsForSale for each item
  5. project fields that I want in the output
  6. convert wei askingPrice string to a smaller number by stripping zeros and converting to Int, which can be sorted properly
  7. inject User data: I didn’t manage to do it in the pipeline because I can’t get the avatar.url() from that data, as the avatar field only outputs the filename and no access to url()

(*) about using block_number instead of date ‘createdAt’: for some reason I don’t have access to createdAt to convert it to a date format. The pipeline outputs the date as a string and is thus unsortable.

image
image2964×1634 821 KB

3 Likes
Scrape NFT token_uri data into Moralis db
#4

Great job @matiyin

Yesterday I started to deal with changing the data type in a similar way, but did not have time to finish.

Thanks for the detailed solution to the problem! :man_mechanic:

#5

This is amazing! Keep up the great work @matiyin! :raised_hands:

1 Like
#6

@malik I’m wondering if it’s possible to get the User avatar.url() in a lookup / pipeline.

I can only get User like this is a pipeline, so straight from the db:

    {
      lookup: {
        from: '_User',
        localField: 'owner_of',
        foreignField: 'accounts',
        as: 'userItem'
      }
    },

Which only returns a limited set of actual db values and no access to methods because it’s not a Moralis object. There’s only a value ‘avatar’ which contains the filename:

avatar: "xxxxx_myavatar.png"

How would one append the full server url to use in the output?

For now I just append the User data directly to the results:

  const queryResults = await query.aggregate(pipeline)
  let results = []
  if (queryResults) {
    for (let i = 0; i < queryResults.length; i++) {
      const userQuery = new Moralis.Query(Moralis.User)
      userQuery.equalTo('accounts', queryResults[i].owner_of)
      const userItem = await userQuery.first({useMasterKey:true})
      if (userItem) {
        queryResults[i].username = userItem.attributes.username
        queryResults[i].avatar = userItem.attributes.avatar ? userItem.attributes.avatar.url() : null
      }
      results.push(queryResults[i])   
    }
  }
  return results

Fine for now, but prevents filtering and sorting on User (for example username) while still being able to use proper pagination with a $limit.
A solution is to do both, $lookup for sorting and filtering and appending the avatar._url to results with a Moralis.Query, spending a double call to the User table for each item.

So yes, not optimal. If you or someone knows a better way, let me know :slight_smile: :muscle: