How to index one to many tables on sphinx?

Question

Sphinx

I have a one-to-many parent and child table.

parent
- id
- name
- age

child
- id
- parent_id
- name
- age

if in the source config write:

source mainSourse
{
  sql_query = \
        SELECT row_number() OVER () AS id, p.id as parent_id, p."name" as parent_name, \
        p.age as parent_age, ch.id as child_id, ch.name as child_name, ch.age as child_age\
        FROM parent p\
        JOIN child ch ON ch.parent_id=p.id\

        sql_field_string = parent_name
        sql_field_string = child_name
        sql_attr_uint = parent_age
        sql_attr_uint = child_age
}

then if one parent has more than one children, then display them in sphinx like this:

| id | parent_id | parent_name | parent_age | child_id | child_name | child_age |
|  1 |              1|                    a |                 35|            1|                aa|               2|
|  2 |              1|                    a |                 35|            2|                ab|               3|
|  3 |              1|                    a |                 35|            3|                ac|               4|
|  4 |              1|                    a |                 35|            4|                ad|               5|

those. the parent's data is repeated, thereby taking up disk space. When searching by parent name, if the parent has more than one child, then the number of parents is equal to the number of children. And when searching by the name of the child is normal, one entry. Tell me how to index such data correctly or is it supposed to be so?

Answer 1

I propose to look from the side of adjusting the architecture for a particular case.
The parent and child tables can be combined into one - family, having a structure similar to the child table.
- id
- parent_id
- name
- age Let
's get something like:
--------------------------------
id | parent_id | name | age
--------------------------------
1 | 0 | Father | 30
2 | 1 | Daughter | 9
3 | 1 | Daughter2 | 7
4 | 0 | Father2 | 41
5 | 4 | Daughter3 | 12
Then we will get three fields in the index itself:
- name
- age
- parent_id We are
looking for among parents:
WHERE parent_id = 0 AND MATCH('@name "Father"')
Search among children: WHERE parent_id
> 0 AND MATCH('@name "Daughter"')
using the MySQL GROUP_CONCAT function using a delimiter to further access the array.
For example, when grouping by the parent_name field, you need to concatenate 3 fields - child_id, child_name, child_age:

SELECT \
        row_number() OVER () AS id, \
        p.id as parent_id, \
        p."name" as parent_name, \
        p.age as parent_age, \
        GROUP_CONCAT(ch.id SEPARATOR '|') as child_id, \
        GROUP_CONCAT(ch.name SEPARATOR '|') as child_name, \
        GROUP_CONCAT(ch.age SEPARATOR '|') as child_age\
FROM parent p\
JOIN child ch ON ch.parent_id=p.id\
GROUP BY parent_id

With SELECT * FROM index WHERE parent_id = 1 we get something like this:
-------------------------------------------------- -----------------------------------------
id | parent_id | parent_name | child_id | child_name | child_age
------------------------------------------------ ----------------------------
1 | 1 | Father | 1|2|3 | aa|ab|ac | 2|3|4
After the query is executed, the concatenated rows need to be processed for normal work with child_* fields.
In general, it is good to determine the method and the very need for any changes based on specific tasks.