Есть таблицы vacancy, prof_area, company и company_address - у company может быть множество вакансий и адресов т.е. эта таблица связана один ко многим с таблицами vacancy и company_address. И таким образом у вакансии может быть несколько адресов. В company_address есть два поле координат с типом float latitude и longitude. Так же у вакансии может быть несколько профессиональных областей соответственно таблица vacancy связана с таблицей prof_area один ко многим.
Задача индексировать все вакансии в sphinx вместе с профессиональными областями, адресами и координат. Чтоб по координат можно было реализовать поиск по радиусу с помощью метода GEODIST так же на форме фильтра отображать данные FACET для всех полей. Так, как у вакансии множество профессиональных областей и адресов (MVA) изначально планировал использовать sql_attr_multi. С полем prof_area_ids всё обошлось отлично НО возникла проблема с координатами, sql_attr_multi - не поддерживает данные с типом float. По этому я решил индексировать все данные просто через JOIN и получилось вот такие данные в индексе:
mysql> select id,vacancy_id,latitude,longitude,prof_area_ids from vacancyIndex;
+------+------------+----------+-----------+---------------+
| id | vacancy_id | latitude | longitude | prof_area_ids |
+------+------------+----------+-----------+---------------+
| 1 | 917 | 0.973178 | 0.743566 | 11,199,202 |
| 2 | 916 | 0.973178 | 0.743566 | 17,283,288 |
| 3 | 915 | 0.973178 | 0.743566 | 17,288 |
| 4 | 914 | 0.973178 | 0.743566 | 30,482 |
| 5 | 919 | 0.825153 | 0.692837 | 15,243 |
| 6 | 919 | 0.825162 | 0.692828 | 15,243 |
| 7 | 918 | 0.825153 | 0.692837 | 8,154 |
| 8 | 918 | 0.825162 | 0.692828 | 8,154 |
| 9 | 920 | 0.958914 | 1.282161 | 17,283,288 |
| 10 | 920 | 0.958915 | 1.282215 | 17,283,288 |
| 11 | 924 | 0.97333 | 0.658246 | 12,208 |
| 12 | 924 | 0.973336 | 0.658237 | 12,208 |
| 13 | 923 | 0.97333 | 0.658246 | 21,365 |
| 14 | 923 | 0.973336 | 0.658237 | 21,365 |
| 15 | 922 | 0.97333 | 0.658246 | 20,359 |
| 16 | 922 | 0.973336 | 0.658237 | 20,359 |
| 17 | 921 | 0.97333 | 0.658246 | 19,346 |
| 18 | 921 | 0.973336 | 0.658237 | 19,346 |
| 19 | 926 | 0.88396 | 2.389868 | 12,17,208,292 |
| 20 | 925 | 0.88396 | 2.389868 | 12,208 |
+------+------------+----------+-----------+---------------+
20 rows in set (0.00 sec)
Как видно, что некоторые вакансии повторяются по несколько раз, отличаются только координатами и id (ID на самом деле не касается к вакансии так, как он сгенерирован “row_number() OVER () AS id” , у вакансии отдельное поле vacancy_id).
Для поиска данных всё хорошо работает, если мне надо выбрать все вакансии в профессиональной области 199, я просто группирую данные по vacancy_id:
mysql> select id,vacancy_id,latitude,longitude,prof_area_ids from jobVacancy where prof_area_ids=199 group by vacancy_id;
+------+------------+----------+-----------+-----------------+
| id | vacancy_id | latitude | longitude | prof_area_ids |
+------+------------+----------+-----------+-----------------+
| 1 | 917 | 0.973178 | 0.743566 | 11,199,202 |
| 191 | 1004 | 0.925335 | 2.768874 | 11,196,199 |
| 313 | 1072 | 0.963968 | 1.070624 | 1,11,60,197,199 |
| 318 | 1136 | 0.96071 | 1.448998 | 11,196,199 |
| 374 | 1097 | 0.785255 | 0.678504 | 11,199 |
+------+------------+----------+-----------+-----------------+
5 rows in set, 1 warning (0.01 sec)
Всё отлично! НО проблема возникает, когда я в конец добавляю FACET:
mysql> select id,vacancy_id,latitude,longitude,prof_area_ids from jobVacancy where prof_area_ids=199 group by vacancy_id facet prof_area_ids;
+------+------------+----------+-----------+-----------------+
| id | vacancy_id | latitude | longitude | prof_area_ids |
+------+------------+----------+-----------+-----------------+
| 1 | 917 | 0.973178 | 0.743566 | 11,199,202 |
| 191 | 1004 | 0.925335 | 2.768874 | 11,196,199 |
| 313 | 1072 | 0.963968 | 1.070624 | 1,11,60,197,199 |
| 318 | 1136 | 0.96071 | 1.448998 | 11,196,199 |
| 374 | 1097 | 0.785255 | 0.678504 | 11,199 |
+------+------------+----------+-----------+-----------------+
5 rows in set (0.00 sec)
+---------------+----------+
| prof_area_ids | count(*) |
+---------------+----------+
| 202 | 1 |
| 199 | 12 |
| 11 | 12 |
| 196 | 5 |
| 197 | 3 |
| 60 | 3 |
| 1 | 3 |
+---------------+----------+
7 rows in set (0.02 sec)
Как видно в результатах поиска вакансий где prof_area_ids=199 всего 5 данных а в FACET 12 показывает. То есть если бы я отобразил фасетные данные в форме фильтра клиент видит, что профессиональная область с идентификатором 199 в базе 12, он выбирает и по факту система ему показывает всего 5.
Можно ли с помощью SPHINX добиться, чтоб фасетные данные верно отображались? Может я не верно проиндексировал? Вообщем подскажите, как на самом деле решается такие вопросы. Думаю я не единственный у кого такие вопросы возникли.