Pack de coupons aves sql views

Tentative d'optimisation du module pack de coupons aves sql views. Requête à optimiser: pack_coupon.available_modele_articles => renvoi les modele_articles qui sont disponibles pour un modele_article donné (+ filtrable par filière de vente et statut de disponibilité).

1 Vue principale

Requête permettant de récupérer une pseudo table de jointure entre: - modele_article via modele_article_id - pack_coupon via pack_coupon_id - filiere_vente via filiere_vente_id

On ajoute aussi le statut de l'article_disponibilite, liant la filière de vente et le modele article: article_disponibilite_statut.

Requête:

SELECT DISTINCT 
    pack_coupons.id AS pack_coupon_id,
    modele_articles.id AS modele_article_id,
    filiere_ventes.id AS filiere_vente_id,
    article_disponibilites.statut AS article_disponibilite_statut
FROM 
    pack_coupons
JOIN 
    template_pack_coupons ON template_pack_coupons.id = pack_coupons.template_pack_coupon_id
JOIN 
    categorie_places_template_pack_coupons ON template_pack_coupons.id = categorie_places_template_pack_coupons.template_pack_coupon_id
JOIN 
    categorie_places ON categorie_places.id = categorie_places_template_pack_coupons.categorie_place_id
JOIN 
    template_articles_match ON template_articles_match.id = template_pack_coupons.template_article_match_id
JOIN 
    matchs_pack_coupons ON pack_coupons.id = matchs_pack_coupons.pack_coupon_id
JOIN 
    matchs ON matchs_pack_coupons.match_id = matchs.id AND matchs.date >= CURRENT_DATE
JOIN 
    matchs_modele_articles ON matchs_modele_articles.match_id = matchs.id
JOIN 
    modele_articles ON modele_articles.id = matchs_modele_articles.modele_article_id 
    AND modele_articles.template_article_match_id = template_articles_match.id 
    AND modele_articles.etat = 1
JOIN 
    categorie_publics ON categorie_publics.id = modele_articles.categorie_public_id 
    AND categorie_publics.id = template_pack_coupons.categorie_public_id
JOIN 
    article_disponibilites ON modele_articles.id = article_disponibilites.modele_article_id
JOIN 
    filiere_ventes ON article_disponibilites.filiere_vente_id = filiere_ventes.id
JOIN 
    tarifs ON tarifs.modele_article_id = modele_articles.id 
    AND tarifs.categorie_place_id = categorie_places.id
JOIN 
    ligne_tarifs ON ligne_tarifs.id = tarifs.ligne_tarif_id
LEFT OUTER JOIN 
    articles ON articles.pack_coupon_id = pack_coupons.id
LEFT OUTER JOIN 
    articles_matchs ON articles.id = articles_matchs.article_id
WHERE 
    (modele_articles.type_acces = ANY(CASE
        WHEN template_pack_coupons.type_produit = 0 THEN ARRAY[0]
        ELSE ARRAY[0, 2] 
    END))
GROUP BY 
    (pack_coupons.id, modele_articles.id, filiere_ventes.id, template_pack_coupons.taille,
    template_pack_coupons.type_coupon, article_disponibilites.statut)
HAVING 
    (CASE
        WHEN template_pack_coupons.type_coupon = 0 THEN COUNT(DISTINCT CASE WHEN articles.vendu THEN articles.id ELSE NULL END) < template_pack_coupons.taille
        ELSE COUNT(DISTINCT CASE WHEN articles.vendu AND articles_matchs.match_id = matchs.id THEN articles_matchs.article_id ELSE NULL END) < template_pack_coupons.taille
    END)

Une ligne est présente si: - Le pack de coupon est encore valide: - Si il est au_match: il n'a pas vendu autant d'articles que sa taille - Si il est recurrent, pour un match: il n'a pas vendu autant d'articles que sa taille pour le modele_article lié au match - Un modele_article existe: - ayant le même template_article_match que le pack de coupon - etant lié à un des matchs non passé de ce pack de coupon - ayant des tarifs actifs pour les catégories de place du pack_coupon - ayant le type d'acces par_coupon si le pack de coupon est de type prive, n'importe quel autre type sinon

2 Fonction de rafraichissement

CREATE OR REPLACE FUNCTION refresh_persisted_available_modele_articles_pack_coupons(
    _pack_coupon_id bigint,
    _modele_article_id bigint,
    _filiere_vente_id bigint
)
    RETURNS TABLE (
                      func_pack_coupon_id bigint,
                      func_modele_article_id bigint,
                      func_filiere_vente_id bigint,
                      func_article_disponibilite_statut integer,
                      func_expiration_time timestamp
                  )
    LANGUAGE plpgsql
AS $$
DECLARE
    t RECORD;
BEGIN
    -- Récupère les données correspondantes
    SELECT *
    INTO t
    FROM available_modele_articles_pack_coupons
    WHERE pack_coupon_id = _pack_coupon_id
      AND modele_article_id = _modele_article_id
      AND filiere_vente_id = _filiere_vente_id;

    -- Vérifie si la ligne existe
    IF FOUND THEN
        -- Si la ligne existe, on met à jour l'entrée correspondante dans lazy_available_modele_articles_pack_coupons
        UPDATE persisted_available_modele_articles_pack_coupons
        SET article_disponibilite_statut = t.article_disponibilite_statut,
            expiration_time = 'Infinity'
        WHERE filiere_vente_id = _filiere_vente_id
          AND pack_coupon_id = _pack_coupon_id
          AND modele_article_id = _modele_article_id;
    ELSE
        -- Sinon, on supprime l'entrée correspondante (s'il y en a une)
        DELETE FROM persisted_available_modele_articles_pack_coupons
        WHERE pack_coupon_id = _pack_coupon_id
          AND modele_article_id = _modele_article_id
          AND filiere_vente_id = _filiere_vente_id;
    END IF;

    -- Retourne la ligne modifiée ou supprimée avec des noms de colonnes différents
    RETURN QUERY
        SELECT pack_coupon_id, modele_article_id, filiere_vente_id, article_disponibilite_statut, expiration_time
        FROM persisted_available_modele_articles_pack_coupons
        WHERE pack_coupon_id = _pack_coupon_id
          AND modele_article_id = _modele_article_id
          AND filiere_vente_id = _filiere_vente_id;
END;
$$;

Via la vue available_modele_articles_pack_coupons, on vérifie si la la ligne _pack_coupon_id , _modele_article_id, _filiere_vente_id existe. Si non, on la supprime de la table persisted_available_modele_articles_pack_coupons. Si oui, on met à jour le statut et la colonne expiration_time de la ligne correspondante.

3 Vue d'accès à la table

select * from persisted_available_modele_articles_pack_coupons where expiration_time > current_timestamp
union all
select result.* from persisted_available_modele_articles_pack_coupons lamapc
                         cross join refresh_persisted_available_modele_articles_pack_coupons(lamapc.pack_coupon_id, lamapc.modele_article_id, lamapc.filiere_vente_id) result
where lamapc.expiration_time < current_timestamp;

Accède à la table persisted_available_modele_articles_pack_coupons en renvoyant directement les lignes qui ont une expiration_time supérieure à la date actuelle, et en renvoyant les lignes nt une expiration_time inférieure à la date actuelle après les avoir refresh via la vue available_modele_articles_pack_coupons.

4 Triggers

1 Pack de coupon

Creation -> Insertion de lignes #DONE

Suppression -> Suppression de lignes #TODO pas censé arriver

N'est pas censé être update

2 Article disponibilite

Creation -> Insertion de lignes #DONE

Suppression -> Suppression de lignes #DONE pas censé arriver

Maj -> Invalidation de lignes #DONE

3 Modele article

Maj -> Invalidation de lignes #TODO

4 Categorie de place

Suppression -> Invalidation de lignes #TODO

5 Tarifs

Tout -> Invalidation de lignes #TODO

6

On part du principe que les autres modèles ne seront pas modifiés.