Sphinx Indexing User Data

Question

So I have a bunch of these source configs in a sphinx.conf file and they index and work fine.

source company
{
    type            = mysql
    sql_host        = mysql
    sql_user        = root
    sql_pass        = root
    sql_db          = database_name
    sql_port        = 3306
    sql_query       = SELECT id, name FROM company
}
index company
{
    source          = company
    path            = /var/lib/sphinxsearch/data/company
    min_prefix_len  = 2
    morphology      = stem_en
}

I'm trying to design one that will index the set of all User's friend names. For example:

users (id, name):
1 Tom
2 Dick
3 Harry
4 Jane

friends (id, user_a, user_b):
1 1 2
2 1 3
3 2 3
4 4 3

QUESTION

How would you go about writing the query to index: 1. Tom's friends: [Dick, Harry] 2. Dick's friends: [Tom, Harry] 3. Harry's friends: [Tom, Dick, Jane] 4. Jane's friends: [Harry]

So that when Harry starts typing to search his friends names he can only find his friends names in the list, and the same for all other Users (should only be able to find their own friends names). Thanks.

Answer 1

Assuming you have this in MySQL:

mysql> select * from users; select * from friends;
+------+-------+
| id   | name  |
+------+-------+
|    1 | Tom   |
|    2 | Dick  |
|    3 | Harry |
|    4 | Jane  |
+------+-------+
4 rows in set (0.00 sec)

+------+--------+--------+
| id   | user_a | user_b |
+------+--------+--------+
|    1 |      1 |      2 |
|    2 |      1 |      3 |
|    3 |      2 |      3 |
|    4 |      4 |      3 |
+------+--------+--------+
4 rows in set (0.00 sec)

You can add the following to your source:

sql_query_pre   = set @id=0;
sql_query       = select (@id:=@id+1) id, u.name user, (if(u.id=user_a,u3.name,if(u.id=user_b,u2.name,''))) friend from users u left join friends f on f.user_a = u.id or f.user_b = u.id left join users u2 on f.user_a = u2.id left join users u3 on f.user_b = u3.id
sql_field_string = friend

which will give you this:

mysql> set @id=0;select (@id:=@id+1) id, u.name user, (if(u.id=user_a,u3.name,if(u.id=user_b,u2.name,''))) friend from users u left join friends f on f.user_a = u.id or f.user_b = u.id left join users u2 on f.user_a = u2.id left join users u3 on f.user_b = u3.id;
Query OK, 0 rows affected (0.00 sec)

+------+-------+--------+
| id   | user  | friend |
+------+-------+--------+
|    1 | Tom   | Dick   |
|    2 | Tom   | Harry  |
|    3 | Dick  | Tom    |
|    4 | Dick  | Harry  |
|    5 | Harry | Tom    |
|    6 | Harry | Dick   |
|    7 | Harry | Jane   |
|    8 | Jane  | Harry  |
+------+-------+--------+
8 rows in set (0.00 sec)

Here's how it would work in Sphinx then:

mysql> select friend from company where match('@user tom @friend di*');
+--------+
| friend |
+--------+
| Dick   |
+--------+
1 row in set (0.00 sec)

mysql> select friend from company where match('@user tom @friend ha*');
+--------+
| friend |
+--------+
| Harry  |
+--------+
1 row in set (0.00 sec)

mysql> select friend from company where match('@user tom @friend ja*');
Empty set (0.00 sec)

mysql> select friend from company where match('@user harry @friend ja*');
+--------+
| friend |
+--------+
| Jane   |
+--------+
1 row in set (0.00 sec)

You may be also interested in CALL SUGGEST / CALL QSUGGEST. Here's an interactive course on that - https://play.manticoresearch.com/simpleautocomplete/