address_transactions: order and retrieve by date (desc)

[behas]

Currently, address_transactions is ordered by address id. However, on the UI it would be desirable to see the latest transactions first and, if the number of transactions is large, to somehow retrieve them by date (can also be by month, year, whatever).

[soad003]

Had a look at the problem I think both of the things can be achived by restructuring how we query the table.

In e.g. the REST interface we often query the table like this:

select * from  address_transactions where address_id_group=264 and address_id=2647117;

Which results in random order. The tables clustering order is defined as follows:

...
    PRIMARY KEY (address_id_group, address_id, is_outgoing, tx_id)
) WITH CLUSTERING ORDER BY (address_id DESC, is_outgoing DESC, tx_id DESC)

Tx_id desc gives us temporal order but only if we query the right way (background), only if is_outgoing included in the filter we get ordered results (desc by tx_id).

select * from  address_transactions where address_id_group=264 and address_id=2647117 and is_outgoing=true;
select * from  address_transactions where address_id_group=264 and address_id=2647117 and is_outgoing=false;

So to receive ordered results we need to split the queries into two and add some logic in the client (REST etc.) to combine the result-sets (merge).

By including is_outgoing in the where clause we can also support range queries and switching up the order e.g.

select * from  address_transactions 
where address_id_group=264 and 
             address_id=2647117 and 
             is_outgoing=true and 
             tx_id > 1000
             order by tx_id ASC;

Using some additional lookups this could be even done by block number or date:

• Get the block number for the start timestamp (raw block)
• Get the first tx_id in that block (raw block_transactions)
• Get the block number for the end timestamp (raw block)
• Get the last tx_id in that block (raw block_transactions)

Getting the blocknumber by date is currently not efficient but could be solved by a materialized view or binary search.

[soad003]

@myrho this is already implemented even in the rest interface right?