One-to-Many Relationships in EOS Tables
NOTE: Full code examples can be found by selecting the blue title of each section
I come from a background in Graph Databases, a wonderful land where data is directly linked too it’s related data, like MySQL joins but on steroids. So if you’re like me and mastered the basics of EOS tables, then you’ve probably asked the question, but how do we reference multiple rows from one table in another? Well in this post we’ll be looking into not one, but multiple methods for creating one-to-many relationships with multi index tables.
Vectors
C++ Vectors represent the structure of a dynamic array, allowing us to store an arbitrarily sized collection of values.
In the following example we will use a vector to represent an array of unique Item identifiers. The identifiers stored in the Profile table will serve as a reference to the primary_key of rows in our Item table.
struct Profile {
name account;
vector<uint64_t> items;
}
struct Item {
uint64_t id;
string name;
}
Now inside our void example_table_vectors::create(... ) method, we’re going to push a reference to the Item using the Item.id we stored prior.
owners.modify(currentPlayer, 0, [&](auto& owner) {
owner.items.push_back(itemID);
});
The push_back() function appends our value to the end of the items vector.
We can even go a step further and define a sub-collection within our item vector
vector<uint64_t, uint32_t> items;
where uint64_t is our item identifier and uint32_t is the item’s age. Alternatively a secondary stuct (collection) can be stored inside the vector like vector<Item> items;.
Vectors are a great method when we want to store related data directly within our table, however table rows could quickly grow excessively large from user abuse or improper data management. Let’s explore the use of Secondary Indexes instead to create relationships between tables.
Indexes
Using secondary indexes is an alternative way we can scale the number of references but avoid excessively large arrays (vectors) filling our table rows. It also gives us a method to backwards reference the relationship, which can be combined with previous relationship methods, allowing us to move seamlessly between children and parents of table rows. For this we will use what we learned in the previous post, Secondary Table Indexes. First we’re going to modify our structures from the previous method, removing our items vector from the Profile struct and adding a reference to our owner within the Item struct.
struct Profile {
name account;
}
struct Item {
uint64_t uid;
string name;
name owner;
}
To search Items by owner we will need to create a secondary index like indexed_by<N(byowner), const_mem_fun<Item, uint64_t, &Item::get_owner> and add it too our multi_index definition. EOS allows us to define up too 16 additional indexes for each table.
struct Item {
...
uint64_t get_owner() const { return owner; }
}
typedef multi_index<N(items), Item, indexed_by<N(byowner), const_mem_fun<Item, uint64_t, &Item::get_owner>> item_table;
Where the name of our index will be byowner and it will return the uint64_t owner key using the get_owner function.
We will no longer be updating our Player from within our add item function. Instead we will be storing a refefrence to the signee account in the created Item's owner property like so;
items.emplace(account, [&](auto& item) {
...
item.owner = account;
});
- We will not verify the player is registered here for the purpose of simplicity. However, you should obviously do all your validation prior to creating and assigning an item.
Now we can get all our items for a player like so;
void indexes::get(const name account) {
item_table items(_self, _self);
auto accounts_items = items.get_index<N(byowner)>();
auto iter = accounts_items.lower_bound(account);
// while (iter != accounts_items.end(); iter++) { // Do stuff }
}
Scopes
For this final method we’re going to use the table scope to represent relationships between our tables. Using the scope adds a sort of protected security by storing data within the specified EOS account (scope).
The EOS data structure
– code — The account name assigned write permission (contract)
—- scope — The account where the data is stored
—–- table — Name of the table being stored
—–—- record — A table row
We’re going to modify our player’s Item's to be stored within the scope of our player’s account, the use the account scope to look up all the player’s items. To start we can remove the owner reference in get_owner() index for our multi_index Item table. You could, and probably should keep this in most cases, but we are being simplistic in these examples and cutting straight to the point.
// The cleaned Item table
struct Item {
uint64_t id;
string name;
auto primary_key() const { return id; };
EOSLIB_SERIALIZE(Item, (id)(name));
};
// And our multi_index definition
typedef multi_index<N(items), Item> item_table;
The real magic happens over in our setters and getters, let’s take a look at how they’ve changed.
item_table items(_self, account);
auto item = items.emplace(account, [&](auto& item) {
... setup
});
If you look closely, we are now instantiating our table within the scope of our account, rather than the contract code itself. Our get function has been modified in the same way.
item_table items(_self, account);
auto iter = playerItems.lower_bound();
while (iter != playerItems.end()) { ... }
This method makes accessing all items from all accounts extremely difficult, however it’s allowing users to own their data within the contract.
It’s important to realise this is not private storage, it’s just protected by the difficulty of guessing a users public account address along with the contract address (code) and table name.