1

I am trying to develop the PostgreSQL query that will return all the assets of a given stake address and their respective quantities.

What needs to be joined and queried in order to determine what's in a given wallet?

If I query for the tx_out's of a given stake address:

select * from tx_out where stake_address_id = X order by tx_id desc;

EDIT: Here is what I have so far, with summed quantities for grouped fingerprints and not yet with sum of ADA added to result, just the assets...

EDIT 2: With policy added, ordered by quantity of asset. I think is what I wanted, just does not have quantity of ADA row in results yet.

SELECT encode(ma.policy::bytea, 'hex') as policy, ma.fingerprint, SUM(matxo.quantity) AS total_quantity
FROM tx_out AS txo
LEFT JOIN tx_in AS txi ON txo.tx_id = txi.tx_out_id AND txo.index::smallint = txi.tx_out_index::smallint 
LEFT JOIN tx ON tx.id = txo.tx_id
LEFT JOIN block ON tx.block_id = block.id 
LEFT JOIN stake_address AS sa ON txo.stake_address_id = sa.id 
LEFT JOIN ma_tx_out AS matxo ON matxo.tx_out_id = txo.id 
LEFT JOIN multi_asset AS ma ON ma.id = matxo.ident 
WHERE sa.view = '<stake_address_here>' 
  AND txi.tx_in_id IS NULL 
  AND block.epoch_no IS NOT NULL 
  AND ma.policy IS NOT NULL 
group by ma.fingerprint, ma.policy 
order by total_quantity desc;
4
  • Have you looked at the "interesting queries" document linked from the README in the github repo? Feb 1 at 11:38
  • Hi Erik, thank you - yes, I have examined those, as well as the schema, and the ERD. github.com/IntersectMBO/cardano-db-sync/blob/master/doc/… There is the "Get all assets with quantities and count of mints" query in the interesting queries doc, but it does not retrieve for a given address / stake address..? Feb 1 at 14:26
  • I added what I have so far as an edit to my question - see what you think could be better or should be done different @ErikdeCastroLopo ? Feb 1 at 19:01
  • 1
    I have not tried to run that query, but it seems ok, Feb 2 at 2:13

1 Answer 1

-1

Here is what I have found returns the assets, and their respective quantities, for a given stake address. Enjoy!

SELECT encode(ma.policy::bytea, 'hex') as policy, ma.fingerprint, SUM(matxo.quantity) AS total_quantity FROM tx_out AS txo LEFT JOIN tx_in AS txi ON txo.tx_id = txi.tx_out_id AND txo.index::smallint = txi.tx_out_index::smallint LEFT JOIN tx ON tx.id = txo.tx_id LEFT JOIN block ON tx.block_id = block.id LEFT JOIN stake_address AS sa ON txo.stake_address_id = sa.id LEFT JOIN ma_tx_out AS matxo ON matxo.tx_out_id = txo.id LEFT JOIN multi_asset AS ma ON ma.id = matxo.ident WHERE sa.view = '<stake_address_here>' AND txi.tx_in_id IS NULL AND block.epoch_no IS NOT NULL AND ma.policy IS NOT NULL group by ma.fingerprint, ma.policy order by total_quantity desc;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.