Adahandle Interesting Queries Cardano

adahandle-query-help.md

General queries for adahandle

Retrieve $handle owned by an address

SELECT
	ENCODE(MA.name, 'escape') ada_handle
FROM
	MA_TX_OUT MTX
	INNER JOIN MULTI_ASSET MA ON MA.id = MTX.ident
		AND MA.policy = '\xf0ff48bbb7bbe9d59a40f1ce90e9e9d0ff5002ec48f232b49ca0fb9a' 
	INNER JOIN TX_OUT TXO ON TXO.ID = MTX.TX_OUT_ID
	LEFT JOIN TX_IN ON TXO.TX_ID = TX_IN.TX_OUT_ID
		AND TXO.INDEX::smallint = TX_IN.TX_OUT_INDEX::smallint
WHERE
	txo.address = ?
	AND TX_IN.TX_IN_ID IS NULL;

Retrieve address from a $handle

SELECT
	TXO.address
FROM
	MA_TX_OUT MTX
	INNER JOIN MULTI_ASSET MA ON MA.id = MTX.ident
	INNER JOIN TX_OUT TXO ON TXO.ID = MTX.TX_OUT_ID
	LEFT JOIN stake_address ADDR ON ADDR.id = TXO.stake_address_id
	LEFT JOIN TX_IN ON TXO.TX_ID = TX_IN.TX_OUT_ID
		AND TXO.INDEX::smallint = TX_IN.TX_OUT_INDEX::smallint
WHERE
	MA.policy = '\xf0ff48bbb7bbe9d59a40f1ce90e9e9d0ff5002ec48f232b49ca0fb9a'
	AND MA.name = decode(encode(?1, 'hex'), 'hex')
	AND TX_IN.TX_IN_ID IS NULL;