Returns the approvals (standard and operator) for all ERC721 tokens owned by a given address

Parameters

  1. owner (address as hex string, default: 0x000a22cdb94efbb7260ad038e1b2d377d950ed78)
    1. The owner for which we want to check their portfolio.
  2. chain_id (uint64, default: 0)
    1. Optional. Can be used to view balances from a certain chain. If unset, defaults to 0 and returns results from all chains.
  3. token_address (address as hex string, default: 0x0000000000000000000000000000000000000000)
    1. Optional. Can be used to filter results to a specific token. If unset, defaults to address(0) and all tokens are checked.
  4. block_number (uint64, default: 0)
    1. Optional. Can be used to view allowances at a particular block. If unset, defaults to 0 and returns latest.
  5. limit (uint32, default: 100)
    1. Number of records you want returned.
  6. offset (uint32, default: 0)
    1. For pagination. If you're showing ten records per page, choose offset = 9 for the second page.

Returns

Array with the following fields:

  1. owner (address as hex string)
    1. The owner for which we checked the portfolio.
  2. chain_id (int64)
    1. The chain for the token
  3. token_address (address as hex string)
    1. The token's address.
  4. token_name (string)
    1. The token's name.
  5. token_symbol (string)
    1. The token's symbol.
  6. is_for_all (bool)
    1. true for operator approvals (setApprovalForAll) and false for token-specific approvals (approve). See table usage notes.
  7. operator(address as hex string)
    1. The operator's address for operator approvals, else address(0).
  8. token_id (int256 as decimal string)
    1. The token's id for token-specific approvals, else 0.
  9. approved_address (address as hex string)
    1. The approved address for token-specific approvals, else address(0).
  10. txn_last_changed (address as hex string)
    1. The most recent transaction that impacted the approval. Note that this will include ERC721 transfers, which reset approvals (even if none was set).
  11. block_last_changed (int64)
    1. The most recent block that impacted the approval.

The array is sorted in descending order of block_last_changed.

Query

WITH latest_block_number AS (
  SELECT token_address,
    chain_id,
    is_for_all,
    token_id,
    MAX(block_number) AS latest_block_number
  FROM @sim.erc721_approval_changes_block
  WHERE owner = lower($owner)
  	AND (
      chain_id = $chain_id
      OR $chain_id = 0
    )
    AND (
      token_address = lower($token_address)
      OR $token_address = '0x0000000000000000000000000000000000000000'
    )
    AND (
      block_number <= $block_number
      OR $block_number = 0
    )
  GROUP BY token_address,
    is_for_all,
    token_id,
    chain_id
)
SELECT a.owner,
  a.chain_id,
  a.token_address,
  a.token_name,
  a.token_symbol,
  a.is_for_all,
  a.operator,
  a.token_id,
  a.approved_address,
  a.approved,
  a.txn_hash as txn_last_changed,
  a.block_number as block_last_changed
FROM @sim.erc721_approval_changes_block a
  INNER JOIN latest_block_number lbn ON a.block_number = lbn.latest_block_number
  AND a.token_address = lbn.token_address
  AND a.is_for_all = lbn.is_for_all
  AND a.token_id = lbn.token_id
  AND a.chain_id = lbn.chain_id
WHERE a.owner = lower($owner)
ORDER BY block_last_changed desc
LIMIT $limit OFFSET $offset