bemulima
@bemulima
ФуллСтекРазработчик

Как реализовать фасетный поиск для связанных таблиц?

Допустим, у меня есть три таблицы, одна родительская и два потомка.
табл. (1)
 id | name
----+------
  1 | a
  2 | b
  3 | c
  4 | d

табл. (2)
branch_1
 id | parent_id | b1_name
----+-----------+---------
  1 |         1 | aaa
  2 |         1 | bbb
  3 |         1 | ccc
  4 |         2 | ddd
  5 |         2 | eee
  6 |         2 | fff
  7 |         2 | ggg
  8 |         2 | hhh

табл. (3)
branch_2
 id | b2_name | parent_id
----+---------+-----------
  1 | b2_aa   |         1
  2 | b2_ba   |         2
  3 | b2_bb   |         2
  4 | b2_bc   |         2

Пытаюсь их индексировать вот с таким запросом в sphinx
select p.id, b1.b1_name, b2.b2_name
from parent p
left outer join lateral (select distinct b1.b1_name from branch_1 b1 where b1.parent_id=p.id order by b1.b1_name) as b1 on true
left outer join lateral (select distinct b2.b2_name from branch_2 b2 where b2.parent_id=p.id order by b2.b2_name) as b2 on true

получаю вот такую таблицу
табл. (4)
id | b1_name | b2_name
----+---------+---------
  1 | aaa     | b2_aa
  1 | bbb     | b2_aa
  1 | ccc     | b2_aa
  2 | ddd     | b2_ba
  2 | ddd     | b2_bb
  2 | ddd     | b2_bc
  2 | eee     | b2_ba
  2 | eee     | b2_bb
  2 | eee     | b2_bc
  2 | fff     | b2_ba
  2 | fff     | b2_bb
  2 | fff     | b2_bc
  2 | ggg     | b2_ba
  2 | ggg     | b2_bb
  2 | ggg     | b2_bc
  2 | hhh     | b2_ba
  2 | hhh     | b2_bb
  2 | hhh     | b2_bc

И теперь при фасетном поиске:
select * from facetIndex facet b1_name, b2_name
sphinx возвращает неверное количество значений, а всё потому, что значения повторяются.

Идеально было бы, если индексированная таблица выглядела так:
табл. (5)
 id | b1_name | b2_name
----+---------+---------
  1 | aaa     | b2_aa
  1 | bbb     | ---
  1 | ccc     | ---
  2 | ddd     | b2_ba
  2 | eee     | b2_bb
  2 | fff     | b2_bc
  2 | ggg     | ---
  2 | hhh     | ---

Тогда бы фасетный поиск возвращал верные количества значений.
Вопрос:
как для индексированной таблицы (табл. 4) написать правильный запрос для фасетного поиска
или
как перестроить таблицы (таб. 1, таб. 2, таб. 3) в виде таб. (5)?
  • Вопрос задан
  • 124 просмотра
Пригласить эксперта
Ответы на вопрос 2
bemulima
@bemulima Автор вопроса
ФуллСтекРазработчик
Перестроить таблиц (таб. 1, таб. 2, таб. 3) в виде таб. (5). Может кому то пригодиться.

select t.id, t.b1_name, b2.b2_name
from (
  select p.id, b1.b1_name, row_number() over (partition by p.id order by b1_name) as rn
  from parent p
    join branch_1 b1 on b1.parent_id = p.id
) t
  left join (
    select parent_id, b2_name, 
           row_number() over (partition by parent_id) as rn
    from branch_2 
  ) b2 on b2.parent_id = t.id and b2.rn = t.rn
order by t.id;
Ответ написан
xmoonlight
@xmoonlight
https://sitecoder.blogspot.com
Меня не перестают удивлять такие люди, как Вы.
"Мыши кололись и плакали, но продолжали есть кактус" :))

Вы знаете как сделать правильно с нуля, но продолжаете использовать Sphinx!
Почему?!
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы