IWM Platform

Русский

English

Русский

English

Тема

Реализация MLM-дерева (Closure Table) ​

Проектирование схемы ​

sql
-- Таблица партнёров (список смежности для прямого родителя)
CREATE TABLE mlm.partners (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL UNIQUE REFERENCES core.users(id),
    sponsor_id UUID REFERENCES mlm.partners(id),  -- Прямой спонсор
    rank_id UUID REFERENCES mlm.ranks(id),
    status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
    joined_at TIMESTAMP NOT NULL DEFAULT NOW(),

    -- Денормализованные счётчики для производительности
    direct_referrals_count INT DEFAULT 0,
    total_network_size INT DEFAULT 0,

    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Closure table для эффективных запросов по дереву
CREATE TABLE mlm.partner_tree_paths (
    ancestor_id UUID NOT NULL REFERENCES mlm.partners(id),
    descendant_id UUID NOT NULL REFERENCES mlm.partners(id),
    depth INT NOT NULL,  -- Расстояние между узлами

    PRIMARY KEY (ancestor_id, descendant_id)
);

-- Индексы для частых запросов
CREATE INDEX idx_tree_ancestor_depth ON mlm.partner_tree_paths(ancestor_id, depth);
CREATE INDEX idx_tree_descendant ON mlm.partner_tree_paths(descendant_id);
CREATE INDEX idx_partners_sponsor ON mlm.partners(sponsor_id) WHERE status = 'ACTIVE';

Операции с деревом (критические) ​

INSERT: Добавление нового партнёра ​

При присоединении нового партнёра необходимо:

  1. Вставить запись партнёра с sponsor_id
  2. Вставить самоссылку (depth = 0)
  3. Скопировать все пути предков от спонсора и увеличить depth
sql
-- Функция: Добавление нового партнёра в дерево
CREATE OR REPLACE FUNCTION mlm.add_partner_to_tree(
    p_partner_id UUID,
    p_sponsor_id UUID
) RETURNS VOID AS $$
BEGIN
    -- 1. Вставка самоссылки (каждый узел является своим собственным предком на глубине 0)
    INSERT INTO mlm.partner_tree_paths (ancestor_id, descendant_id, depth)
    VALUES (p_partner_id, p_partner_id, 0);

    -- 2. Если спонсор существует, копируем всех предков спонсора и добавляем самого спонсора
    IF p_sponsor_id IS NOT NULL THEN
        INSERT INTO mlm.partner_tree_paths (ancestor_id, descendant_id, depth)
        SELECT ancestor_id, p_partner_id, depth + 1
        FROM mlm.partner_tree_paths
        WHERE descendant_id = p_sponsor_id;
    END IF;

    -- 3. Обновление денормализованных счётчиков спонсора
    IF p_sponsor_id IS NOT NULL THEN
        UPDATE mlm.partners
        SET direct_referrals_count = direct_referrals_count + 1,
            updated_at = NOW()
        WHERE id = p_sponsor_id;

        -- Обновление total_network_size для всех предков
        UPDATE mlm.partners p
        SET total_network_size = total_network_size + 1,
            updated_at = NOW()
        FROM mlm.partner_tree_paths t
        WHERE t.ancestor_id = p.id
          AND t.descendant_id = p_sponsor_id;
    END IF;
END;
$$ LANGUAGE plpgsql;

Триггер: Автоматическое поддержание closure table при вставке партнёра ​

sql
CREATE OR REPLACE FUNCTION mlm.trg_partner_after_insert()
RETURNS TRIGGER AS $$
BEGIN
    PERFORM mlm.add_partner_to_tree(NEW.id, NEW.sponsor_id);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER partner_after_insert
    AFTER INSERT ON mlm.partners
    FOR EACH ROW
    EXECUTE FUNCTION mlm.trg_partner_after_insert();

MOVE: Перемещение партнёра к новому спонсору (редкая операция) ​

Предупреждение: Это сложная операция, влияющая на историю комиссий. Требует подтверждения администратора и журналирования аудита.

sql
-- Функция: Перемещение партнёра (и всего поддерева) к новому спонсору
CREATE OR REPLACE FUNCTION mlm.move_partner_to_sponsor(
    p_partner_id UUID,
    p_new_sponsor_id UUID,
    p_admin_id UUID,
    p_reason TEXT
) RETURNS VOID AS $$
DECLARE
    v_old_sponsor_id UUID;
    v_subtree_size INT;
BEGIN
    -- 0. Валидация: Нельзя перемещать к себе или собственному потомку
    IF p_partner_id = p_new_sponsor_id THEN
        RAISE EXCEPTION 'Нельзя переместить партнёра к самому себе';
    END IF;

    IF EXISTS (
        SELECT 1 FROM mlm.partner_tree_paths
        WHERE ancestor_id = p_partner_id AND descendant_id = p_new_sponsor_id
    ) THEN
        RAISE EXCEPTION 'Нельзя переместить партнёра к собственному потомку (циклическая ссылка)';
    END IF;

    -- 1. Получение текущего спонсора и размера поддерева
    SELECT sponsor_id INTO v_old_sponsor_id FROM mlm.partners WHERE id = p_partner_id;
    SELECT COUNT(*) INTO v_subtree_size
    FROM mlm.partner_tree_paths WHERE ancestor_id = p_partner_id;

    -- 2. Логирование перемещения для аудита (до внесения изменений)
    INSERT INTO mlm.partner_move_audit (
        partner_id, old_sponsor_id, new_sponsor_id,
        subtree_size, moved_by, reason, created_at
    ) VALUES (
        p_partner_id, v_old_sponsor_id, p_new_sponsor_id,
        v_subtree_size, p_admin_id, p_reason, NOW()
    );

    -- 3. Удаление старых путей (от предков выше партнёра к поддереву партнёра)
    DELETE FROM mlm.partner_tree_paths
    WHERE descendant_id IN (
        SELECT descendant_id FROM mlm.partner_tree_paths WHERE ancestor_id = p_partner_id
    )
    AND ancestor_id IN (
        SELECT ancestor_id FROM mlm.partner_tree_paths
        WHERE descendant_id = p_partner_id AND ancestor_id != p_partner_id
    );

    -- 4. Вставка новых путей (от предков нового спонсора к поддереву партнёра)
    INSERT INTO mlm.partner_tree_paths (ancestor_id, descendant_id, depth)
    SELECT
        super.ancestor_id,
        sub.descendant_id,
        super.depth + sub.depth + 1
    FROM mlm.partner_tree_paths super
    CROSS JOIN mlm.partner_tree_paths sub
    WHERE super.descendant_id = p_new_sponsor_id
      AND sub.ancestor_id = p_partner_id;

    -- 5. Обновление sponsor_id в таблице partners
    UPDATE mlm.partners
    SET sponsor_id = p_new_sponsor_id, updated_at = NOW()
    WHERE id = p_partner_id;

    -- 6. Обновление денормализованных счётчиков
    -- Уменьшение счётчика прямых рефералов старого спонсора
    IF v_old_sponsor_id IS NOT NULL THEN
        UPDATE mlm.partners
        SET direct_referrals_count = direct_referrals_count - 1
        WHERE id = v_old_sponsor_id;
    END IF;

    -- Увеличение счётчика прямых рефералов нового спонсора
    UPDATE mlm.partners
    SET direct_referrals_count = direct_referrals_count + 1
    WHERE id = p_new_sponsor_id;

    -- Примечание: Пересчёт total_network_size должен выполняться пакетным заданием,
    -- так как это затратная операция и редко требуется в реальном времени
END;
$$ LANGUAGE plpgsql;

Таблица аудита перемещений партнёров ​

sql
CREATE TABLE mlm.partner_move_audit (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    partner_id UUID NOT NULL REFERENCES mlm.partners(id),
    old_sponsor_id UUID REFERENCES mlm.partners(id),
    new_sponsor_id UUID REFERENCES mlm.partners(id),
    subtree_size INT NOT NULL,
    moved_by UUID NOT NULL REFERENCES core.users(id),
    reason TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_move_audit_partner ON mlm.partner_move_audit(partner_id);

Защита от циклических ссылок ​

Функция MOVE валидирует циклические ссылки, но мы также добавляем ограничение базы данных:

sql
-- Ограничение: Партнёр не может быть своим собственным спонсором
ALTER TABLE mlm.partners
ADD CONSTRAINT no_self_sponsorship CHECK (id != sponsor_id);

Частые запросы ​

sql
-- Получить прямых рефералов (уровень 1)
SELECT p.* FROM mlm.partners p
JOIN mlm.partner_tree_paths t ON p.id = t.descendant_id
WHERE t.ancestor_id = :partner_id AND t.depth = 1;

-- Получить нижнюю линию до 5 уровня
SELECT p.*, t.depth FROM mlm.partners p
JOIN mlm.partner_tree_paths t ON p.id = t.descendant_id
WHERE t.ancestor_id = :partner_id
  AND t.depth BETWEEN 1 AND 5
ORDER BY t.depth;

-- Получить верхнюю линию (цепочку спонсоров)
SELECT p.*, t.depth FROM mlm.partners p
JOIN mlm.partner_tree_paths t ON p.id = t.ancestor_id
WHERE t.descendant_id = :partner_id AND t.depth > 0
ORDER BY t.depth;

-- Подсчёт по уровням
SELECT t.depth, COUNT(*) as count
FROM mlm.partner_tree_paths t
WHERE t.ancestor_id = :partner_id AND t.depth > 0
GROUP BY t.depth;

Связанные документы ​