Итак, имеется денормализованная таблица (упрощенный вид):
CREATE TABLE public.objects (
id int4 NOT NULL DEFAULT nextval('objects_id_seq'::regclass),
object_id int8 NOT NULL,
title varchar(8192) NOT NULL,
title_lang varchar(8192) NULL
)
INSERT INTO public.objects
(object_id, title, title_lang)
VALUES
(12345, 'TitleRU', 'ru'),
(12345, 'TitleEN', 'EN'),
(12345, 'TitleFR', 'fr'),
(54321, 'DE Title', 'de'),
(54321, 'EN Title', 'en')
Для каждого object_id может существовать несколько записей в таблице, по одной для пары title, title_lang.
Т.е. есть объект, у него есть титель, титель может быть на нескольких языках.
На входе имеем запрошенный язык.
Так же есть дефолтный язык.
Задача:
Для каждого объекта выбрать запись с запрошенным языком.
Если у объекта запрошенного языка нет, то выбрать запись с дефолтным языком.
Если и с дефолтным нет, то выбрать запись с первый встретившимся языком.
Так же отсортировать результат по тителю.
Дамп
select id, object_id, title, title_lang, * from objects where object_id in (12345, 54321)
Входящие параметры в запрос:
- запрошенный язык: ru
- дефолтный язык: de
select DISTINCT on (o.object_id) o.object_id,
case
when olreq.title notnull then olreq.title
when oldef.title notnull then oldef.title
else o.title
end
as title
from objects o
left join objects olreq on (o.object_id = olreq.object_id and olreq.title_lang = 'ru')
left join objects oldef on (o.object_id = oldef.object_id and oldef.title_lang = 'de')
where o.object_id in (12345, 54321)
Все хорошо, имеем результат
Но, невозможно отсортировать по полю title т.к. имеем агрегатную функцию, и если сортировать, то сначала нужно сортировать по агрегируемому полю
/* ошибка */
select DISTINCT on (o.object_id) o.object_id,
case
when olreq.title notnull then olreq.title
when oldef.title notnull then oldef.title
else o.title
end
as title
from objects o
left join objects olreq on (o.object_id = olreq.object_id and olreq.title_lang = 'ru')
left join objects oldef on (o.object_id = oldef.object_id and oldef.title_lang = 'de')
where o.object_id in (12345, 54321)
order by title
Если же дать ему то, что он хочет, и сначала сортировать по агрегированому полю, то теряется сортировка по требуемому полю title
select DISTINCT on (o.object_id) o.object_id,
case
when olreq.title notnull then olreq.title
when oldef.title notnull then oldef.title
else o.title
end
as title
from objects o
left join objects olreq on (o.object_id = olreq.object_id and olreq.title_lang = 'ru')
left join objects oldef on (o.object_id = oldef.object_id and oldef.title_lang = 'de')
where o.object_id in (12345, 54321)
order by o.object_id, title
Есть идеи? :)
P.S. оборачивать запрос в другой запрос и сортировать уже результат запроса-врепера не подходит, т.к. помимо сортировки нужны еще LIMIT и OFFSET, и , если оборачивать другим SELECT * from ( SELECT .... ) то теряется весь смысл идеи, т.к. сильно просаживается производительность. А-ля прочитать сначала вообще всё, отсортировать, потом обрезать.
_____________
[продано копирайтерам]