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%';
EOS.IO Stack Exchange is a question and answer site for users and developers of distributed applications built on top of the blockchain operating system, EOS.IO. It only takes a minute to sign up.
Sign up to join this communityCan 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%';
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.
get
andfind
actions in the DB documentation, both working on primary key, but I don't see anything like the example above.