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

Question

[Марат Долотов]

Допустим, у меня есть три таблицы, одна родительская и два потомка.

табл. (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)?

Answer 1

[Марат Долотов]

Перестроить таблиц (таб. 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;

Answer 2

[xmoonlight]

Меня не перестают удивлять такие люди, как Вы.
"Мыши кололись и плакали, но продолжали есть кактус" :))

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

Comments

[Марат Долотов]

Если вы не знаете ответа то зачем вы написали. Разве, что остроумничать? Такие люди, как вы тоже не перестают удивлять меня.

Язык болтает, а голова не знает
Знай больше, а говори меньше

[xmoonlight]

Марат Долотов, я знаю ответ по реализации фасетного поиска не только средствами сфинкса.
Но Вы - проигнорировали помощь.

[Марат Долотов]

xmoonlight, принять помощь меня подобные люди, которые Вас не перестают удивлять всегда готовы но не таким образом. Но к счастью Вы не единственный, кто знает выполнить фасетный поиск. Обойдемся и без Вашей помощью.

[xmoonlight]

Марат Долотов, Итог: я прав.
(Вы сами и ответили на свой же вопрос)