Support addresses filter for transaction search

[faustbrian]

When using the addresses filter for the /api/transactions/search endpoint you would expect that multi payments are also shown based on their recipients. That's not the case because the only searches that are performed are on the recipientId and senderPublicKey columns while the recipients for a multi payment are in the asset column.

if (params.addresses) {
    if (!params.recipientId) {
        params.recipientId = params.addresses;
    }

    if (!params.senderPublicKey) {
        params.senderPublicKey = params.addresses.map(address => {
            return this.getPublicKeyFromAddress(address);
        });
    }

    delete params.addresses;
}

The query needs to be extended to also look up recipients through asset->payments->*->recipient. These kind of queries are very expensive on large databases and need to be properly benchmarked before being released into production.

[faustbrian]

A simple query like the following will suffice to get this working without too much of a performance hit.

SELECT id FROM transactions WHERE type = 6 AND asset->>'payments' like '%AJ97QsBNxYr4UAWdoKP2DStDCHAJGirmSM%' LIMIT 1;

Using the type is important because the query otherwise will be very slow. The reason for that is that the type column has an index while asset doesn't.

[faustbrian]

Implemented in 3.0

[rainydio]

It's implemented not as separate addresses filter. Wallet api controller filters transactions using three expressions (joined with OR):

• ...filter_from_request AND recipientId = :wallet_address
• ...filter_from_request AND asset @> :payment_with_wallet_address
• ...filter_from_request AND sender_public_key = :wallet_public_key (if public key is known)

https://github.com/ArkEcosystem/core/blob/develop/packages/core-api/src/controllers/wallets.ts#L50-L57