Select from Tarantool by composite index with different iterators

Question

I have space peoples:

1. id
2. name
3. age

with two indexes:

peoples = box.schema.space.create('peoples', {engine = 'memtx', field_count = 3, if_not_exists = true})
peoples:create_index('primary', {type = 'TREE', unique = true, parts = {1, 'unsigned'}, if_not_exists = true})
peoples:create_index('by_name_age', {type = 'TREE', unique = false, parts = {2, 'STR', 3, 'unsigned'}, if_not_exists = true})

and with data:

peoples:auto_increment{ 'name_1', 10 }
peoples:auto_increment{ 'name_1', 11 }
peoples:auto_increment{ 'name_1', 12 }

I need select peoples by name and by age more than some value. For example, I want to select all 'Alex' with age > 10, waiting for the next result:

[1, 'Alex', 11]
[2, 'Alex', 12]

I try to execute the query:

peoples.index.by_name_age:select({'Alex', 10}, {{iterator = box.index.EQ},{iterator = box.index.GT}})

but get the result

[1, 'Alex', 10]

How can I use different iterators for name and age index parts?

Answer 1

You have an index and its sorted by the first part. Means it can't do the selection as you are expecting.

What you have to do to see the expected behavior? You have to use the for loop[1] with GE iterator, and you have to test ranges using the if condition inside this loop.

[1] A simple code example:

local test_val = 'Alex'
for _, v in peoples.index.by_name_age:select({test_val, 10}, {iterator = box.index.GE})
   if v[1] ~= test_val then
   -- do exit
   end
   -- do store result 
end

Answer 2

peoples.index.by_name_age:pairs({'Alex', 10}, 'GE'):
    take_while(function(x) return x.name == 'Alex' end):
    totable()

Here it starts iterating over all records with name >= 'Alex' and age >= 10. take_while is used to limit records only to matching name == 'Alex'.

:totable() is optional, without it this expression can be used in for-loop similar to pairs.