Как искать несколько совпадений с группировкой по какому-то полю?

Question

[d1mvla5off]

Аналог на sql:

select
    distinct
    group_id,
    text
from index_1 as i1
where text like '%search_text_1%'
    and exists (select 1 from index_1 as i2 where i2.group_id = i1.group_id and i2.text like
    '%search_text_2%')

Попытка на Сфинксе:

select
    group_id,
    text
from index_1
where match('@text %search_text_1%')
...?
group by group_id

Подскажите пожалуйста

Answer 1

[Сергей Тихонов]

Запрос выглядит как "найти группу, для которой в одном документе есть один текст, а в другом (или том же) - другой". Сдается мне, что надо делать индекс, в котором документ - это группа, и искать просто оба совпадения сразу. Почему так:

• Если искать по "И", то найдутся только документы группы, в которых есть и тот и другой текст (т.е. ситуация "в другом документе второй текст" не находится)
  
• Если искать по "ИЛИ" а потом отфильтровывать результаты по HAVING COUNT(*) > 1, то попадают лишние результаты (2 документа с первым текстом)
  

Comments

[d1mvla5off]

Если искать по "И", то найдутся только документы группы, в которых есть и тот и другой текст

1. Да, именно такое условие должно быть. Можно ведь сделать такой запрос?
Например сейчас в индексе:
id|group_id|text
1|1|москва
2|2|орел
3|1|уфа
4|2|уфа
Нужно чтобы вывел группы в которых есть одновременно и Москва и Уфа, т.е. группу 1. Можно без документ ид.

2. А индекс с группмровкой дольше будет строиться? И что делать с ограничением по конкатенации, ведь у одной группы может быть очень много записей

[Сергей Тихонов]

1. Неверно поняли. "И тот и другой текст" есть в одном и том же документа, из примера - text="москва уфа". Думаю, это все же не то что надо.

2. Индекс будет строиться примерно столько же, а искаться быстрее: документов-то меньше. С ограничением по конкатенации можно бороться через sql_joined_field (если у вас вторая версия сфинкса) либо средствами субд (всякими там переменными, настраивающими этот аспект).

[d1mvla5off]

Сергей Тихонов, ок. А вот еще одна задача, есть еще доп поле text_type_id и нужно фильтровать еще и по нему:
id|group_id|text|text_type_id
1|1|москва|5
2|2|орел|5
3|1|уфа|5
4|2|уфа|5
5|1|синий|6
6|1|красный|6
Нужно вывести групп_ид, где (text=Москва и text_type_id=5) и (text=Уфа и text_type_id=5) и (text=Синий и text_type_id=6), и - читать как У ЭТОЙЖЕ ГРУПП_ИД СУЩЕСТВУЕТ
Есть вариант как такое сделать?

[Сергей Тихонов]

А число типов текстов неизменяемое? Если да, то проще по отдельным текстовым полям распихать.
Плюс возможно стоит почитать про FACET, но я с фасетным поиском не работал, поэтому тут не помогу.

[d1mvla5off]

Сергей Тихонов, число типов изменяемое, сегодня их 100, завтра +10, столбцы заводить для каждого - тупик.
Фасеты смотрел, но там нет возможности поиска полнотекстового, собсно того что и нужно. А какие есть варианты вообще? Неверится что такого вида поисковая задача нереализуема(

[Сергей Тихонов]

Можно text_type_id сделать тоже текстовым атрибутом, тогда:

SELECT group_id FROM index_1 WHERE MATCH('@text "aaa" & @text_type "5" | @text "bbb" & @text_type "6"') GROUP BY group_id

[d1mvla5off]

Сергей Тихонов, тогда в индексе придется делать группировку по груп_ид и text_type_id конкатенируя поле text и груп_ид делаем как документ ид, а
text_type_id как поисковое текстовое поле, а так можно будет группировать по нему тогда?
Это конечно кажется грязно будет, ибо индекс то 1 и настройки общие для него, надо будет учесть для text_type_id что слово это одна цифра может быть и тд. У вас есть предположение как это повлияет на скорость индексации и размер файлов индекса? Ибо данных в сотнях ГБ(

[Сергей Тихонов]

d1mvla5off, из всех предложенных вещей group by хуже всего влияет. Ну и с учетом слабой селективности text_type_id небольшая просадка при фильтрации по этому полю тоже добавится.

Насчет первого вопроса, я не совсем понял, зачем группировать по text_type_id и конкатенировать group_id и text для docid? Это вообще-то невозможно, т.к. docid - это целое число.

[d1mvla5off]

Сергей Тихонов, имел ввиду в запросе для создания индекса будет что-то вроде:
Исходные данные

id|group_id|text|text_type_id
1|1|москва|5
2|2|орел|5
3|1|уфа|5
4|2|уфа|5
5|1|синий|6
6|1|красный|6

запрос

select
 group_id,
 group_concat(text SEPARATOR ",") as text,
 group_concat(distinct text_type_id SEPARATOR ",") as text_type_id
from index_1
Group by
 group_id,
 text_type_id

Где, text и text_type_id это sql_field_string чтобы можно было использовать MATCH().

Полученые проиндексированные данные

group_id|text|text_type_id
1|москва,уфа,синий,красный|5,6
2|орел,уфа|5

А как теперь выполнить такой запрос?

Нужно вывести групп_ид, где (text=Москва и text_type_id=5) и (text=Уфа и text_type_id=5) и (text=Синий и text_type_id=6), и - читать как У ЭТОЙЖЕ ГРУПП_ИД СУЩЕСТВУЕТ

Тут же мы не сможем проверять соответствие типа текста и самого текста, ведь все в куче

[d1mvla5off]

Описка в запросе лишнее group by text_type_id