Could you help with a database query to compare Nakama users with phone contacts?

Runtime Framework
, ,
1

Hello,
We have phonebook/Contact list in json. We wanted to compare the mobile numbers and email address from this contact list to database in a single go. We are trying to pass array in sql query and not sure how to pass them as we are new to lua.

Please find attached lua file we have created.
search_user_with_mobile.lua (878 Bytes)

Feedback is appreciated.

PS -
User maybe have 1000 contacts in his phonebook, do we able to compare those 1000 contacts in a single go by putting all numbers in array?
Or we have to batch them lets say in 50 contacts and run 20 call in loop?

Thanks

2

@YoursTruly I took a look at your code and have a couple of notes.

nk.sql_exec([[
CREATE UNIQUE INDEX IF NOT EXISTS users_location_phone_number_idx ON users (location)
]], params)

You should only run this code in a run_once function so its executed just at server startup.

As for the custom SQL query you’ve written you’ll need to pass a SQL array literal into the query as the placeholder parameter and cast it to the array type. You must make sure that you validate your inputs to the query so they are SQL safe.

    local query = [[
SELECT
    username, display_name, id, avatar_url, create_time
FROM
    users
WHERE
    location = ANY($1::TEXT[]) OR email = ANY($2::TEXT[])
LIMIT
    50
    ]]
    local parameters = { "{a,b}", "{c,d}" }
    local rows = nk.sql_query(query, parameters)
    for i, row in ipairs(rows)
    do
        print(string.format("%q: %s", i, nk.json_encode(row)))
    end

The usual recommendations also apply. Please minimize your use of custom SQL queries because they will inhibit the performance of the game server. Use with care.

PS -
User maybe have 1000 contacts in his phonebook, do we able to compare those 1000 contacts in a single go by putting all numbers in array?
Or we have to batch them lets say in 50 contacts and run 20 call in loop?

Your query looks up rows against both the "location" column and the "email" column. This would normally be very slow but you’ve introduced a unique index to the location column so it should be fast enough to query against 1000 contacts in a single SQL query. Nevertheless I would advise you to load a large amount of test data into your database and use EXPLAIN statements to analyse the query performance just in case.

1 Like