Лет 6 назад, когда слон был только в 8.0, а я сильно застрял на MySql, я часто слышал призывы сменить БД.
Помню, как больно было начинать.
Но после того, как я принял решение, я ни разу об этом не пожалел и вряд ли вернусь в мускулы.
Плюсов здесь масса, но пост не об этом.
Появилась задача: написать магазин, который в будущем будет большим.
А-ля Фото, Горячая линия.
Ну и стандартная задача для таких сайтов – фильтр.
Как это делается в обычных двигателях? Правильно: параметры = фильтры.
Ну понятно, что это просто, но не всегда красиво, особенно когда нужны фильтры с диапазонами (например: диагональ 10"-11").
И даже тогда придется задуматься о том, что фильтры — это сущность, независимая от параметров.
В моей версии фильтры «привязаны» к параметрам.
Не знаю, как это делается на той же горячей линии (есть подозрение, что фильтры привязаны напрямую к товарам), но этот вариант требует большого человеческого внимания.
Я не буду вдаваться в подробности архитектуры; это тема для другого поста.
Но мы рассмотрим упрощенный вариант, чтобы не заблудиться.
Проблема таких фильтров заключается в их конструкции.
Подсчитайте количество товаров с выбранными (или еще не выбранными) фильтрами.
Так… Создайте таблицу для продуктов:
Таблица фильтров:CREATE TABLE public.products ( id SERIAL, title VARCHAR NOT NULL, CONSTRAINT products_pkey PRIMARY KEY(id) );
CREATE TABLE public.filters (
id SERIAL,
title VARCHAR NOT NULL,
CONSTRAINT filters_pkey PRIMARY KEY(id)
);
Таблица соединений между фильтрами и изделиями:
CREATE TABLE public.products_ref_filters (
id_product INTEGER NOT NULL,
id_filter INTEGER NOT NULL,
CONSTRAINT products_ref_filters_pkey PRIMARY KEY(id_product, id_filter),
CONSTRAINT products_ref_filters_fk FOREIGN KEY (id_product)
REFERENCES public.products(id)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT products_ref_filters_fk1 FOREIGN KEY (id_filter)
REFERENCES public.filters(id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
И это оказывается стандартный вариант. Тривиально.
Дальше…
Добавьте поле фильтров в таблицу товаров: ALTER TABLE public.products
ADD COLUMN filters INTEGER[] DEFAULT ARRAY[]::integer[] NOT NULL;
Как вы заметили, это массив чисел, здесь будут дублироваться идентификаторы фильтров, да, это денормализация.
Для целостности пишем процедуры и прикрепляем их к триггеру.
Вставлять: CREATE OR REPLACE FUNCTION public.products_ref_filters__insert_tr ()
RETURNS trigger AS'
BEGIN
UPDATE products
SET filters = filters + NEW.id_filter --push element onto array
WHERE id = NEW.id_product;
RETURN NEW;
END;
'LANGUAGE 'plpgsql';
CREATE TRIGGER products_ref_filters__insert_tr
AFTER INSERT
ON public.products_ref_filters FOR EACH ROW
EXECUTE PROCEDURE public.products_ref_filters__insert_tr();
Удаление: CREATE OR REPLACE FUNCTION public.products_ref_filters__delete_tr ()
RETURNS trigger AS'
BEGIN
UPDATE products
SET filters = filters - OLD.id_filter --remove entries matching right argument from array
WHERE id = OLD.id_product;
RETURN OLD;
END;
'LANGUAGE 'plpgsql';
Обновлять: CREATE OR REPLACE FUNCTION public.products_ref_filters__update_tr ()
RETURNS trigger AS'
BEGIN
UPDATE products
SET filters = filters - OLD.id_filter
WHERE id = OLD.id_product;
UPDATE products
SET filters = filters + NEW.id_filter
WHERE id = NEW.id_product;
RETURN NEW;
END;
'LANGUAGE 'plpgsql';
CREATE TRIGGER products_ref_filters__update_tr
AFTER UPDATE
ON public.products_ref_filters FOR EACH ROW
EXECUTE PROCEDURE public.products_ref_filters__update_tr();
Очистка: CREATE OR REPLACE FUNCTION public.products_ref_filters__truncate_tr ()
RETURNS trigger AS'
BEGIN
UPDATE products SET filters = ARRAY[]::INTEGER[];
RETURN NULL;
END;
'LANGUAGE 'plpgsql';
CREATE TRIGGER products_ref_filters__truncate_tr
AFTER TRUNCATE
ON public.products_ref_filters FOR EACH STATEMENT
EXECUTE PROCEDURE public.products_ref_filters__truncate_tr();
Теперь при вставке, обновлении, удалении данных из таблицы ссылок в таблицу товаров будет записываться массив фильтров.
И мы избавились от JOIN. Теперь нет необходимости склеивать таблицы в запросе.
Это дает много преимуществ: запросы строить проще, они быстрее, памяти меньше и т. д. Но статья про intarray? Да.
Установите расширение: CREATE EXTENSION intarray;
После выполнения этой команды в базе данных появятся функции, индексы и операторы для работы с массивами типа INTEGER. Теперь работать будет намного быстрее и удобнее.
Наполняем нашу базу данных.
10 000 фильтров.
100 000 товаров.
Каждый продукт имеет 10 фильтров.
Итого: промежуточная таблица 1 000 000 строк.
На выполнение этого блока запросов у меня ушло 8 минут. Так что ждите.
INSERT INTO filters (title) SELECT 'filter_' || num FROM generate_series(1, 10000) as num;
INSERT INTO products (title) SELECT 'product_' || num FROM generate_series(1, 100000) as num;
DO $$
DECLARE
idp INTEGER;
BEGIN
FOR idp IN SELECT id FROM products
LOOP
Теги: #postgresql #postgresql
-
Есть Ли Жизнь После Синьора?
19 Oct, 24 -
Телефон В Сердце Спутника
19 Oct, 24 -
Google Checkout Работает Без Комиссии
19 Oct, 24
