Join on moralis DB

Moralis General
#1

Hi @here I have a use case which is like I need to get the all nfts transfers data based on the wallet address and smart contract address with value column (eth value for transfer) which means based on this column I can identify the (sell/buy transfer) for these nfts transfer by using moralis DB.

For this like Moralis support two tables i.e ( EthNFTTransfers and EthTransactions ). I am asking this because EthNFTTransfers does not include the value column for this I need to join EthTransactions table for this. How can i do this ? On which keys I can join ? and How ?

Thanks

#2

maybe you can try on transaction hash, in case that there are not more than on transfer per transaction

#3

yeah i have tried it out but the data which is returned is not correct.

#4

What didn’t work? What happened?

#5

Here is the code:


async function nftTransfers(){
    const userAddress = '0x9ad80abc02ba459d7a7e252b3d173f471daa1012';
    const query = new Moralis.Query("EthNFTTransfers");
        const pipeline = [
            { match: { from_address: userAddress } },
            { lookup: {
                from: "EthTransactions",
                let: { hashValue: "$hash" },
                pipeline: [
                  {$match: {$expr:  { $eq: ["$transaction_hash", "$$hashValue"]}
                    }},
                  { $project: { gas: 1, value: 1, hash: 1. ,_id :0 }},
                ],
                as: "TokenWithPrice"
              }},
              { unwind : "$TokenWithPrice" },
              {limit : 100} 
            ];
    const result = await query.aggregate(pipeline);
    console.log(result)

} 
nftTransfers();

Here is the response:

{
    from_address: '0x9ad80abc02ba459d7a7e252b3d173f471daa1012',
    log_index: 273,
    to_address: '0xbb683e735ca23fdb9ba6f22f3608bf5ed20b845f',
    token_address: '0xf4ee95274741437636e748ddac70818b4ed7d043',
    token_id: '9351',
    transaction_hash: '0x79481308e7d78d2ee6a5b4e3eb1e3c2147f3b1c3b11231b0c7fb42fe9b42ffc5',
    createdAt: '2022-02-21T12:34:18.908Z',
    updatedAt: '2022-02-21T12:34:18.908Z',
    amount: '1',
    block_hash: '0x5238ddd96256f97398fb2d5eeb696bcd066f0a69eff1c96722b869951e24d442',
    block_number: 12857573,
    block_timestamp: { __type: 'Date', iso: '2021-07-19T14:30:54.000Z' },
    confirmed: true,
    contract_type: 'ERC721',
    historical: true,
    transaction_index: 142,
    transaction_type: 'Single',
    TokenWithPrice: {
      hash: '0x8f2f64b8832ac2e6b4309ee2022a336ed93c299f76ecc0766cecd07881c8c897',
      gas: 191664,
      value: '17730108140703232'
    },
    objectId: 'CgRblSR9z8bGVqvSPIsrabmH'
  },
  {
    from_address: '0x9ad80abc02ba459d7a7e252b3d173f471daa1012',
    log_index: 273,
    to_address: '0xbb683e735ca23fdb9ba6f22f3608bf5ed20b845f',
    token_address: '0xf4ee95274741437636e748ddac70818b4ed7d043',
    token_id: '9351',
    transaction_hash: '0x79481308e7d78d2ee6a5b4e3eb1e3c2147f3b1c3b11231b0c7fb42fe9b42ffc5',
    createdAt: '2022-02-21T12:34:18.908Z',
    updatedAt: '2022-02-21T12:34:18.908Z',
    amount: '1',
    block_hash: '0x5238ddd96256f97398fb2d5eeb696bcd066f0a69eff1c96722b869951e24d442',
    block_number: 12857573,
    block_timestamp: { __type: 'Date', iso: '2021-07-19T14:30:54.000Z' },
    confirmed: true,
    contract_type: 'ERC721',
    historical: true,
    transaction_index: 142,
    transaction_type: 'Single',
    TokenWithPrice: {
      hash: '0x62fa9041e91b0ef7d2e92a4c68976e846e801fbd2ba6eb3474a5455b82fd64b8',
      gas: 46079,
      value: '0'
    },
    objectId: 'CgRblSR9z8bGVqvSPIsrabmH'
  },

Here you will see that Transaction_hash and hash is different in each response
Is my code is correct ? or any thing else you can suggest ?

#6

If you don’t have too many rows you could run a job that adds that value column

#7

How can i do this ? Like as i have shared a code with you is there any wrong thing with my code why I am asking this because

 {
    from_address: '0x9ad80abc02ba459d7a7e252b3d173f471daa1012',
    log_index: 273,
    to_address: '0xbb683e735ca23fdb9ba6f22f3608bf5ed20b845f',
    token_address: '0xf4ee95274741437636e748ddac70818b4ed7d043',
    token_id: '9351',
    transaction_hash: '0x79481308e7d78d2ee6a5b4e3eb1e3c2147f3b1c3b11231b0c7fb42fe9b42ffc5',
    createdAt: '2022-02-21T12:34:18.908Z',
    updatedAt: '2022-02-21T12:34:18.908Z',
    amount: '1',
    block_hash: '0x5238ddd96256f97398fb2d5eeb696bcd066f0a69eff1c96722b869951e24d442',
    block_number: 12857573,
    block_timestamp: { __type: 'Date', iso: '2021-07-19T14:30:54.000Z' },
    confirmed: true,
    contract_type: 'ERC721',
    historical: true,
    transaction_index: 142,
    transaction_type: 'Single',
    TokenWithPrice: {
      hash: '0x62fa9041e91b0ef7d2e92a4c68976e846e801fbd2ba6eb3474a5455b82fd64b8',
      gas: 46079,
      value: '0'
    },
    objectId: 'CgRblSR9z8bGVqvSPIsrabmH'
  },

You will se the TokenWithPrice having the hash is not matched with transaction_hash in the response object.

#8

You could do two queries without a pipeline. Maybe it should work with pipeline too

#9

So you are saying that I need to use the lookup for join without pipline right ?

#10

maybe, I’m not expect in pipelines now to say exactly what is wrong with that query