How to use two secondary keys as a filter? (WHERE f1 AND f2)

Question

In the contract multi index tables we can filter by the primary key or we can just create an indexed_by as an additional index.

Then we have basically two options: filter by the primary key or filter by the additional index.

But what if we want to add a second additional index and find for both of them?

Like in a SQL fashion: index1 = 1 AND index2 = 2

I've already asked this question and received the below answer from Dan Larimer, in Official EOS Developers Telegram, but I'm sharing with you because I believe it can help others

Answer 1

So, for this scenario we can create another index that concatenates both indexes and look for it. As an example, let's see a scenario where we can invite accounts to a group and each invitation has an id.

// table declaration for invites 
// @abi table invitation i64
struct invitation {
    uint64_t     id;
    uint64_t     group;
    account_name user;

    uint64_t primary_key() const { return id; }

    uint128_t get_user_by_group() const { return combine_ids(group, user); }

    EOSLIB_SERIALIZE(invitation, (id)(group)(user))
};
typedef multi_index<N(invitation), invitation, 
  indexed_by<N(user_by_group), const_mem_fun<invitation, uint128_t, 
                              &invitation::get_user_by_group>>
> tb_invitation;

// concatenation of ids example
uint128_t combine_ids(const uint64_t &x, const uint64_t &y) {
    return (uint128_t{x} << 64) | y;
}


// now in your smartcontract, you can look if the user already
// belongs to the specific group
// validates if user is already in the community

tb_invitation invitation(_self, _self);
auto idx_existent_invitation = invitation.template get_index<N(user_by_group)>();
auto group_user_id = combine_ids(group_id, new_user_id);
auto itr_group_user = idx_existent_invitation.find(group_user_id);
eosio_assert(itr_group_user == idx_existent_invitation.end(), 
    "user already invited to group");

Besides that, you can also do Dan's suggestion, using two iterators and merging them. This solution works for simple AND clauses only. Dan suggestion's would probably be better for OR and any other complex filters.

Also, it was my own implementation without Dan eyes, if you can, please suggest any improvements and better approaches on this concatenation! ;)