How do I lookup multiple entries by same secondary indices with more than limit entries?

Question

I have a table with a lot of entries. I want to iterate through all the rows by the same secondary index on multiple pages.

TABLE post {
    uint64_t   id;
    name       author;
    string     text;

    auto primary_key() const {
        return id;
    }
    uint64_t by_author() const {
        return author.value;
    }
};
typedef eosio::multi_index< "posts"_n, post ,
    eosio::indexed_by< "author"_n, eosio::const_mem_fun<post, uint64_t, &post::by_author> >
> posts;

I have a table with 150 posts that have author "bob" Now when I use get table I limit the query to 100 rows how do I get more if the lower_bound is the exact same at all times, with author="bob"? Is there an offset possible?

{
                "json":true,
                "code":"blog",
                "table":"posts",
                "scope":"blog",
                "table_key":"author",
                "lower_bound": bobAsNumericName,
                "upper_bound": bobAsNumericName+1,
                "index_position":2,
                "key_type":"i64",
                "limit":100,
                "reverse":false,
                "show_payer":false
 }

Oh and all rows are in the same scope.

basically I need to query for 100 blog posts from "bob" and then offset 100 entries and query for 100 more (In this case receive 50 entries and more=false)

Answer 1

Is there a reason for the rows to reside on the same scope? Because in that usecase it seems that separate scopes for each author would simplify... but if you need to list all posts, for instance, then the scope is not an option and maybe you need a uint128_t key compound key.

I would probably suggest the following:

TABLE post {
    uint64_t   id;
    name       author;
    string     text;

    auto primary_key() const {
        return id;
    }
    uint128_t by_author() const {
        return ((uint128_t)author.value << 64) + id;
    }
};
typedef eosio::multi_index< "posts"_n, post ,
    eosio::indexed_by< "author"_n, eosio::const_mem_fun<post, uint128_t, &post::by_author> >
> posts;

Please notice the change in the by_author() function and the index template row.

So in this case your lower_bound would be (uint128_t)bobAsNumericName << 64, the upper_bound would become ((uint128_t)bobAsNumericName + 1) << 64, don't forget that you need to specify these rather large numbers in little endian hexadecimal, see below...

Also you need to use key_type as i128.

So to specify these you can use this code, from CMichel great article:

function getTableBoundsForName(name, asLittleEndianHex = true) {
  const nameValue = nameToValue(name);
  const nameValueP1 = nameValue.add(1);

  if(!asLittleEndianHex) {
    return {
      lower_bound: nameValue.toString(),
      upper_bound: nameValueP1.toString()
    };
  }

  const lowerBound = bytesToHex(nameValue.toBytesLE());
  const upperBound = bytesToHex(nameValueP1.toBytesLE());

  return {
    lower_bound: lowerBound,
    upper_bound: upperBound,
  };
}

So this way you'll have different keys and you can specify the next_key on the lower_bound parameter for the next 100 rows...