4

Can I query a table with an indexed string column a la SQL?

Is something like this possible for example:

SELECT * from <table_name> where <my_string_column> LIKE '%test%';
  • I see the get and find actions in the DB documentation, both working on primary key, but I don't see anything like the example above. – onions May 14 '18 at 18:31
  • A use case would be searching for a user by name in a social network. – onions May 14 '18 at 19:34
1

You would need to use an iterator and go row by row using custom code to do the check. Something like this:

string test = "test";
for (auto itr = idx.begin(); itr != idx.end(); ++itr) {
    if (itr->username.find(test) != std::string::npos) {
        // Do something with that user and then exit the loop
        break;
    }
}

Since the database tables are stored on RAM, this should run pretty quickly, but if there are too many rows you could end up hitting the transaction time limit, so I would recommend filtering first using the index to reduce the amount of rows that you would iterate through, like limiting it to only users of a certain type, or something like that. You can use lower_bound for that, for example (documentation).

Besides that, EOSIO allows separating the stored data in different scopes, so you might want to have users of one particular type or country or some other property stored under separate scopes, and that way each table would be smaller as well.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.