CODEWITHSUNDEEP
nosqltarantool
Denis Khabibullin
Denis Khabibullin

Reputation: 33

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.

Upvotes: 1

Views: 788

Answers (2)

Oleg Babin
Oleg Babin

Reputation: 429

Or instead of write additional conditions under "if" you could write custom "filter" function. And use it in the following way:

E.g you have following schema:

space = box.schema.space.create('test')
space:create_index('primary')
space:replace{1, 'Odd'}
space:replace{2, 'Even'}
space:replace{3, 'Odd'}

-- Print
-- [1, 'Odd']
-- [2, 'Even']
-- [3, 'Odd']
--
for _, tuple in space:pairs() do
    print(tuple)
end

-- If you want to select tuples with second "Odd" field
-- define
function is_odd(tuple)
    return tuple[2] == 'Odd' -- could be more complex condition
end

-- And then
-- it will print
-- [1, 'Odd']
-- [3, 'Odd']
--
for _, tuple in space:pairs():filter(is_odd) do
    print(tuple)
end

Upvotes: 1

user13626536
user13626536

Reputation:

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.

Upvotes: 4

Related Questions