services/wallet/activity/filter.sql
-- Query includes duplicates, will return multiple rows for the same transaction if both to and from addresses are in the address list.
--
-- The switch for tr_type is used to de-conflict the source for the two entries for the same transaction
--
-- UNION ALL is used to avoid the overhead of DISTINCT given that we don't expect to have duplicate entries outside the sender and receiver addresses being in the list which is handled separately
--
-- Only status FailedAS, PendingAS and CompleteAS are returned. FinalizedAS requires correlation with blockchain current state. As an optimization we approximate it by using timestamp information; see startTimestamp and endTimestamp
--
-- ContractDeploymentAT is subtype of SendAT and MintAT is subtype of ReceiveAT. It means query must prevent returning MintAT when filtering by ReceiveAT or ContractDeploymentAT when filtering by SendAT. That required duplicated code in filter by type query, to maintain performance.
--
-- Token filtering has two parts
-- 1. Filtering by symbol (multi_transactions and pending_transactions tables) where the chain ID is ignored, basically the filter_networks will account for that
-- 2. Filtering by token identity (chain and address for transfers table) where the symbol is ignored and all the token identities must be provided
--
WITH filter_conditions AS (
SELECT
? AS startFilterDisabled,
? AS startTimestamp,
? AS endFilterDisabled,
? AS endTimestamp,
? AS filterActivityTypeAll,
? AS filterActivityTypeSend,
? AS filterActivityTypeReceive,
? AS filterActivityTypeContractDeployment,
? AS filterActivityTypeMint,
? AS mTTypeSend,
? AS fromTrType,
? AS toTrType,
? AS filterAllAddresses,
? AS filterAllToAddresses,
? AS filterAllActivityStatus,
? AS filterStatusCompleted,
? AS filterStatusFailed,
? AS filterStatusFinalized,
? AS filterStatusPending,
? AS statusFailed,
? AS statusCompleted,
? AS statusFinalized,
? AS statusPending,
? AS includeAllTokenTypeAssets,
? AS includeAllCollectibles,
? AS includeAllNetworks,
? AS pendingStatus,
? AS nowTimestamp,
? AS layer2FinalisationDuration,
? AS layer1FinalisationDuration,
X'0000000000000000000000000000000000000000' AS zeroAddress,
'0x28c427b0611d99da5c4f7368abe57e86b045b483c4689ae93e90745802335b87' as communityMintEvent
),
-- This UNION between CTE and TEMP TABLE acts as an optimization. As soon as we drop one or use them interchangeably the performance drops significantly.
filter_addresses(address) AS (
SELECT
address
FROM
filter_addresses_table
WHERE
(
SELECT
filterAllAddresses
FROM
filter_conditions
) != 0
UNION
ALL
SELECT
*
FROM
(
VALUES
%s
)
WHERE
(
SELECT
filterAllAddresses
FROM
filter_conditions
) = 0
),
filter_to_addresses(address) AS (
VALUES
%s
),
assets_token_codes(token_code) AS (
VALUES
%s
),
assets_erc20(chain_id, token_address) AS (
VALUES
%s
),
assets_erc721(chain_id, token_id, token_address) AS (
VALUES
%s
),
filter_networks(network_id) AS (
VALUES
%s
),
tr_status AS (
SELECT
multi_transaction_id,
MIN(status) AS min_status,
COUNT(*) AS count,
network_id
FROM
transfers
WHERE
transfers.loaded == 1
AND transfers.multi_transaction_id != 0
GROUP BY
transfers.multi_transaction_id
),
tr_network_ids AS (
SELECT
multi_transaction_id
FROM
transfers
WHERE
transfers.loaded == 1
AND transfers.multi_transaction_id != 0
AND network_id IN filter_networks
GROUP BY
transfers.multi_transaction_id
),
pending_status AS (
SELECT
multi_transaction_id,
COUNT(*) AS count,
network_id
FROM
pending_transactions,
filter_conditions
WHERE
pending_transactions.multi_transaction_id != 0
AND pending_transactions.status = pendingStatus
GROUP BY
pending_transactions.multi_transaction_id
),
pending_network_ids AS (
SELECT
multi_transaction_id
FROM
pending_transactions,
filter_conditions
WHERE
pending_transactions.multi_transaction_id != 0
AND pending_transactions.status = pendingStatus
AND pending_transactions.network_id IN filter_networks
GROUP BY
pending_transactions.multi_transaction_id
),
layer2_networks(network_id) AS (
VALUES
%s
),
mint_methods(method_hash) AS (
%s
)
SELECT
transfers.hash AS transfer_hash,
NULL AS pending_hash,
transfers.network_id AS network_id,
0 AS multi_tx_id,
transfers.timestamp AS timestamp,
NULL AS mt_type,
CASE
WHEN from_join.address IS NOT NULL AND to_join.address IS NULL THEN fromTrType
WHEN to_join.address IS NOT NULL AND from_join.address IS NULL THEN toTrType
WHEN from_join.address IS NOT NULL AND to_join.address IS NOT NULL THEN
CASE
WHEN transfers.address = transfers.tx_from_address THEN fromTrType
WHEN transfers.address = transfers.tx_to_address THEN toTrType
ELSE NULL
END
ELSE NULL
END as tr_type,
transfers.tx_from_address AS from_address,
transfers.tx_to_address AS to_address,
transfers.address AS owner_address,
transfers.amount_padded128hex AS tr_amount,
NULL AS ptr_amount,
NULL AS mt_from_amount,
NULL AS mt_to_amount,
CASE
WHEN transfers.status IS 1 THEN CASE
WHEN transfers.timestamp > 0
AND filter_conditions.nowTimestamp >= transfers.timestamp + (
CASE
WHEN transfers.network_id in layer2_networks THEN layer2FinalisationDuration
ELSE layer1FinalisationDuration
END
) THEN statusFinalized
ELSE statusCompleted
END
ELSE statusFailed
END AS agg_status,
1 AS agg_count,
transfers.token_address AS token_address,
CASE
WHEN LENGTH(transfers.token_id) = 0 THEN X'00'
ELSE transfers.token_id
END AS tmp_token_id,
NULL AS token_code,
NULL AS from_token_code,
NULL AS to_token_code,
NULL AS out_network_id,
NULL AS in_network_id,
transfers.type AS type,
transfers.contract_address AS contract_address,
CASE
WHEN transfers.tx_from_address = zeroAddress AND transfers.type = "erc20" THEN substr(json_extract(tx, '$.input'), 1, 10)
ELSE NULL
END AS method_hash,
CASE
WHEN transfers.tx_from_address = zeroAddress AND transfers.type = "erc20" THEN (SELECT 1 FROM json_each(transfers.receipt, '$.logs' ) WHERE json_extract( value, '$.topics[0]' ) = communityMintEvent)
ELSE NULL
END AS community_mint_event,
CASE
WHEN transfers.type = 'erc20' THEN (SELECT community_id FROM tokens WHERE transfers.token_address = tokens.address AND transfers.network_id = tokens.network_id)
WHEN transfers.type = 'erc721' OR transfers.type = 'erc1155' THEN (SELECT community_id FROM collectible_data_cache WHERE transfers.token_address = collectible_data_cache.contract_address AND transfers.network_id = collectible_data_cache.chain_id)
ELSE NULL
END AS community_id
FROM
transfers
CROSS JOIN filter_conditions
LEFT JOIN filter_addresses from_join ON transfers.tx_from_address = from_join.address
LEFT JOIN filter_addresses to_join ON transfers.tx_to_address = to_join.address
WHERE
transfers.loaded == 1
AND transfers.multi_transaction_id = 0
AND (
(
startFilterDisabled
OR transfers.timestamp >= startTimestamp
)
AND (
endFilterDisabled
OR transfers.timestamp <= endTimestamp
)
)
AND (
-- Check description at the top of the file why code below is duplicated
filterActivityTypeAll
OR (
filterActivityTypeSend
AND tr_type = fromTrType -- Check NOT ContractDeploymentAT
AND NOT (
transfers.tx_to_address IS NULL
AND transfers.type = 'eth'
AND transfers.contract_address IS NOT NULL
AND transfers.contract_address != zeroAddress
)
)
OR (
filterActivityTypeReceive
AND tr_type = toTrType -- Check NOT MintAT
AND NOT (
(
transfers.tx_from_address IS NULL
OR transfers.tx_from_address = zeroAddress
)
AND (
transfers.type = 'erc721'
OR (
transfers.type = 'erc20'
AND (
(method_hash IS NOT NULL AND method_hash IN mint_methods)
OR community_mint_event IS NOT NULL
)
)
)
)
)
OR (
filterActivityTypeContractDeployment
AND tr_type = fromTrType
AND transfers.tx_to_address IS NULL
AND transfers.type = 'eth'
AND transfers.contract_address IS NOT NULL
AND transfers.contract_address != zeroAddress
)
OR (
filterActivityTypeMint
AND tr_type = toTrType
AND (
transfers.tx_from_address IS NULL
OR transfers.tx_from_address = zeroAddress
)
AND (
transfers.type = 'erc721'
OR (
transfers.type = 'erc20'
AND (
(method_hash IS NOT NULL AND method_hash IN mint_methods)
OR community_mint_event IS NOT NULL
)
)
)
)
)
AND (
filterAllAddresses -- Every account address has an "owned" entry either as to or from
OR (owner_address IN filter_addresses)
)
AND (
filterAllToAddresses
OR (transfers.tx_to_address IN filter_to_addresses)
)
AND (
includeAllTokenTypeAssets
OR (
transfers.type = 'eth'
AND ('ETH' IN assets_token_codes)
)
OR (
transfers.type = 'erc20'
AND (
(
transfers.network_id,
transfers.token_address
) IN assets_erc20
)
)
)
AND (
includeAllCollectibles
OR (
transfers.type = "erc721"
AND (
(
transfers.network_id,
tmp_token_id,
transfers.token_address
) IN assets_erc721
)
)
)
AND (
includeAllNetworks
OR (transfers.network_id IN filter_networks)
)
AND (
filterAllActivityStatus
OR (
filterStatusCompleted
AND agg_status = statusCompleted
)
OR (
filterStatusFinalized
AND agg_status = statusFinalized
)
OR (
filterStatusFailed
AND agg_status = statusFailed
)
)
UNION
ALL
SELECT
NULL AS transfer_hash,
pending_transactions.hash AS pending_hash,
pending_transactions.network_id AS network_id,
0 AS multi_tx_id,
pending_transactions.timestamp AS timestamp,
NULL AS mt_type,
CASE
WHEN from_join.address IS NOT NULL AND to_join.address IS NULL THEN fromTrType
WHEN to_join.address IS NOT NULL AND from_join.address IS NULL THEN toTrType
WHEN from_join.address IS NOT NULL AND to_join.address IS NOT NULL THEN fromTrType
ELSE NULL
END as tr_type,
pending_transactions.from_address AS from_address,
pending_transactions.to_address AS to_address,
NULL AS owner_address,
NULL AS tr_amount,
pending_transactions.value AS ptr_amount,
NULL AS mt_from_amount,
NULL AS mt_to_amount,
statusPending AS agg_status,
1 AS agg_count,
NULL AS token_address,
NULL AS tmp_token_id,
pending_transactions.symbol AS token_code,
NULL AS from_token_code,
NULL AS to_token_code,
NULL AS out_network_id,
NULL AS in_network_id,
pending_transactions.type AS type,
NULL as contract_address,
NULL AS method_hash,
NULL AS community_mint_event,
NULL AS community_id
FROM
pending_transactions
CROSS JOIN filter_conditions
LEFT JOIN filter_addresses from_join ON pending_transactions.from_address = from_join.address
LEFT JOIN filter_addresses to_join ON pending_transactions.to_address = to_join.address
WHERE
pending_transactions.multi_transaction_id = 0
AND pending_transactions.status = pendingStatus
AND (
filterAllActivityStatus
OR filterStatusPending
)
AND includeAllCollectibles
AND (
(
startFilterDisabled
OR timestamp >= startTimestamp
)
AND (
endFilterDisabled
OR timestamp <= endTimestamp
)
)
AND (
filterActivityTypeAll
OR filterActivityTypeSend
)
AND (
filterAllAddresses
OR tr_type NOT NULL
)
AND (
filterAllToAddresses
OR (pending_transactions.to_address IN filter_to_addresses)
)
AND (
includeAllTokenTypeAssets
OR (
UPPER(pending_transactions.symbol) IN assets_token_codes
)
)
AND (
includeAllNetworks
OR (
pending_transactions.network_id IN filter_networks
)
)
UNION
ALL
SELECT
NULL AS transfer_hash,
NULL AS pending_hash,
NULL AS network_id,
multi_transactions.id AS multi_tx_id,
multi_transactions.timestamp AS timestamp,
multi_transactions.type AS mt_type,
NULL as tr_type,
multi_transactions.from_address AS from_address,
multi_transactions.to_address AS to_address,
multi_transactions.from_address AS owner_address,
NULL AS tr_amount,
NULL AS ptr_amount,
multi_transactions.from_amount AS mt_from_amount,
multi_transactions.to_amount AS mt_to_amount,
CASE
WHEN tr_status.min_status = 1
AND COALESCE(pending_status.count, 0) = 0 THEN CASE
WHEN multi_transactions.timestamp > 0
AND filter_conditions.nowTimestamp >= multi_transactions.timestamp + (
CASE
WHEN multi_transactions.from_network_id in layer2_networks
OR multi_transactions.to_network_id in layer2_networks THEN layer2FinalisationDuration
ELSE layer1FinalisationDuration
END
) THEN statusFinalized
ELSE statusCompleted
END
WHEN tr_status.min_status = 0 THEN statusFailed
ELSE statusPending
END AS agg_status,
COALESCE(tr_status.count, 0) + COALESCE(pending_status.count, 0) AS agg_count,
NULL AS token_address,
NULL AS tmp_token_id,
NULL AS token_code,
multi_transactions.from_asset AS from_token_code,
multi_transactions.to_asset AS to_token_code,
multi_transactions.from_network_id AS out_network_id,
multi_transactions.to_network_id AS in_network_id,
NULL AS type,
NULL as contract_address,
NULL AS method_hash,
NULL AS community_mint_event,
NULL AS community_id
FROM
multi_transactions
CROSS JOIN filter_conditions
LEFT JOIN tr_status ON multi_transactions.id = tr_status.multi_transaction_id
LEFT JOIN pending_status ON multi_transactions.id = pending_status.multi_transaction_id
WHERE
(
(
startFilterDisabled
OR multi_transactions.timestamp >= startTimestamp
)
AND (
endFilterDisabled
OR multi_transactions.timestamp <= endTimestamp
)
)
AND includeAllCollectibles
AND (
filterActivityTypeAll
OR (multi_transactions.type IN (%s))
)
AND (
filterAllAddresses
OR (
-- Send multi-transaction types are exclusively for outbound transfers. The receiving end will have a corresponding entry as "owner_address" in the transfers table.
mt_type = mTTypeSend
AND owner_address IN filter_addresses
)
OR (
mt_type != mTTypeSend
AND (
multi_transactions.from_address IN filter_addresses
OR multi_transactions.to_address IN filter_addresses
)
)
)
AND (
filterAllToAddresses
OR (multi_transactions.to_address IN filter_to_addresses)
)
AND (
includeAllTokenTypeAssets
OR (
multi_transactions.from_asset != ''
AND (
UPPER(multi_transactions.from_asset) IN assets_token_codes
)
)
OR (
multi_transactions.to_asset != ''
AND (
UPPER(multi_transactions.to_asset) IN assets_token_codes
)
)
)
AND (
filterAllActivityStatus
OR (
filterStatusCompleted
AND agg_status = statusCompleted
)
OR (
filterStatusFinalized
AND agg_status = statusFinalized
)
OR (
filterStatusFailed
AND agg_status = statusFailed
)
OR (
filterStatusPending
AND agg_status = statusPending
)
)
AND (
includeAllNetworks
OR (
multi_transactions.from_network_id IN filter_networks
)
OR (
multi_transactions.to_network_id IN filter_networks
)
OR (
COALESCE(multi_transactions.from_network_id, 0) = 0
AND COALESCE(multi_transactions.to_network_id, 0) = 0
AND (
EXISTS (
SELECT
1
FROM
tr_network_ids
WHERE
multi_transactions.id = tr_network_ids.multi_transaction_id
)
OR EXISTS (
SELECT
1
FROM
pending_network_ids
WHERE
multi_transactions.id = pending_network_ids.multi_transaction_id
)
)
)
)
ORDER BY
timestamp DESC
LIMIT
? OFFSET ?