Search code examples
nosqltarantool

Select from tarantool using multiple conditions

How do i make a select with conditions with two or more fields from one space at once? I didn't find an example in the documentation.

Solution

  • There are 2 ways to do this: using SQL or using a lower-level lua API.

    The first one requires you to set space format (see here). It looks like this:

    box.space.myusers:format({{name='id',type='number'},
                          {name='first_name',type='string'},
                          {name='last_name',type='string'}})

    This is required for SQL to figure out column names. Then you can query it as follows:

    box.execute([[SELECT "id" FROM "myusers" WHERE "first_name"='John' AND "last_name"='Doe';]])

    Another way to select from the same space is:

    user_ids = {}
for_,user in box.space.myusers.index.first_name:pairs("John") do
    if user.last_name == "Doe" then
        table.insert(user_ids, user.id)
    end
end

    Look here for more details on the low-level space API.